SELECTING WHERE from array in a mysql query

Question

I'm trying to SELECT from an array inside a mysql query

result = mysql_query("
SELECT * FROM fruit_db
WHERE fruit='".$url."'
")

$url has been created by $_GET and exploded into an array. It can be 'apple' or 'apple,banana,orange' for example. Not sure whether to implode back into string or not.

I need to be selecting 'apple' OR 'banana' OR 'orange', not 'apple,banana,orange'.

I'm used to counting the elements of the string, and looping through them, but not inside a mysql query, I can't work out if there's a simple way. I've tried something like

/*    
    result = mysql_query("
    SELECT * FROM fruit_db
    WHERE fruit='".$url[0]."'
    OR  fruit='".$url[1]."'
    // etc
    ")
*/

but I don't know how many elements there will be

You should use IN. And you should not use myqsl_* functions anymore. — moonwave99
– moonwave99, Commented Aug 13, 2013 at 11:29

GautamD31 · Accepted Answer · 2013-08-13 11:28:53Z

5

Try with IN with implode of the array $url like

$sql = "SELECT * FROM fruit_db WHERE fruit IN (".implode(',',$url).")";

Make sure that $url is an array.

And try to avoid mysql_* statements due to the entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_*, is officially deprecated as of PHP v5.5.0 and will be removed in the future.

There are two other MySQL extensions that you can better Use: MySQLi and PDO_MySQL, either of which can be used instead of ext/mysql.

answered Aug 13, 2013 at 11:28

GautamD31

28.8k10 gold badges66 silver badges86 bronze badges

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

3 Comments

gavin stanley Over a year ago

Will do. once it is all working I will replace the deprecated syntax, thanks. Do I simply replace 'mysql_query' with 'MySQLi' or 'PDO_MySQL'? And do they return the same type of resource?

GautamD31 Over a year ago

Yes ,May need some extra config for connection setup in mysqli_* and you can follow the using php.net/manual/en/book.mysqli.php

AdRock Over a year ago

Again, you beat me to it

som · Accepted Answer · 2013-08-13 11:40:43Z

2

"SELECT * FROM fruit_db WHERE fruit IN ('".implode("','", $url)."')";

Suppose your $url = array('apple','banana','orange');

Then sql will be :

SELECT * FROM fruit_db WHERE fruit IN ('apple','banana','orange')

edited Aug 13, 2013 at 11:40

answered Aug 13, 2013 at 11:29

som

4,6542 gold badges23 silver badges36 bronze badges

Comments

user2678106 · Accepted Answer · 2013-08-13 11:31:44Z

0

you may use

SELECT column_name FROM table_name WHERE column_name IN ("1","2");

answered Aug 13, 2013 at 11:31

user2678106

Comments

Chinmay235 · Accepted Answer · 2013-08-13 12:25:02Z

0

Try this

$url=explode(",",$_GET['fruit']);

foreach($url as $fr){
  $result = mysql_query("
  SELECT * FROM fruit_db
  WHERE fruit='".$fr."'
  ")
}

edited Aug 13, 2013 at 12:25

answered Aug 13, 2013 at 11:33

Chinmay235

3,4448 gold badges65 silver badges99 bronze badges

1 Comment

gavin stanley Over a year ago

This is kind of what I had before, can whoever down-voted explain why it's bad?

Collectives™ on Stack Overflow

SELECTING WHERE from array in a mysql query

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related