Page 1 of 1

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

Posted: Fri Jun 07, 2013 1:24 am
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.

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

Posted: Fri Jun 07, 2013 1:50 am
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

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

Posted: Fri Jun 07, 2013 10:27 am
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);

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

Posted: Fri Jun 07, 2013 10:46 pm
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";