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.

Regarding your coding style: never, ever make queries using String concatenation. — meskobalazs
– meskobalazs, Commented Feb 4, 2015 at 10:59
SELECT * FROM tbl_fruits WHERE find_in_set('$fruit', fruit) catch it in the query — Danyal Sandeelo
– Danyal Sandeelo, Commented Feb 4, 2015 at 10:59
@ meskobalazs, point taken. May I bother you to explain why this should not be done. As you can see I am still learning so any comments that bring some knowledge are appreciated. — user3132858
– user3132858, Commented Feb 4, 2015 at 11:09

juergen d · Accepted Answer · 2015-02-04 11:36:41Z

10

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

edited Feb 4, 2015 at 11:36

answered Feb 4, 2015 at 10:59

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3132858 Over a year ago

that work great, thank you juergen. On the table design, I have decided to do it like this since I have 4 columns containing 5 values each. so I had to choose between having 20 different columns, one for each value or having only 4 columns with an array with 5 values. Second option seemed like a better choice for simplicity reasons.

juergen d Over a year ago

I expand my answer to a table design that would be better. Give me a minuite

juergen d Over a year ago

What is the name and purpose of your original table?

user3132858 Over a year ago

The table contains a list of products - about 17000 rows. So each product has a tag attached to it. Then I am showing a list of products that share the same tag. So products that have the tag apple appear in the list with apples and so on. In the same fashion I have 3 more columns with 5 tags each that display the product on other relevant pages throughout the website.

harry · Accepted Answer · 2015-02-04 11:09:35Z

2

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

edited Feb 4, 2015 at 11:09

answered Feb 4, 2015 at 11:00

harry

1,0072 gold badges10 silver badges19 bronze badges

2 Comments

harry Over a year ago

@Robert I think now its sql injection free.

Robert Over a year ago

After you edited it is but when I post the comment it wasn't. Now th ecode won't compile btw. Syntax error.

v2solutions.com · Accepted Answer · 2015-02-04 11:00:20Z

0

Try below sql

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

answered Feb 4, 2015 at 11:00

v2solutions.com

1,4399 silver badges8 bronze badges

Comments

Savv · Accepted Answer · 2015-02-04 11:01:26Z

0

Use a LIKE clause:

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

answered Feb 4, 2015 at 11:01

Savv

4312 silver badges7 bronze badges

Comments

웃웃웃웃웃 · Accepted Answer · 2015-02-04 11:04:31Z

0

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

edited Feb 4, 2015 at 11:04

웃웃웃웃웃

12k15 gold badges63 silver badges95 bronze badges

answered Feb 4, 2015 at 11:02

mikepanda

32 bronze badges

1 Comment

Jeremy J Starcher Over a year ago

That would also catch "watermelon". Now, if the OP was able to start and end all of his string with commas so they looked like ,apple,orange,pear,grape,lemon,, then he could use a like '%,melon,%'

VolkerK · Accepted Answer · 2015-02-04 11:31:29Z

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/

Collectives™ on Stack Overflow

MySQL SELECT statement where value is in array

6 Answers 6

But you actually should rather change your table design.

4 Comments

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

But you actually should rather change your table design.

4 Comments

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related