MYSQLI - WHERE IN array

Question

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.

score 7 · Accepted Answer · edited Mar 16 '23 at 06:05

7

Explode it in array first

$list = explode(', ', 'food, drink, cooking');

Then bind each element into query using separate placeholder.

$in    = str_repeat('?,', count($list) - 1) . '?'; // placeholders
$sql   = "SELECT * FROM table WHERE stuff IN ($in)"; // sql
$stmt  = $mysqli->prepare($sql); // prepare
$types = str_repeat('s', count($array)); //types
$stmt->bind_param($types, ...$array); // bind array at once
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$data = $result->fetch_all(MYSQLI_ASSOC); // fetch the data

edited Mar 16 '23 at 06:05

Your Common Sense

156,878
40
214
345

answered Mar 26 '16 at 21:43

Kylie

11,421
11
47
78

2

The array method will not work. – Charlotte Dunois Mar 26 '16 at 21:59
the simplest thing, damn! thanks, this solved the problem perfectly - and thank you to everybody else who commented! – Mar 26 '16 at 22:08
@PHPlearnerproject you have to realize that this code has noting to do with perfectness as it's essentially prone to SQL injection. – Your Common Sense Mar 27 '16 at 09:33
@YourCommonSense show me exactly how this is prone to Injection. Where are the inputs? How would the user inject? I understand what you're saying but in this context its not true. Unless you have another way? – Kylie Mar 27 '16 at 10:18
Yes. There is another way. Called prepared statements – Your Common Sense Mar 27 '16 at 10:50
1

I meant another way to SQL inject. Without the variable coming from an input. Which in this case its not. Like how would you go about injecting into this code?? Just curious? Im asking legitimately – Kylie Mar 27 '16 at 11:30
3

It doesn't matter where a variable is coming from. Prepared statement have to be used unconditionally. – Your Common Sense Mar 28 '16 at 04:45

score 2 · Answer 2 · answered Mar 26 '16 at 22:03

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".

MYSQLI - WHERE IN array

2 Answers2