Page 1 of 1
How can I search mysql table with array?
Posted: Fri Aug 02, 2002 2:16 pm
by sebamed
this is what i have come up so far:
but unfortunately it doesn't work
Code: Select all
$variable =array(123, 124, 125);
$search = split($variable);
$query = "SELECT * FROM data where code='$search'";
$result = mysql_query($query)
or die("Quary failed");
echo "\t<ul>\n";
while ($row = mysql_fetch_row($result)) {
$KO=$rowї0];
$NI=$rowї2];
$KI=$rowї3];
$HI=$rowї25];
echo"$NI $NI $HI<br>";
}
echo "\t</ul>\n";
Posted: Fri Aug 02, 2002 5:17 pm
by volka
you will retrieve all rows, where code contains "123 124 125" not all records where code is "123" or "123" or "125".
try something like
Code: Select all
$variable =array(123, 124, 125);
$query = 'SELECT * FROM data';
$nArrLen = count($variable);
if ($nArrLen > 0)
{
$query .= " where code='{$variableї0]}'";
for ($i=1; $i<$nArrLen; $++)
{
$query.=" or code='{$variableї$i]}'";
}
}
$result = mysql_query($query) or die(mysql_error());
echo "\t<ul>\n";
while ($row = mysql_fetch_row($result)) {
//$KO=$rowї0];
//$NI=$rowї2];
//$KI=$rowї3];
//$HI=$rowї25];
echo $rowї2].' '.$rowї3].' '.$rowї25];
// or: echo "{$rowї2]} {$rowї3]} {$rowї25]}";
}
echo "\t</ul>\n";
assuming you don't know what $variable contains before the script runs. If you do, you may simply write your query as
Code: Select all
$query = 'SELECT * FROM data where code='123' or code='124' or code='125'";
$result = mysql_query($query) or die(mysql_error());
remeber that quoting with '' is only necessary if you're comparing strings. if the code-field is a number in db
Code: Select all
$query = 'SELECT * FROM data where code=123 or code=124 or code=125";
will do
and refering to "$HI=$row[25];": if you only need three fields you may consider limiting your select to these three fields i.e.
Code: Select all
$query = "SELECT NI,KI,HI from data";
p.s.: code not tested (not even by compiler

)
Posted: Mon Aug 05, 2002 9:43 am
by mikeq
You need to convert your array to a string and use 'IN' within the where clause
Code: Select all
$SearchArray =array(123, 124, 125);
$CommaSeperatedList = implode(",",$SearchArray);
//will give a string like 123,124,125
$query = "SELECT * FROM data where code IN ($CommaSeperatedList)";
$result = mysql_query($query)
or die("Quary failed");
Posted: Mon Aug 05, 2002 12:20 pm
by volka
ok ok, 'IN' is much easier

Posted: Mon Aug 05, 2002 12:48 pm
by twigletmac
and one of those things that isn't as obvious in the manual as it should be...
Mac