Get from mysql table where multiple id in one cell

Question

I have a table invoices:

    id|orders|user|status|
    1 |1,2,5 |1   |1     |
    2 |75    |2   |0     |
    3 |31,4  |5   |1     |

What should be my request to the database so that I receive the invoice status by the order number in the orders column.

It seems to me that the way to use LIKE here is not suitable as it will select incorrect values.

    $sql = "SELECT status FROM invoices WHERE orders LIKE '%".$order_id."%'";

You normalize the table and stop saving multiple values on same field. — Juan Carlos Oropeza, Feb 25 '19 at 19:19
Agree with Juan. You're setting yourself up for a coding nightmare doing it that way. One value per row is the standard. — dmikester1, Feb 25 '19 at 19:22
Also use parameterized queries to avoid sql injection. https://xkcd.com/327/ — Juan Carlos Oropeza, Feb 25 '19 at 19:22
Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Progman, Feb 25 '19 at 20:41

score 1 · Accepted Answer · answered Feb 25 '19 at 19:21

1

What you want is

 $sql = "SELECT status 
         FROM invoices 
         WHERE CONCAT(',', orders, ',') LIKE '%,".$order_id.",%'";

answered Feb 25 '19 at 19:21

Juan Carlos Oropeza

47,252
12
78
118

For some reason, it does not always give the correct value. – user3514052 Feb 25 '19 at 19:43
What cases doesn't found the correct value? do you have spaces on the orders string? – Juan Carlos Oropeza Feb 25 '19 at 19:45
No, I dont have spaces. For ex. cant find any id from this "605,604,415,367,339,338" but can find from this "512,511" or from this "19". Mystic The same with the query that was written by Florent above – user3514052 Feb 25 '19 at 20:29
Also for some reason if i searching status form order_id 250 it returns 1, but real status is 0 – user3514052 Feb 25 '19 at 20:36
Can you be more precise. What is the id you are looking and what are the orders on the db. because if you are looking for multiple id, you wont be able to do it. And 19 isnt on that list – Juan Carlos Oropeza Feb 25 '19 at 20:41
I tried run that query from phpmyadmin and it works correctly. But when I run with php at my page it work not correctly. – user3514052 Feb 25 '19 at 20:46
Then you problem is the php. I just give you the working query – Juan Carlos Oropeza Feb 25 '19 at 20:52
Yes, I will mark your answer correct. I will look for the problem myself. Thank. – user3514052 Feb 25 '19 at 20:54
Still you should consider normalize your db, That will save you headaches in the long run. – Juan Carlos Oropeza Feb 25 '19 at 21:10

score 0 · Answer 2 · answered Feb 25 '19 at 19:34

0

You can try this assuming no space is used

$sql =<<<REQQ
SELECT status 
FROM invoices 
WHERE orders LIKE '{$order_id}'
OR orders LIKE '{$order_id},%'
OR orders LIKE '%,{$order_id},%'
OR orders LIKE '%,{$order_id}'
REQQ;
//end

answered Feb 25 '19 at 19:34

Florent

436
3
8

Get from mysql table where multiple id in one cell

2 Answers2