[RESOLVED] MySQL query - Where Field In(Array)

[middleman666]

Hi All,

I'm trying to do something that must be simple, but I just can't seem to get my query right:

//Prepare venue data
$Vresult = mysql_query("SELECT * FROM SPCvenue WHERE Country = \"$county\"");
while ($Vrow= mysql_fetch_array($Vresult)) {
  $VenueID = $Vrow['id'];
  $VenueIDArray[] = $VenueID;
}

  //SQL Query  

  $query = "SELECT *, 
            DATE_FORMAT(Date,'%a, %D %M, %Y') AS readdate,
            DATE_FORMAT(Date,'%H:%i') AS time FROM SPCshow 
			WHERE Date >= \"$today\" 
			AND VenueID IN(\"$VenueIDArray\")
			ORDER BY Date ASC";

I want the second query to get all the VenueID's that are in the VenueIDArray Variable... is this possible?

Thanks.

[Jack_McSlay]

the best practice in this case would be to put the first query inside the second. replace the whole code with just this:

  $query = "SELECT *,
            DATE_FORMAT(Date,'%a, %D %M, %Y') AS readdate,
            DATE_FORMAT(Date,'%H:%i') AS time FROM SPCshow
            WHERE Date >= \"$today\"
            AND VenueID IN (SELECT id FROM SPCvenue WHERE Country = \"$county\")
            ORDER BY Date ASC";

[middleman666]

That's brilliant! Thanks for your help, works like a charm.