[SOLVED] Need help with a query.

[Waldir]

hello! i need help with a query, im trying to get a bunch of info out of 3 tables (message, user info, buddylist) however i want to query to take the infoo even when its not present i have it working just fine  but when the sender of the message is not on the users buddylist the query returns empty however if he is on his buddy list the query returns everything i was using 2 querys for this and it worked fine however i was wondering if there was a way to make it so i can only use one quey, and i can determine if the user is on the buddy list if lets say $msginfo[buddy_id] is empty, here is the query:

 

	SELECT '. TBL_MESSAGES .'.*, 
		  ('. TBL_BUDDYLIST .'.userid) AS buddy_userid, 
		  ('. TBL_BUDDYLIST .'.buddyid) AS buddy_id, 		  
		  ('. TBL_USERS .'.id) AS from_id, 
		  ('. TBL_USERS .'.username) AS from_username
	  FROM '. TBL_MESSAGES .', '. TBL_USERS .', '. TBL_BUDDYLIST .'  
	 WHERE '. TBL_MESSAGES .'.id='. QS_ID .' 
	   AND '. TBL_MESSAGES .'.toid='. $user_data[id] .'
	   AND '. TBL_MESSAGES .'.userid = '. TBL_USERS .'.id 
	   AND '. TBL_BUDDYLIST .'.userid = '. $user_data[id] .'
	   OR '. TBL_BUDDYLIST .'.buddyid = '. TBL_USERS .'.id 
	   LIMIT 1'

 

thanks in advance

[Waldir]

anyone? ;_;

[Barand]

Sounds like you need

 

buddy_list LEFT JOIN users ON buddy_list.buddyid = users.id

[Waldir]

i changed it to this but i still get the same result :/

SELECT '. TBL_MESSAGES .'.*, 
		  ('. TBL_BUDDYLIST .'.userid) AS buddy_userid, 
		  ('. TBL_BUDDYLIST .'.buddyid) AS buddy_id, 		  
		  ('. TBL_USERS .'.id) AS from_id, 
		  ('. TBL_USERS .'.username) AS from_username
	  FROM '. TBL_MESSAGES .', '. TBL_BUDDYLIST .' LEFT JOIN '. TBL_USERS .' ON '. TBL_BUDDYLIST .'.buddyid = '. TBL_USERS .'.id
	 WHERE '. TBL_MESSAGES .'.id='. QS_ID .' 
	   AND '. TBL_MESSAGES .'.toid='. $user_data[id] .'
	   AND '. TBL_MESSAGES .'.userid = '. TBL_USERS .'.id 
	   AND '. TBL_BUDDYLIST .'.userid = '. $user_data[id] .'
	   LIMIT 1

[Barand]

I've read you code a dozen times and I am still not sure what it is supposed to do. The only db structure I can glean from it is

 

[pre]

buddy_list            users            messages     

----------            ---------        --------------

userid        +-----> id        <----- userid       

buddyid  -----+      username        toid         

                                        id     

[/pre]

 

If you told us how the tables are structured and related, and maybe gave sample data and expected results, your chances of a successful reply would certainly increase.

[Waldir]

i got it to work, thank you so much for your help, i just had to play around with left join this is what i ended up with :

 

	SELECT '. TBL_MESSAGES .'.*, 
		   '. TBL_BUDDYLIST .'.userid AS buddy_userid, 
		   '. TBL_BUDDYLIST .'.buddyid AS buddy_id, 		  
		   '. TBL_USERS .'.id AS from_id, 
		   '. TBL_USERS .'.username AS from_username
	  FROM '. TBL_MESSAGES .', 
	       '. TBL_USERS .' 
 LEFT JOIN '. TBL_BUDDYLIST .' 
        ON '. TBL_USERS .'.id = '. TBL_BUDDYLIST .'.buddyid
	   AND '. TBL_BUDDYLIST .'.userid = '. $user_data[id] .'
	 WHERE '. TBL_MESSAGES .'.id='. QS_ID .' 
	   AND '. TBL_MESSAGES .'.toid='. $user_data[id] .'
	   AND '. TBL_MESSAGES .'.userid = '. TBL_USERS .'.id 
	   LIMIT 1