I need some help with an MySQL statement that I cannot really make to work.

I have a table like this:

+---+-------------------------------+

|id | fruit |

+---+-------------------------------+

| 1 | apple,orange,pear,grape,lemon |

| 2 | pear,melon,apple,kiwi,lemon |

| 3 | orange,kiwi,pear,apple,cherry |

| 4 | grape,lemon,cherry,apple,melon|

+---+-------------------------------+

What I need to do is to SELECT all rows where the column fruit contains the word melon. The word in question might be at any position in the array.

I tired with the below query but for some reason I only get 3-4 rows, definitely not all of them:

$fruit = $_GET['fruit'];

$query1= "SELECT * FROM tbl_fruits WHERE ".$fruit." IN (fruit)";

Any comments will be appreciated.

解决方案

You can use FIND_IN_SET

SELECT * FROM tbl_fruits

WHERE find_in_set('$fruit', fruit)

But you actually should rather change your table design.

Never store multiple values in a single column!

A better table design would be

fruits table

------------

id name

1 melon

2 orange

3 apple

...

products table

-------------------

id name price

1 P1 1.50

2 P2 2.99

3 P3 0.99

product_fruits table

--------------------

product_id fruit_id

1 1

1 2

2 2

3 1

That is a classic many to many relation (m to n).