Array in SQL Query?

Question

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

'OR' is the SQL equivalent of the devil and will slow down even a small request. — Raveline, Mar 14 '11 at 07:16
don't forget to `foreach($myarray as $key => $val) $myarray[$key] = mysql_real_escape_string($val);` — Your Common Sense, Mar 14 '11 at 09:09

Tim · Accepted Answer · 2011-03-14T07:23:29.387

14

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);

edited Mar 14 '11 at 07:23

answered Mar 14 '11 at 07:18

Tim

9,351
1
32
48

1

i think he needs to add quotes to the strings on the array though.. – amosrivera Mar 14 '11 at 07:19
1

@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 09:28

score 10 · Answer 2 · edited Mar 19 '15 at 12:00

10

$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);

edited Mar 19 '15 at 12:00

nehmeroumani

3
3

answered Mar 14 '11 at 09:23

Your Common Sense

156,878
40
214
345

Muthu Kumaran · Answer 3 · 2018-01-29T04:12:53.287

3

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).');

edited Jan 29 '18 at 04:12

answered Mar 14 '11 at 07:25

Muthu Kumaran

17,682
5
47
70

score 2 · Answer 4 · answered Mar 14 '11 at 07:19

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.

Array in SQL Query?

4 Answers4

Linked

Related