SELECT with array()

Question

I'm trying to filter a table to SELECT the values to certain rows based on condition. The first rather complex SELECT works fine yielding me a group of id's. But I want to use this id group ($art_id) in an array for the 2nd SELECT to get rows more straight forwardly. Hope someone ha suggestions. Thanks, Allen.

the first SELECT is not included here -- I do get values with print $art_id; ...but only the last value shows up in my image list SELECT because I need to use an array properly

$QUERY1="SELECT.....etc,..."   ///this works fine

$res = mysql_query($QUERY1);
   $num = mysql_num_rows($res);
   if($num>0){
   while($row = mysql_fetch_array($res)){
       $art_id = $row['art_id'];

print $art_id;
$a1 = array($art_id); ///this $a1 var didn't work in the SELECT below.
   }
}
///here is where I need to have an array var instead of just $art_id
$QUERY2="SELECT * FROM artWork WHERE art_id = '$art_id'";  

    $res = mysql_query($QUERY2);
   $num = mysql_num_rows($res);
   if($num>0){

   while($row = mysql_fetch_array($res)){
       $art_title = $row['art_title'];
       $artist_name = $row['artist_name'];
       $art_id = $row['art_id'];
                 $media = $row['media'];

  echo.....etc,...../// only one image (the last, of course) shows up here

Answer 1

Hi,

Just use this to do it in one query.

SELECT [...] WHERE `art_id` IN (SELECT `art_id` [...])

Although this is redundant if you select from the same table in both queries.

If you want to test for values from a PHP array you should use IN like this:

$values = array(1,2,3);
'WHERE `art_id` IN ('.implode(',', $values).')'

Answer 2

you can resolve this problem by using just query if you like:

let's say that you first query get the art_id from the table table1 so:

SELECT art_id FROM table1

and you next query it's:

SELECT * FROM artWork WHERE art_id = '$art_id'

The final query obtained by merging the other 2 queries:

SELECT * FROM artWork WHERE art_id in ( SELECT art_id FROM table1 );

Answer 3

Use a join, it's pretty simple and much faster.

SELECT aw.* FROM table1 t
JOIN artWork aw ON aw.art_id=t.art_id
WHERE (CONDITIONS);

where CONDITIONS is the conditions from your first query.