| | |
Echo if no result
Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
Thread Solved |
I'm trying to make a list of friends that are NOT in address book.
This returns all friends :
This returns all friends that are in address book:
How do I return all friends that ARE NOT in the 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']; }
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']); }
How do I return all friends that ARE NOT in the address book ?
Hey.
This should do it:
P.S.
Is "my_id" a number?
If it is, the $myid value really shouldn't be quoted
This should do it:
sql Syntax (Toggle Plain Text)
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
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!
And use [code] tags!
That would return the friends of other users.
It tried
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 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.
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'";
I'm having problems with that one too.
I changed it up to
I'm a bit confused about this part
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?
PHP Syntax (Toggle Plain Text)
SELECT * FROM friends AS f WHERE their_id NOT IN ( SELECT their_id FROM address_book )
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?
Ah yes, you used friend_id in the first table.
So it should be:
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.
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.)
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)";
•
•
•
•
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? 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?
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!
And use [code] tags!
![]() |
Similar Threads
- Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result (PHP)
- Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result (PHP)
- Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource (PHP)
- ERROR: supplied argument is not a valid MySQL result resource (PHP)
- Extracting select option fields from mySQL (PHP)
- help me--find command-shell program in unix (Shell Scripting)
- Find text on an external html site (PHP)
- automatic type conversion (PHP)
Other Threads in the PHP Forum
- Previous Thread: Simple Form to Thread Post?
- Next Thread: Foce-Download
| Thread Tools | Search this Thread |
apache api array beginner binary broken cache cakephp checkbox class cms code confirm cron curl customizableitems database date display dynamic echo email error external fcc file files folder form forms forum freelancing function functions google header headmethod howtowriteathesis href htaccess html iframe image include insert ip javascript joomla limit link login mail malfunction menu method mlm mod_rewrite multiple mysql neutrality oop pageing pagerank paypal pdf php phpmysql play problem query question radio random recursion remote root script search select server sessions sms soap source space sql support! syntax system table template tutorial update upload url validator variable video web youtube





