Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
2

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

share|improve this question
1  
'OR' is the SQL equivalent of the devil and will slow down even a small request. – Raveline Mar 14 '11 at 7:16
possible duplicate stackoverflow.com/questions/907806/… – amosrivera Mar 14 '11 at 7:20
possible duplicate of MySQL number of items within "in clause" – krock Mar 14 '11 at 7:33
don't forget to foreach($myarray as $key => $val) $myarray[$key] = mysql_real_escape_string($val); – Your Common Sense Mar 14 '11 at 9:09

4 Answers

up vote 4 down vote accepted

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);
share|improve this answer
1  
i think he needs to add quotes to the strings on the array though.. – amosrivera Mar 14 '11 at 7:19
oww yeah true, stupid me. I'll edit my answer – Tim Mar 14 '11 at 7:22
thank you very much – njaknjak Mar 14 '11 at 7:29
@njaknjak thats obviously wrong answer. you don't have to add quotes manually - it's job for PHP. and you have to escape strings – Your Common Sense Mar 14 '11 at 9:28
up vote 4 down vote 
$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 ($newarray);
share|improve this answer
up vote 2 down vote

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.

share|improve this answer
up vote 0 down vote

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).');
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.