MySQL SELECT语句，其中value在数组中

Question

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.

Answer 1

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).