0

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.

Kalaivanan
  • 459
  • 2
  • 8
  • 17

1 Answers1

2

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?

Community
  • 1
  • 1
Hanky Panky
  • 46,730
  • 8
  • 72
  • 95
  • Sorry Friend. This code is not worked. When Using this code, no rows is fetched – Kalaivanan Jun 02 '15 at 06:16
  • But a couple of mins ago you said it was working perfectly. What's the error? – Hanky Panky Jun 02 '15 at 06:16
  • When adding a new cusine type "Start Hotel" its not working. It fetch all the rows. – Kalaivanan Jun 02 '15 at 06:20
  • can you give solution for below. `$cus_type=$_POST['cus_type']; if($cus_type=='all') { $cus_type='*'; } else { $cus_type=$_POST['cus_type'] } include "db.php"; $ff=mysqli_query($con, "SELECT * FROM add_res where cusine_type=$cus_type");` – Kalaivanan Jun 02 '15 at 06:29
  • echo the constructed statement; you will probably see the problem. One problem in the original query was the misunderstanding of what [] means in a REGEXP. – Rick James Jun 08 '15 at 04:20