Array in SQL Query? [duplicate]

10
votes

i have a problem making a SQL Query with an array in my WHERE clause.

For example:

My Array:

$myarray[1] = "hi";
$myarray[2] = "there";
$myarray[3] = "everybody";

My MySQL Statement:

SELECT * FROM myTable WHERE title='".$myarray[]."'

Is there any way to realize that? I solved it myself like this:

for(...) {
$where = $where." title='".$myarray[$count]."' OR ";
}
$where = substr($where , 0, -3);
.....
SELECT * FROM myTable WHERE ".$where."

But if i had thousands of entries in my array, the SQL Statement would be too big and slow, right?

Thanks

4
phpmysql
'OR' is the SQL equivalent of the devil and will slow down even a small request. - Raveline
don't forget to foreach($myarray as $key => $val) $myarray[$key] = mysql_real_escape_string($val); - Your Common Sense

4 Answers

15
votes

You can use mysql's IN-function

EDIT: As amosrevira said, you need to escape you strings in the array.

$myarray[1] = "'hi'";
$myarray[2] = "'there'";
$myarray[3] = "'everybody'";

$newarray = implode(", ", $myarray); //makes format 'hi', 'there', 'everybody' 

SELECT * FROM myTable WHERE title IN ($newarray);
9
votes
$myarray[1] = "hi";
$myarray[2] = "there";
$myarray[3] = "everybody";

//every quoted string should be escaped according to SQL rules
foreach($myarray as $key => $val) {
  $myarray[$key] = mysql_real_escape_string($val);
}

$in_str = "'".implode("', '", $myarray)."'"; //makes format 'hi', 'there', 'everybody' 

SELECT * FROM myTable WHERE title IN ($in_str);
3
votes

You can try use of IN in your WHERE clause,

SELECT * FROM myTable WHERE title IN ('hi', 'there', 'everybody');

or

SELECT * FROM myTable WHERE title IN ('.implode(',', $myarray).');
2
votes

You can us the IN operator. You want it to look like:

title IN ('hi', 'there', 'everybody')

So you'd do something like:

$sql = "SELECT * FROM myTable WHERE title IN '" . implode("','", $myarray) . "';"

Note that you need to filter your array for SQL injection issues first.