Efficient way of querying buddy list

[dolbex]

Ive been sitting here for an hour banging my head against the desk trying to figure out how to write a SQL statement that will return an array of users that are buddies on a specific user. I can pass the function the userid fine and I know that they reside in the vb3_usertextfield.buddylist field, I just don't know how to handle the contents so that I can then query the usernames without looping through an impload and querying for every single userid.

Any SQL junkies out there?

Why not just take vbulletin's code in profile.php? That's what I did ^_^

[Adrian Schneider]

I duno if there is a vb function, but try something like this (did it real quick, may contain errors):

PHP Code:

// Get Buddy List
$buddylist = $vbulletin->db->query_first("
    SELECT buddylist
    FROM " . TABLE_PREFIX . "usertextfield
    WHERE userid = '" . $userinfo['userid'] . "'
    LIMIT 1
");

// Convert to List
$buddies = str_replace(" ", ",", $buddylist['buddylist']);

// Get Their Usernames
$getbuddies = $vbulletin->db->query_read("
    SELECT username, userid
    FROM " . TABLE_PREFIX . "user
    WHERE userid IN(0" . $buddies . ")
    ORDER BY username ASC
");

// Print out Each Buddy
while ($buddy = $vbulletin->db->fetch_array($getbuddies))
{
    $buddycounter++;
    $comma = ($buddycounter > 1) ? ", " : "";
    echo $comma . '<a href="member.php?u=' . $buddy['userid'] . '">' .  $buddy['username'] . '</a>';
} 


change $userinfo['userid'] to the userid of the user you want to get.

[dolbex]

Thanks much SirAdrian works perfect. Had to modify it a little to play nicely with my db and functions but other than that it seems you were spot on. The only other request that I am getting is that it display which buddies are mutual. For instance. If I add you as a buddy and you add me as a buddy we are then mutual.

I have installed the "Who Am I a Buddy Of" Hack for the forum itself, but I can't seem to get the query to apply to my page. Basically I just want to display weather or not the buddy is mutual next to each buddy with a "(mutual)" output. LOL. Seems like its not a big deal but I am having a heck of a time trying to pull it off.

Thanks for all the help so far!

[dolbex]

I still have had no luck with figuring out an efficient way to figure out mutual buddies... anyone have a clue?

SELECT userid
FROM vb3_usertextfield
WHERE FIND_IN_SET('".$uid."', REPLACE(buddylist, ' ', ',')) > 0

Remember that this was for a function outside vb and "$uid" should be replaced with $bbuserinfo[$uid] or whatever the variable is...

Dolbex