PHP SELECT returns empty using IN and implode

Question

I have been at this for almost two hours and I'm pretty sure I'm just missing something simple. I have tried multiple iterations without success. SO searches have given insights but nothing exactly like this to see were I'm going wrong. Your input will be appreciated.

I have an array built [$prime_ids] made up of user IDs (individual email addresses). This is the look of the finished array:

["[email protected]", "[email protected]", "[email protected]", ...

Now I need to find matches in this list against matches in a database table. Note: there can be multiple matches with the information contained in the table (meaning a single user ID may show up multiple times in the list). That is being done for a specific reason.

My understanding is I need to use implode to make this work. Here is my query:

$sql_query = "SELECT * FROM worksheet4 WHERE worksheet4.user_id IN (" . implode(",", $prime_ids) . ");";
$result = $dbc->query($sql_query);

I then run a loop to build a second array ($kp_positions). Here is my code:

while($row = mysqli_fetch_assoc($result)) {
    $kp_positions[] = $row;
}

This returns an empty array. While debugging I tried the same SELECT query using WHERE instead of IN and implode:

WHERE user_id = '[email protected]'";

... and it works perfectly (but only for this single-user). Thinking this might have something to do with single quotes (') in the implode statement I tried every possible combination that made sense without avail. Because this works perfectly with a single user ID, where my going wrong in my implode statement? Or, am I going about this the wrong way? Thank you.

You need to quote the parts, IN ('" . implode("','", $prime_ids) . "') — Jared Farrish
– Jared Farrish, Commented Feb 11, 2021 at 21:20
The reason you won't find anything "exactly like this" is because you haven't broken the problem down: your database doesn't know about PHP, and PHP doesn't know about SQL. Rather than running $sql_query directly, echo it to the screen, and paste it into your database manually using a tool like PHPMyAdmin or MySQL WorkBench. Get it working in there, and then you'll see how to make it work in PHP. — IMSoP
– IMSoP, Commented Feb 11, 2021 at 21:21

Eden Moshe · Accepted Answer · 2021-02-11 21:21:48Z

4

if you print your query you will get something like this:

 SELECT * FROM worksheet4 WHERE worksheet4.user_id IN ([email protected],[email protected],[email protected]);

Note that each member within the "in" should be wrapped in quotes so try to run this line:

$sql_query = "SELECT * FROM worksheet4 WHERE worksheet4.user_id IN ('" . implode("','", $prime_ids) . "');";

and get

SELECT * FROM worksheet4 WHERE worksheet4.user_id IN ('[email protected]','[email protected]','[email protected]');

answered Feb 11, 2021 at 21:21

Eden Moshe

1,1571 gold badge6 silver badges18 bronze badges

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

3 Comments

Neil_Tinkerer Over a year ago

I tried your exact configuration (even though I think it was one of my previous attempts) and it still returns an empty array. Could I ask when you say "if you print your query ..." how do you do that? (You can see I'm fairly new to this.)

Eden Moshe Over a year ago

you can use debugger or use this php code ` die ($sql_query); `, it's print the content of sql_query, and exit. @Neil_Tinkerer

Neil_Tinkerer Over a year ago

Thank you for the suggestion. I'm going to give this technique a try to see what I can learn. Much appreciated.

Jared Farrish · Accepted Answer · 2021-02-11 21:25:41Z

1

Another option, in my opinion better, is to use json_encode() to quote it.

$prime_ids = array_map('json_encode', $prime_ids)

https://3v4l.org/HPL2o

Then without quotes, which json_encode() adds for you:

$sql_query = "SELECT ... IN (" . implode(",", $prime_ids) . ")";

This will escape quotes that may exist, which is good.

answered Feb 11, 2021 at 21:25

Jared Farrish

49.4k17 gold badges100 silver badges108 bronze badges

1 Comment

Neil_Tinkerer Over a year ago

This worked! Thank you! I'm certain the other suggestions have merit as well because it must be the double or single quotes I need to look at and LEARN. In the meantime, you've provided a working solution. Again, thank you.

Collectives™ on Stack Overflow

PHP SELECT returns empty using IN and implode

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related