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

Sounds like a simple IN() clause should do the trick in 1 query? — Wrikken
– Wrikken, Commented Oct 15, 2010 at 23:49
Just a small note. IN is an operator. Not to be confused with a clause or a function. — Alin P.
– Alin P., Commented Oct 15, 2010 at 23:58
As golden rule, include as much information as you can (I'm referring to the first query) — Cesar
– Cesar, Commented Oct 16, 2010 at 0:27

Cesar · Accepted Answer · 2010-10-16 00:25:42Z

3

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

answered Oct 16, 2010 at 0:25

Cesar

4,4372 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alin P. · Accepted Answer · 2010-10-16 00:01:17Z

0

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

edited Oct 16, 2010 at 0:01

answered Oct 15, 2010 at 23:55

Alin P.

44.5k13 gold badges79 silver badges93 bronze badges

2 Comments

artworthy Over a year ago

Thanks, Alin. Worked great. With 70 vars compared in different groupings (e.g., paintings not the same media as sculpture or photos) it was complex to figure out. But you pointed me in the right direction with query within query. -much appreciated, Allen

Alin P. Over a year ago

No problem. Also note that on stackoverflow it is customary to accept the answers you consider best answer your questions.

Aspelund · Accepted Answer · 2010-10-16 10:13:24Z

0

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.

answered Oct 16, 2010 at 10:13

Aspelund

4264 silver badges10 bronze badges

Collectives™ on Stack Overflow

SELECT with array()

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related