FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


mySQL help needed please.





DoctorBeaver
My mySQL is somewhat rusty & I can't remember how to do this.

I've got a membership system on my site with buddy lists. I want to be able to show members the names of all the members who AREN'T on their buddy list (excluding their own name, of course). I want the names displayed in alphabetical order

The fields concerned are:-

buddies.member_id - the id of the member whose buddy list it is
buddies.buddy_id - the id of the buddy

members.member_id - member's id
members.member_name - the name of the member

Basically what I want is:-

SELECT members.member_name, members.member_id FROM members WHERE (members.member_id NOT IN buddies IF buddies.member_id=$this_member_id) AND members.member_id != $this_member_id ORDER BY members.member_name ASC

In other words, if a member's id appears in table BUDDIES with $this_member_id as buddies.member_id, or members.member_id=$this_member_id, then exclude it and sort all the others alphabetically.

At the moment I'm reading the members table where member_id != $this_member_id ordered by member_name then, before I display the name I check whether it's already in this_member's buddy list. That's cumbersome. I'm sure there's a way to do it with just 1 database SELECT statement using a JOIN and checking for NULL but I can't for the life of me remember what it is.
DoctorBeaver
Could a mod please move this thread to the php/mySQL forum. I posted it in the wrong place. Sorry Embarassed
AftershockVibe
Try something along the lines of:

Code:
SELECT MemberId
FROM Members as M
WHERE NOT EXISTS
(
SELECT BuddyId
FROM Buddies AS B
WHERE B.MemberId = [i]$MemberId[/i] AND B.MemberId = M.MemberId
)


I assume you can't be buddies with yourself so this should be OK. I haven't tested this but it should work if you stick it in a string and substitute the italic part with the PHP variable for the member you want to do this for.
DoctorBeaver
Thanks. It's not the method I was trying to remember but I'll give it a go.
Related topics
help me please
Perl Script - Internal Error 404 File not found
Online Game
PHP/Mysql - beginner!
Xitami help needed, please
php page-refresh/redirect/boot help needed please
ARG, need help getting web site up.
SMF forum- help needed please
Big Help needed .
Uploading help needed.......Please?
mySQL help needed, any other good database thing?
How can I log in to MySQL? Please help!
php project help needed please
Can we get Python module mysql.connector installed, please ?
Reply to topic    Frihost Forum Index -> Computers -> Computer Problems and Support

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.