Looks like you've got JavaScript disabled in your browser - you'll get a better site experience if you enable JavaScript

solved DB Get JR ProfileURL from UserID

The Jamroom Network » Forum » Jamroom Developers » Page » DB Get JR ProfileURL from UserID

SoftDesigns
@softdesigns
6 years ago
242 posts

Hi - At the moment, for this integration, we must work directly against the JR DB using standard SQL.
--
We already have the UserID and G+URL from table: jr_jroneall_link
We are now correctly getting the list of users from "jr_jroneall_link" table, but one question:
--
Please confirm which table the JR User Profile URL is stored in?
What is the standard SQL to get a user "JR ProfileURL" from UserID ?
updated by @softdesigns: 08/19/18 09:06:14PM

@michael
6 years ago
7,692 posts

I'd avoid trying to use SQL on a datastore if at all possible. If possible use the jrCore_db_query() function to get datastore stuff or you're bypassing all the other modules optional overrides.

A search query to get all profiles info would be

    // We have mentions - make sure they are good
        $_rt = array(
            'search'         => array(
                'profile_url in ' . implode(',', $_tmp)
            ),
            'return_keys'    => array('_profile_id', 'profile_url'),
            'skip_triggers'  => true,
            'ignore_pending' => true,
            'limit'          => count($_tmp)
        );
        $_rt = jrCore_db_search_items('jrProfile', $_rt);
        if ($_rt && is_array($_rt) && isset($_rt['_items'])) {
.....

do a search on the codebase for 'jrCore_db_search_items('jrProfile',' and you'll see many examples.

A profile url in is just the base url of the site + the profile_url, so in php that's

$profile_url  = "{$_conf['jrCore_base_url']}/{$item['profile_url']}";

if you absolutely have to do a direct SQL query then its

$tbl = jrCore_db_table_name('jrProfile', 'item_key');
$req = "SELECT * FROM {$tbl} WHERE item_id = '1' ";
$single = jrCore_db_query($req, 'SINGLE'); // a single query or
$_many = jrCore_db_query($req, 'NUMERIC'); // many items

SoftDesigns
@softdesigns
6 years ago
242 posts

Hi - Actually our API is coded in NodeJS, so we need to work with straight standard SQL. For integration coding, we have found it most productive if we are able to query our data directly at the DB level.
--
It looks like the two tables we need are:

jr_jrprofile_item_key - we found "profile_url" is stored here - Now just need to link JR UserID to JR ProfileID...

jr_jroneall_link - the "data" column of this table may work...

Also Maybe table: jr_jrprofile_profile_link

Does "data" column of "jr_jroneall_link" table store JR ProfileID?
Or do we need to use : jr_jrprofile_profile_link ?
--
Basicallly from the @michael code above, we now have found the - profile_url - field.
--
We just need the simplest standard SQL, method, and tables involved to return "profile_url" from table jr_jrprofile_item_key , by querying using the JR UserID.
What would that standard SQL look like?
updated by @softdesigns: 05/15/18 04:12:10PM

@michael
6 years ago
7,692 posts

user_id and profile_id are separate since any user can have multiple profiles. The _profile_id listed in the jrUser datastore is the 'home profile' so maybe use that.

Once you have the profile_id use it in the second query explained above with the SINGLE option.

SoftDesigns
@softdesigns
6 years ago
242 posts

@michael - Ok, cool - Now this is starting to make sense.
--
Need more time to study and test your info above...
Great Support

updated by @softdesigns: 05/16/18 07:04:52PM

solved DB Get JR ProfileURL from UserID

Tags