Select * FROM table WHERE id = $array???

[tommyda]

is there a way to select * from a table where the row id matches any one of the numbers in an array?

 

I have a table called

 

Genres{ id, vlid, name }

 

And a table called

 

Movies(id, name, releasedate)

 

The genres are assigned to the movies using a the following table

 

Assign{ vlid,typeid, type }

 

What i want to do is select all the movie id;s from the assign table then pull the relevant movies using the vlids.

 

I do have a function but I have a feeling its is poorly coded, plus it doesnt work very well.

 

MoviesByAssign("genre", 1, 0, 30); should pull the first 30 movies assigned to the genre id "1"

 

 

<php
function MoviesByAssign($type, $typeid, $start, $limit){

$moviesingenre = array();
$result = mysql_query("SELECT vlid FROM assign WHERE type ='$type' AND typeid='$typeid'")or die(mysql_error());
while($row = mysql_fetch_array($result)) {
echo $row['vlid'].'-';
   $moviesingenre[] = $row['vlid'];
}

$result = mysql_query("SELECT * FROM videolistings ORDER BY releasedate DESC LIMIT $start, $limit");

$output='';

while($video = mysql_fetch_array($result)) {
   if(in_array($video['id'], $moviesingenre)) {
                $date = date("2/m/Y",$video['releasedate']);
             $num = GetMovieRating($video['id']);
             $expl = explode('-',$num);
                $round1 = round($expl[0],1);
	$rounded = round($expl[0]);
	if(empty($video['img'])){$img= $baseurl.'/images/x.gif';}else{$img= $video['img'];};
     $output.= '
     <div class="lholder">
     <a href="'.$baseurl.'/videos/'.$video['slug'].'_'.$video['id'].'" title="'.ucwords($video['name']).'">
     <img class="limg" src="'.$img.'" border="0"/></a>
     <div class="linfo">
     <a href="'.$baseurl.'/videos/'.$video['slug'].'_'.$video['id'].'" title="'.ucwords($video['name']).'"><strong>'.ucwords($video['name']).'</strong></a>
     <ul class="linkrow">
     <li>Release Date: '.$date.'</li>
     <li> Rating:
     <strong>'.$round1.'</strong> / 5  ('.$expl[1].' Votes) <br/><img src="'.$baseurl.'/img/stars_'.$rounded.'.gif"/></li></ul></div></div>';
  
   }//END IF

};//ND WHILE
return $output;

};//END FUNCTION

?>

 

Can anyone please help

[PFMaBiSmAd]

If you covert your array into a comma separated list, you can use the IN() function - http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

[Mchl]

How about one query to rule them all?

 

SELECT m.* 
FROM movies AS m
INNER JOIN
assign AS a
ON a.vlid = m.id
WHERE a.typeID = '$typeID' AND a.type = '$type'
LIMIT 0,30

 

[tommyda]

Thanks for the help.

 

Mchl could you possibly explain the query.

 

i think ive got it but i dont understand what the .m is for

[tommyda]

Wow it works, thanks I understand the query now.

 

Thanks again/.