Mysql query inside a foreach

[bschultz]

I'm trying to run a query based on matches of an array (that I've put in a foreach loop).

 

Here's the code...

 

<?php
foreach($step_three as $step_three_final)   //$step_three is an array
{
//echo $step_three_final . "<br />";

$sqlx = "SELECT ump_id, priority FROM ump_priority WHERE `ump_id` = '$step_three_final' ORDER BY priority DESC";           
echo $sqlx;
$rsx = mysql_query($sqlx,$dbc);  
while($rowx = mysql_fetch_array($rsx)) {
echo "$rowx[ump_id]<br />";
}
}
?>

 

This results is this

 

SELECT ump_id, priority FROM ump_priority WHERE `ump_id` = '1' ORDER BY priority DESC1

SELECT ump_id, priority FROM ump_priority WHERE `ump_id` = '2' ORDER BY priority DESC2

SELECT ump_id, priority FROM ump_priority WHERE `ump_id` = '4' ORDER BY priority DESC4

SELECT ump_id, priority FROM ump_priority WHERE `ump_id` = '5' ORDER BY priority DESC5

SELECT ump_id, priority FROM ump_priority WHERE `ump_id` = '6' ORDER BY priority DESC6

SELECT ump_id, priority FROM ump_priority WHERE `ump_id` = '10' ORDER BY priority DESC10

SELECT ump_id, priority FROM ump_priority WHERE `ump_id` = '14' ORDER BY priority DESC14

SELECT ump_id, priority FROM ump_priority WHERE `ump_id` = '15' ORDER BY priority DESC15

SELECT ump_id, priority FROM ump_priority WHERE `ump_id` = '18' ORDER BY priority DESC18

 

This would answer my problem...why isn't this ordering by the priority properly?...of course it's because it's running 9 queries. 

 

What is the right way to run this query...so that it will have all the records in one query...so that I can order by the priority?

 

Thanks.

[Pikachu2000]

Don't run queries in loops unless absolutely necessary (and usually it isn't). Build the query string in the loop, or in this case all that's needed is implode and MySQL's IN(), then execute the query once.

 

$values = implode(', ', $step_three);
$sqlx = "SELECT ump_id, priority FROM ump_priority WHERE `ump_id` IN ($values) ORDER BY priority DESC";
$rsx = mysql_query($sqlx,$dbc);  
// ETC.

[bschultz]

people around this board are smart! 

 

Thanks!