Array in SQL Query? [duplicate]

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
– Raveline, Commented Mar 14, 2011 at 7:16
don't forget to foreach($myarray as $key => $val) $myarray[$key] = mysql_real_escape_string($val); — Your Common Sense
– Your Common Sense, Commented Mar 14, 2011 at 9:09

Tim · Accepted Answer · 2011-03-14 07:23:29Z

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, 2011 at 7:23

answered Mar 14, 2011 at 7:18

Tim

9,5191 gold badge34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

amosrivera Over a year ago

i think he needs to add quotes to the strings on the array though..

Your Common Sense Over a year ago

@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 · Accepted Answer · 2015-03-19 12:00:29Z

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, 2015 at 12:00

user4523908

answered Mar 14, 2011 at 9:23

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Comments

Muthu Kumaran · Accepted Answer · 2018-01-29 04:12:53Z

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, 2018 at 4:12

answered Mar 14, 2011 at 7:25

Muthu Kumaran

18k5 gold badges50 silver badges72 bronze badges

Comments

Hamish · Accepted Answer · 2011-03-14 07:19:04Z

2

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.

answered Mar 14, 2011 at 7:19

Hamish

23.4k8 gold badges57 silver badges68 bronze badges

Collectives™ on Stack Overflow

Array in SQL Query? [duplicate]

4 Answers 4

2 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Linked

Related