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? :)

[Guest190829]

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):
// 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