MySQL: list of ids in where query

Asked Jul 29 '19 at 20:07

Active Jul 29 '19 at 22:22

Viewed 131 times

I'm trying to insert a list of ids in a query as such:

    $base_query = $base_query . " 
LEFT 
JOIN {$wpdb->prefix}wpc_client_objects_assigns as o 
    ON o.object_id = f.id 
 WHERE f.cat_id = %d 
   AND o.assign_id IN (%s)";
    $base_query = $wpdb->prepare($base_query, $request['cat'], $customer_ids);

I can log the query:

SELECT * 
  FROM kh05R1_wpc_client_files as f 
  LEFT 
  JOIN kh05R1_wpc_client_objects_assigns as o 
    ON o.object_id = f.id 
 WHERE o.assign_id IN ('138,41,78');

This query only returns the first id.

How can I fix this query to allow an array of ids (or strings)?

edited Jul 29 '19 at 22:22

Strawberry

33,750
13
40
57

asked Jul 29 '19 at 20:07

AlxVallejo

3,066
6
50
74

you need to remove the quotes around the numbers in the IN operator. – PeterHe Jul 29 '19 at 20:10
I'm not setting the quotes tho. That's how the query is output. I'm setting it to a %s tho. – AlxVallejo Jul 29 '19 at 20:12
@stickybit you mean the question where the accepted answer introduces SQL injection? :thumbsup – AlxVallejo Jul 29 '19 at 20:17
@AlxVallejo: Look at the other answers, especially the one that got the bounty and currently has 292 upvotes. – sticky bit Jul 29 '19 at 20:23
`LEFT JOIN o... WHERE o...` is the same as `INNER JOIN o`, so either write it that way, if that's what you want, or move the WHERE condition to the ON clause. – Strawberry Jul 29 '19 at 22:25

MySQL: list of ids in where query

0 Answers0