CODEWITHSUNDEEP
phpmysql
user3514052
user3514052

Reputation: 429

Get from mysql table where multiple id in one cell

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."%'";

Upvotes: 1

Views: 1281

Answers (2)

Florent
Florent

Reputation: 446

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

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

What you want is 

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

Upvotes: 1

Related Questions