Page 1 of 1

From SELECT MULTIPLE to MySQL query

Posted: Mon Mar 03, 2003 9:42 am
by bluenote
:roll: Hi there,

a prob:

I have a form wich contains a SELECT MULTIPLE element like this:

Code: Select all

<?php

// a databse query for the $anumrows values

echo "<FORM NAME="whois_advanced_$LANG" ACTION="03.php3" METHOD="GET" onreset="return confirmReset(this)">\n";
echo "<INPUT TYPE="HIDDEN" NAME="LANG" VALUE="$LANG">\n";
echo "<INPUT TYPE="HIDDEN" NAME="method" VALUE="$method">\n";
echo "<INPUT TYPE="HIDDEN" NAME="view" VALUE="$view">\n";
echo "<INPUT TYPE="HIDDEN" NAME="search_mode" VALUE="$search_mode">\n";
			
echo "<SELECT NAME="department[]" SIZE=5 MULTIPLE>\n";
			
if ($anumrows > '0'){
				
$a = 0;
				
while ($a < $anumrows){
					
$dep_s03 = mysql_result($aerg,$a,"dep_s03");
$dep_s05 = mysql_result($aerg,$a,"dep_s05");
					
echo "<OPTION VALUE="$dep_s03">"; echo strtr($dep_s05,$short_names); echo "</OPTION>\n";
					
$a++;}}
					
echo "</SELECT>\n";


?>
The variable $dep_s03 can contain numbers from 01 up to 99. After filling out, the form is send to another PHP script which shall run a query on another table using (amongst others) the values from the SELECT MULTIPLE element in its WHERE clause:

Code: Select all

<?php

$tquery = "SELECT staff.s01, staff.s02, staff.s03, staff.s04, staff.s05, staff.s06, staff.s11, staff.s21, staff.s22, staff.s23, staff.s27, staff.s28, staff.s29, staff.s30, staff.s31, staff.s39, staff.s46, staff.s47, staff.s48, staff.s49, staff_departments.dep_s03, staff_departments.dep_s04 FROM staff LEFT JOIN staff_departments ON staff.s03 = staff_departments.dep_s03 WHERE (staff.s05 LIKE '$user%') $connection_01 (staff.s03 LIKE '%$department%') $connection_02 (staff.s01 = '$status') ORDER BY staff.s02 ASC, staff.s05 ASC";
		
?>
$connection_01 / 02 can be AND or OR. If I'd like to print out $department, I would do the following:

Code: Select all

<?php

$department = $_REQUEST["department"];

foreach ($department as $value) {
	
echo $value." ";}

?>
But: How can I get the $department[] values (for example: 02 03 18 23) into the LIKE '%$department%' part of the query??

Thanx for help in advance, yours bluenote

Posted: Mon Mar 03, 2003 10:21 am
by daven
You could use a for/foreach loop to go through the array.

Code: Select all

<?php
$query=' . . . .WHERE . . .";
for($i=0;$i<count($department);$i++){
$query .= "staff.s03 LIKE '%$department[$i]%'";
}?>

Posted: Mon Mar 03, 2003 10:53 am
by pootergeist
or use mysqls IN method

WHERE a IN (9,8,7,6,5)

would return all rows where a was 9,8,7,6 OR 5
the parenthesised values need to be comma seperated though