Kalaivanan Kalaivanan - 2 months ago 6
MySQL Question

how to use WHERE condition to find specific value from string in table?

NEED:

I need to select extract value in the field using mysql. The value are separated by commas.

MYSQL TABLE:

id | city | cusine_type
------------------------------------
1 | Coimbatore | 3 Star Hotel
2 | Coimbatore | 5 Star Hotel, Bakery
3 | Coimbatore | 3 Star Hotel, Ice Cream Store
4 | Coimbatore | Star Hotel, Restaurant


MY PHP AND MYSQL CODE

include "db.php";
$city='Coimbatore';
$cus_type='Star Hotel';
$qy=mysqli_query($con, "SELECT * FROM add_res where city='$city' and cusine_type REGEXP CONCAT(',?[$cus_type],?') ");


PROBLEM:

The above code select all the rows in a table. I need only row 4.

Answer

You can do

cusine_type LIKE '%$cus_type%'

Edit based on your edited question. A good option is

FIND_IN_SET('$cus_type',cusine_type)

Fiddle

How can I prevent SQL-injection in PHP?