[SOLVED] Using loops within Query Statements

[ethos_bass]

Can someone think of a more graceful way to write this bit of code?  It's a SELECT query that grows in length depending on user input.  I have a simple <select list> HTML form on the previous page which outputs its results to the variable '$region_array'.  The trouble is that I want the query to contain only as many OR statements as the number of rows the user selected.  (In the following code the variable "$num_rows_selected" is how many items on the list the user selected.)

 

if ($num_rows_selected == 1)

{

$query="select * FROM bar_index WHERE region LIKE \"%$region_array[0]%\"";

}

elseif ($num_rows_selected == 2)

{

$query="select * FROM bar_index WHERE region LIKE \"%$region_array[0]%\"

OR region LIKE \"%$region_array[1]%\"";

}

elseif ($num_rows_selected == 3)

{

$query="select * FROM bar_index WHERE region LIKE \"%$region_array[0]%\"

OR region LIKE \"%$region_array[1]%\"

OR region LIKE \"%$region_array[2]%\"";

}

elseif ($num_rows_selected == 4)

{$query="select * FROM bar_index WHERE region LIKE \"%$region_array[0]%\"

OR region LIKE \"%$region_array[1]%\"

OR region LIKE \"%$region_array[2]%\"

OR region LIKE \"%$region_array[3]%\"";

}

 

Is it possible to use IF or WHILE statements within the query string?  Or loops?  And, if so, what is the proper syntax?  Perhaps something like this:

 

$query="SELECT * FROM bar_index WHERE region

    for ($i=0;$i<$num_rows_selected;$i++)

          {LIKE \"%$region_array['$i']%\""}

 

Or something like that?  Any help and insight is greatly appreciated.

[mrdamien]

Not directly in the query like that, but you can use concatenation to construct the query + loops:

 

for($i = 1; $i<$num_rows_selected;$i++){
  $part2 .= " OR region LIKE '%$region_array[$i]%'";
}
$query="select * FROM bar_index WHERE region LIKE '%$region_array[0]%' " . $part2;

[toplay]

Example:

for ($i = 0; $i < $num_rows_selected; $i++) {
    $arrWhereClause[] = sprintf('`region` LIKE "%%%s%%"', $region_array[$i]);
}

$query = sprintf(  "SELECT * FROM `bar_index` WHERE %s"
                 , join(' OR ', $arrWhereClause)
                );

 

 

Depending on version of MySQL, it takes longer to process "OR" conditions, so using UNION is better. Example:

for ($i = 0; $i < $num_rows_selected; $i++) {
    $arrQueries[] = sprintf('(SELECT * FROM `bar_index` WHERE `region` LIKE "%%%s%%")', $region_array[$i]);
}

$query = join(' UNION ', $arrQueries);

 

FYI:

This is not what you need here, but just thought to point out that MySQL has an IF() function, and IF statements that can be used in queries:

 

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

 

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html