Ad:

PHP Discussion Thread
Marked Solved
Views: 364

Oct 4th, 2009

Echo if no result

Expand Post »

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

This returns all friends :

PHP Syntax (Toggle Plain Text)
                                        // 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']; }
                                        // 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:

PHP Syntax (Toggle Plain Text)
                                // 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']); }
                                // 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 ?

Similar Threads

CFROG

Reputation Points: 21

Solved Threads: 31

Posting Pro in Training

Offline

405 posts
since Jul 2009

Permalink

Oct 4th, 2009

Re: Echo if no result

Hey.

This should do it:

sql Syntax (Toggle Plain Text)
SELECT * FROM friends
WHERE their_id NOT IN (
	SELECT their_id FROM address_book
)
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

sql Syntax (Toggle Plain Text)
SELECT * FROM address_book WHERE my_id = $myid
SELECT * FROM address_book WHERE my_id = $myid

Last edited by Atli; Oct 4th, 2009 at 8:29 pm. Reason: Misunderstood. Sorry :)

Atli

Reputation Points: 93

Solved Threads: 70

Posting Pro

Offline

526 posts
since May 2007

Permalink

Oct 4th, 2009

Re: Echo if no result

That would return the friends of other users.

It tried

PHP Syntax (Toggle Plain Text)

$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

PHP Syntax (Toggle Plain Text)

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

CFROG

Reputation Points: 21

Solved Threads: 31

Posting Pro in Training

Offline

405 posts
since Jul 2009

Permalink

Oct 4th, 2009

Re: Echo if no result

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

Atli

Reputation Points: 93

Solved Threads: 70

Posting Pro

Offline

526 posts
since May 2007

Permalink

Oct 4th, 2009

Re: Echo if no result

I'm having problems with that one too.

PHP Syntax (Toggle Plain Text)
SELECT * FROM friends AS f
WHERE their_id NOT IN (
	SELECT their_id FROM address_book
)
SELECT * FROM friends AS f
WHERE their_id NOT IN (
	SELECT their_id FROM address_book
)

I changed it up to

PHP Syntax (Toggle Plain Text)

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

CFROG

Reputation Points: 21

Solved Threads: 31

Posting Pro in Training

Offline

405 posts
since Jul 2009

Permalink

Oct 4th, 2009

Re: Echo if no result

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

php Syntax (Toggle Plain Text)

$sql = "SELECT * FROM friends WHERE my_id=$myid AND friend_id NOT IN (SELECT their_id FROM address_book WHERE my_id=$myid)";

Click to Expand / Collapse Quote originally posted by CFROG ...

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.

Click to Expand / Collapse Quote originally posted by CFROG ...

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

Atli

Reputation Points: 93

Solved Threads: 70

Posting Pro

Offline

526 posts
since May 2007

Permalink

Oct 4th, 2009

Re: Echo if no result

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!

CFROG

Reputation Points: 21

Solved Threads: 31

Posting Pro in Training

Offline

405 posts
since Jul 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Simple Form to Thread Post?

Next Thread in PHP Forum Timeline: Foce-Download

DaniWeb Message

Cancel Changes

User Name
Password