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)) &#123;
$KO=$row&#1111;0];
$NI=$row&#1111;2];
$KI=$row&#1111;3];
$HI=$row&#1111;25];
echo"$NI $NI $HI<br>";
&#125; 
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)
&#123;
   $query .= " where code='&#123;$variable&#1111;0]&#125;'";
   for ($i=1; $i<$nArrLen; $++)
   &#123;
      $query.=" or code='&#123;$variable&#1111;$i]&#125;'";
   &#125;
&#125;
  
$result = mysql_query($query)  or die(mysql_error());
echo "\t<ul>\n";
while ($row = mysql_fetch_row($result)) &#123;
   //$KO=$row&#1111;0];
   //$NI=$row&#1111;2];
   //$KI=$row&#1111;3];
   //$HI=$row&#1111;25];
   echo $row&#1111;2].' '.$row&#1111;3].' '.$row&#1111;25];
   // or:  echo "&#123;$row&#1111;2]&#125; &#123;$row&#1111;3]&#125; &#123;$row&#1111;25]&#125;";
&#125; 
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 :roll:

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