Echo if no result

Thread Solved

Join Date: Jul 2009
Posts: 208
Reputation: CFROG is an unknown quantity at this point 
Solved Threads: 14
CFROG's Avatar
CFROG CFROG is offline Offline
Posting Whiz in Training

Echo if no result

 
0
  #1
Oct 4th, 2009
I'm trying to make a list of friends that are NOT in address book.

This returns all friends :
  1. // Check for friends
  2. $frnd="SELECT * FROM friends WHERE my_id='$_SESSION[user_id]'";
  3. $fres = mysql_query($frnd);
  4. $fnum = mysql_num_rows($fres);
  5. while($fr=mysql_fetch_array($fres)){
  6. $friend = $fr['friend_name'];
  7. $friend_id = $fr['friend_id']; }

This returns all friends that are in address book:
  1. // Lookup address book
  2. $addy="SELECT * FROM address_book WHERE my_id='$myid'";
  3. $abresult=mysql_query($addy);
  4. $adnum=mysql_num_rows($abresult);
  5. while($ab=mysql_fetch_array($abresult)){
  6. $usr=$ab['email'];
  7. $their_id=stripslashes($ab['their_id']);
  8. $user_name=stripslashes($ab['nick']);
  9. $their_username=stripslashes($ab['their_username']); }

How do I return all friends that ARE NOT in the address book ?
Reply With Quote Quick reply to this message  
Join Date: May 2007
Posts: 433
Reputation: Atli is on a distinguished road 
Solved Threads: 56
Atli's Avatar
Atli Atli is offline Offline
Posting Pro in Training

Re: Echo if no result

 
1
  #2
Oct 4th, 2009
Hey.

This should do it:
  1. SELECT * FROM friends
  2. WHERE their_id NOT IN (
  3. SELECT their_id FROM address_book
  4. )

P.S.
Is "my_id" a number?
If it is, the $myid value really shouldn't be quoted
  1. SELECT * FROM address_book WHERE my_id = $myid
Last edited by Atli; Oct 4th, 2009 at 8:29 pm. Reason: Misunderstood. Sorry :)
Please do not ask for help in a PM. Use the forums.
And use [code] tags!
Reply With Quote Quick reply to this message  
Join Date: Jul 2009
Posts: 208
Reputation: CFROG is an unknown quantity at this point 
Solved Threads: 14
CFROG's Avatar
CFROG CFROG is offline Offline
Posting Whiz in Training

Re: Echo if no result

 
0
  #3
Oct 4th, 2009
That would return the friends of other users.

It tried
  1. $sql = "SELECT * FROM address_book WHERE their_id != '$friend_id'"

It didn't work, maybe I did it wrong cuz it seems like that should work. I'll have to try again.

I also tried this
  1. $sql = "SELECT ab.*, f.* FROM address_book AS u INNER JOIN friends AS f ON ab.my_id = f.my_id WHERE ab.their_id !='$friend_id'";
Needless to say that didn't work either. I need to clear the cobwebs out of my head b/c this really shouldn't be that difficult.
Reply With Quote Quick reply to this message  
Join Date: May 2007
Posts: 433
Reputation: Atli is on a distinguished road 
Solved Threads: 56
Atli's Avatar
Atli Atli is offline Offline
Posting Pro in Training

Re: Echo if no result

 
0
  #4
Oct 4th, 2009
Yes, I misunderstood the question there.
Edited my previous post with the real answer.

Sorry though I would be able to edit it in time before you saw my first attempt
Please do not ask for help in a PM. Use the forums.
And use [code] tags!
Reply With Quote Quick reply to this message  
Join Date: Jul 2009
Posts: 208
Reputation: CFROG is an unknown quantity at this point 
Solved Threads: 14
CFROG's Avatar
CFROG CFROG is offline Offline
Posting Whiz in Training

Re: Echo if no result

 
0
  #5
Oct 4th, 2009
I'm having problems with that one too.
  1. SELECT * FROM friends AS f
  2. WHERE their_id NOT IN (
  3. SELECT their_id FROM address_book
  4. )
I changed it up to
  1. $sql = "SELECT * FROM friends WHERE my_id=$myid AND their_id NOT IN (SELECT their_id FROM address_book WHERE my_id=$myid)";

I'm a bit confused about this part (SELECT their_id FROM address_book) I mean, I understand the concept ... Is that just another query?

Yes, $myid is a #. I never knew I didn't have to quote numbers. Is is because they don't need to read as string?
Reply With Quote Quick reply to this message  
Join Date: May 2007
Posts: 433
Reputation: Atli is on a distinguished road 
Solved Threads: 56
Atli's Avatar
Atli Atli is offline Offline
Posting Pro in Training

Re: Echo if no result

 
0
  #6
Oct 4th, 2009
Ah yes, you used friend_id in the first table.
So it should be:
  1. $sql = "SELECT * FROM friends WHERE my_id=$myid AND friend_id NOT IN (SELECT their_id FROM address_book WHERE my_id=$myid)";

Originally Posted by CFROG View Post
I'm a bit confused about this part (SELECT their_id FROM address_book) I mean, I understand the concept ... Is that just another query?
Yes, it's a sub-query.
Basically, it performs a second query inside the first one, to get a list of ids to match against the one in the first query.

It can be very handy at times. Well worth looking into.
Originally Posted by CFROG View Post
Yes, $myid is a #. I never knew I didn't have to quote numbers. Is is because they don't need to read as string?
Yea. Only strings and string-like data, such as dates, need to be quoted. Numbers don't.
Doesn't really matter that much today, MySQL 5 does a good job of spotting numbers even if they are quoted, but older software would give you incorrect results, or even errors, if you quoted numbers. (Not sure exactly if MySQL ever did that tho, but I definetly remember M$ SQL server doing it way back.)
Please do not ask for help in a PM. Use the forums.
And use [code] tags!
Reply With Quote Quick reply to this message  
Join Date: Jul 2009
Posts: 208
Reputation: CFROG is an unknown quantity at this point 
Solved Threads: 14
CFROG's Avatar
CFROG CFROG is offline Offline
Posting Whiz in Training

Re: Echo if no result

 
0
  #7
Oct 4th, 2009
That worked perfectly, thank you. I guess my next step is to learn about sub-queries. That worked a heck of a lot better than the mess I was making. It saved me a lot of unnecessary coding. Talk about taking the long way to get from point A to point B.

Thanks again for the help!
Reply With Quote Quick reply to this message  
Reply

This thread has been marked solved.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC