Echo if no result

[CFROG]

I'm trying to make a list of friends that are NOT in address book.

This returns all friends :

// Check for friends 
					$frnd="SELECT * FROM friends WHERE my_id='$_SESSION[user_id]'";
					$fres = mysql_query($frnd);
					$fnum = mysql_num_rows($fres);
					 while($fr=mysql_fetch_array($fres)){
						  $friend = $fr['friend_name'];
						  $friend_id = $fr['friend_id']; }

This returns all friends that are in address book:

// Lookup address book 
				$addy="SELECT * FROM address_book WHERE my_id='$myid'";
				$abresult=mysql_query($addy);
				$adnum=mysql_num_rows($abresult);
                                  while($ab=mysql_fetch_array($abresult)){
							  $usr=$ab['email'];
							  $their_id=stripslashes($ab['their_id']);
							  $user_name=stripslashes($ab['nick']);
							  $their_username=stripslashes($ab['their_username']); }

How do I return all friends that ARE NOT in the address book ?

[Atli]

Hey.

This should do it:

SELECT * FROM friends
WHERE their_id NOT IN (
	SELECT their_id FROM address_book
)

P.S.
Is "my_id" a number?
If it is, the $myid value really shouldn't be quoted

SELECT * FROM address_book WHERE my_id = $myid

[CFROG]

That would return the friends of other users.

It tried

$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

$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.

[Atli]

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

[CFROG]

I'm having problems with that one too.

SELECT * FROM friends AS f
WHERE their_id NOT IN (
	SELECT their_id FROM address_book
)

I changed it up to

$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?

[Atli]

Ah yes, you used friend_id in the first table.
So it should be:

$sql = "SELECT * FROM friends WHERE my_id=$myid AND friend_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, 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.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.)

[CFROG]

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!