MySQL SELECT statement where value is in array

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

Answer 2

$fruit = $_GET['fruit'];
$query1= sprintf("SELECT * FROM tbl_fruits WHERE fruit LIKE '%%s%'",
        mysql_real_escape_string($fruit ));

Answer 3

Try below sql

$fruit = $_GET['fruit'];
$query1= "SELECT * FROM tbl_fruits WHERE fruit LIKE '%".$fruit."%'";

Answer 4

Use a LIKE clause:

SELECT * FROM tbl_fruits
    WHERE fruit LIKE '%apple%';

Answer 5

select * from tbl_fruits WHERE fruit like '%melon%'

Answer 6

As mentioned before you can/should build your table structure differently.
It's the "relational" in "relational database". see http://en.wikipedia.org/wiki/Database_normalization

As always: not a silver bullet. There are other kinds of daabases and there can be rasons for not normalizing (parts of) tables. But anyway, here's an sscce using PDO:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');  
//echo 'client version: ', $pdo->getAttribute(PDO::ATTR_CLIENT_VERSION), "\n";
//echo 'server version: ', $pdo->getAttribute(PDO::ATTR_SERVER_VERSION), "\n";
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
setup($pdo);


$stmt = $pdo->prepare("
    SELECT
        m.name
    FROM
        soFruits as f
    JOIN
        soMixXFruits as x
    ON
        f.id=x.idFruit
    JOIN
        soFruitMix as m
    ON
        x.idMix=m.id
    WHERE
        f.name=?
");
$stmt->setFetchMode(PDO::FETCH_ASSOC);
foo($stmt, 'apple');
foo($stmt, 'melon');


function foo(PDOStatement $stmt, $param) {
    echo "----- $param -----\r\n";
    $stmt->execute( array($param) );
    foreach($stmt as $r) {
        echo join(', ', $r), "\r\n";
    }   
}



function setup($pdo) {
    $queries = array(
        '
            CREATE TEMPORARY TABLE soFruits (
                id INT auto_increment,
                name varchar(64),
                primary key(id)
            )
        ',
        '
            CREATE TEMPORARY TABLE soFruitMix (
                id INT auto_increment,
                name varchar(32),
                primary key(id)
            )
        ',
        '
            CREATE TEMPORARY TABLE soMixXFruits (
                idMix int,
                idFruit int,
                primary key(idMix, idFruit)
            )
        ',
        "INSERT INTO soFruits (id,name) VALUES (1,'apple'),(2,'cherry'),(3,'grape'),(4,'kiwi'),(5,'lemon'),(6,'melon'),(7,'orange'),(8,'pear')",
        "INSERT INTO soFruitMix (id,name) VALUES (1, 'mix1'),(2,'mix2'),(3,'mix3'),(4,'mix4')",
    );
    foreach( $queries as $q ) {
        $pdo->exec($q);
    }

    $data = [
        '1'=>['apple','orange','pear','grape','lemon'],
        '2'=>['pear','melon','apple','kiwi','lemon'],
        '3'=>['orange','kiwi','pear','apple','cherry'],
        '4'=>['grape','lemon','cherry','apple','melon']
    ];

    $stmt = $pdo->prepare('
        INSERT INTO soMixXFruits (idMix, idFruit)
            SELECT
                :idMix, id
            FROM
                soFruits
            WHERE
                name=:nameFruit
    ');
    $stmt->bindParam('idMix', $idMix);
    $stmt->bindParam('nameFruit', $nameFruit);

    foreach($data as $idMix=>$mix) {
        foreach($mix as $nameFruit) {
            $stmt->execute();
        }
    }
}

Take a look at the table definitions in function setup($pdo). You have entities like fruits and mixes and then there's a table that represents their relationship.

side-note: I didn't pay attention to the indicies. But for performance that is usually crucial, see http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/