0

I have trouble returning data in rows in MySQL prepared statement using string binding with commas as WHERE IN condition.

My string is as follows:

$order_ids = '200,201,202';

And here's my query statement:

$stmt = $conn->prepare("
SELECT id FROM TABLE
WHERE t.order_id IN (:order_ids)
");

$stmt->bindParam(':order_ids', $order_ids);

$stmt->execute();

while($row = $stmt->fetch()) {
  echo $row['id'];
}

I'm supposed to get id 200, 201, 202 from the rows returns but it returns only the first row.

It works if I hardcoded the exact ids into the query statement without params binding, like so:

$stmt = $conn->prepare("
SELECT id FROM TABLE
WHERE t.order_id IN (200,201,202)
");
Joe S
  • 83
  • 1
  • 1
  • 11
  • 2
    You could try [FIND_IN_SET(needle,haystack)](https://www.mysqltutorial.org/mysql-find_in_set/), it uses a string for the haystack. In my experience it's slower than IN but it might be a solution to your problem? – KIKO Software Nov 26 '21 at 10:11
  • Try putting these into array like `$order_ids = [200,201,202];` – shirshak007 Nov 26 '21 at 10:16
  • @shirshak007 that doesn't seem to work either. – Joe S Nov 26 '21 at 10:25

1 Answers1

-1

does your table look somewhat like this?

    TABLE
================================
| id   |   order_ids| 
--------------------------------
| 1    |     200,201,202  |
--------------------------------
| 2    |     150,180,181  |

If yes, then it will not work because the IN in your case checks if t.order_id = 200 or t.order_id = 201 or .... and so on. I would advise you to normalize the table, as explained here on What is Normalisation (or Normalization)?

Dokik
  • 364
  • 2
  • 9