MYSQLI - WHERE IN array [duplicate]

问题:

This question already has an answer here:

Bind multiple parameters into mysqli query (1 answer)

Closed 3 years ago.

I've looked all over the internet for answers on this one, and prepared statements and bind params come up (I have no idea what that stuff is)

Basically, I have a comma separated list

$list = 'food, drink, cooking';

Ok, now I want to search for each of those items in a column of the database... Sounds simple, right?

$query = "SELECT * FROM table WHERE stuff IN ('$list')";
$runquery = mysqli_query($connection, $query);
while($row = mysqli_fetch_array($runquery,MYSQLI_ASSOC)){
    $variable = $row;
}

Then later on,

var_dump($variable);

UNDEFINED VARIABLE

Why? I can't see anything wrong with the code. It works if I put a particular value, and I have tested it with WHERE stuff=$item - that works fine.

So it's not the variables / database, it's an error in the IN statement. I don't understand why it won't work.

回答1:

Each element needs quotes around it

$list = "'food', 'drink', 'cooking'";
$query = "SELECT * FROM table WHERE stuff IN ($list)";

Or if you had an array

$array = array("food","drink","cooking");
$query = "SELECT * FROM table WHERE stuff IN (".implode(',', $array).")";

回答2:

I suspect that anytime the query doesn't return a row, you're going to get that UNDEFINED VARIABLE. We don't see that the variable is defined/initialized anywhere except after a row is fetched.

As far as why the query doesn't return a row, your query is equivalent to

   ... WHERE stuff = 'food, drink, cooking'

That's a single string literal. The query is only going to return rows were the column stuff is equal to that string. The commas inside that string are just part of the value. If you want to find rows where stuff contains any of the values, your query would need to be of the form:

   ... WHERE stuff IN ('food','drink','cooking')

Note that this is three separate string literals, separated by commas. The commas are part of the SQL statement, not part of the value. This would return rows where the column stuff contains 'food'. Or 'drink'. Or 'cooking'.

That's why your query "doesn't work".

回答3:

Your list is $list = 'food, drink, cooking';. But you cannot paste it to WHERE IN. You should prepare it: explode $list string, then implode it with appropriate quotes and pass it to IN clause.

Try this code:

$list = 'food, drink, cooking';
$listArray = array_map('trim', explode(',', $list));
$listWhere = "('".implode("','", $listArray)."')";

$query = "SELECT * FROM table WHERE stuff IN ".$listWhere;
$runquery = mysqli_query($connection, $query);
while($row = mysqli_fetch_array($runquery,MYSQLI_ASSOC)){
  $variable = $row;
}