Please don't light me up for this post...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
orbdrums
Forum Commoner
Posts: 82
Joined: Wed Sep 14, 2011 11:42 pm

Please don't light me up for this post...

Post by orbdrums »

I'm trying to create an array variable from a simple SQL query to use in a NOT IN() function in another SQL query. I have an ID field in Table 2 that corresponds to an ID field in Table 1. The relationship is Table 1 one to Table 2 many. I have been successful creating the second SQL query however, I only get the last ID from Table 2.

Here is an example of my code:

Code: Select all

$query = "SELECT * FROM Table2 WHERE `mbr_member_id` = '$v_mbr_id'"; 
$result = mysql_query($query); 

while ($row = mysql_fetch_assoc($result)) 
{ 
$variable = $row['friend_of']; 
} 

$rand_mbr = "SELECT * FROM Table1 WHERE `act`='Y' AND `lck`='N' AND `member_id` != '$v_mbr_id' AND `member_id` NOT IN ('$variable') ORDER BY RAND() LIMIT 5"; 
Any help would be greatly appreciated.


pickle | Please use [ syntax=php ], [ syntax=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.
pbs
Forum Contributor
Posts: 230
Joined: Fri Nov 07, 2008 5:31 am
Location: Nashik, India
Contact:

Re: Please don't light me up for this post...

Post by pbs »

with respect to your above code, you can create comma(,) separated string $variable like this

while ($row = mysql_fetch_assoc($result))
{
$variable .= $row['friend_of'].",";
}
$variable = trim($variable,",");

or else you can join both your queries
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Please don't light me up for this post...

Post by AbraCadaver »

Do it in one query. This may work, or something similar:

Code: Select all

$rand_mbr = "SELECT * FROM Table1
WHERE `act`='Y' AND `lck`='N' AND `member_id` != '$v_mbr_id'
AND `member_id` NOT IN (SELECT `friend_of` FROM Table2 WHERE `mbr_member_id` = '$v_mbr_id')
ORDER BY RAND() LIMIT 5";
But to your original question, you are overwriting $variable each time through the loop. Create an array and implode if you want to do it the other way:

Code: Select all

while ($row = mysql_fetch_assoc($result)) { 
    $variable[] = $row['friend_of']; 
} 
$variable = implode(',', $variable);
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
orbdrums
Forum Commoner
Posts: 82
Joined: Wed Sep 14, 2011 11:42 pm

Re: Please don't light me up for this post...

Post by orbdrums »

This worked like a charm. Thank you to AbraCadaver and those that contributed. I appreciate the help.

This is the code that worked:

$rand_mbr = "SELECT * FROM Table1
WHERE `act`='Y' AND `lck`='N' AND `member_id` != '$v_mbr_id'
AND `member_id` NOT IN (SELECT `friend_of` FROM Table2 WHERE `mbr_member_id` = '$v_mbr_id')
ORDER BY RAND() LIMIT 5";
Post Reply