CODEWITHSUNDEEP
mysqlsqlcodeigniter
TheFoxLab
TheFoxLab

Reputation: 704

Fetch record from MySQL Table based on comma seperated value in column

I have fetching record from table named Pizza_Table.

Pizza_table Schema

id | pizza name | pizza_topping_ids
1  | pizza1     |1,2,3
2  | pizza2     |2,3
3  | pizza3     |4,5,6

Actually my functionality is searching pizza based on pizza_topping_ids I have work allot in this searching. but I can't find proper solution. .

I can't use IN clause for fetching record.

I have tried out :

1)

 SELECT * 
 FROM `pizza_table` 
 WHERE `pizza_topping_id` REGEXP '[[:<:]]2[[:>:]]'

Problem : fetch all record having value 2

2) 

SELECT * 
FROM `pizza_table` 
WHERE `pizza_topping_id` REGEXP '[[:<:]]1,2,3,4[[:>:]]'

but MySQL returned an empty result set,

If comma separated values are not match to any record then return result which is most having value in topping_id column.

EX: If i have run above query for find out pizza having 1,2,3,4 topping_id, but In database no pizza exist having such topping_id combination then result will be most covered id should be displayed like 'pizza1' having pizza_topping_id 1,2,3.

Sorry for bad English as well as format, but try to understand my problem. Thanks in advance.

Please help me.

Upvotes: 2

Views: 1970

Answers (4)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Try find_in_set of mysql useful in this kind of structures

$id = 2;
$query = $this->db->query("
                         SELECT * 
                         FROM `pizza_table` 
                         WHERE FIND_IN_SET($id,pizza_topping_id)";
return $query->result();

Upvotes: 0

John Woo
John Woo

Reputation: 263723

This may not answer your question directly but just only a suggestion :D

First, the rows on table pizza_topping doesn't need to have values separated as commas. It's hard to search for values like this using the current design. The values should be stored in rows not in column.

Consider this following schema,

CREATE TABLE PIZZA 
(
    PizzaID INT PRIMARY KEY,
    NAME VARCHAR(50) UNIQUE,
    PRICE DECIMAL(10,2)
);

INSERT INTO PIZZA VALUES (1,'Sunny Side Up Pizza', 120);
INSERT INTO PIZZA VALUES (2,'BBQ Chicken Pizza', 200);
INSERT INTO PIZZA VALUES (3,'Muffuletta Pizza', 175);
INSERT INTO PIZZA VALUES (4,'Caramelized Onion Pizza', 135);
INSERT INTO PIZZA VALUES (5,'Broccoli Deep Dish Pizza', 150);

CREATE TABLE TOPPINGS 
(
    ToppingID INT PRIMARY KEY,
    NAME VARCHAR(50) UNIQUE
);

INSERT INTO TOPPINGS VALUES (1,'Pepperoni');
INSERT INTO TOPPINGS VALUES (2,'Mushroom');
INSERT INTO TOPPINGS VALUES (3,'Sausage');
INSERT INTO TOPPINGS VALUES (4,'Cheese');
INSERT INTO TOPPINGS VALUES (5,'Garlic');
INSERT INTO TOPPINGS VALUES (6,'Ham');
INSERT INTO TOPPINGS VALUES (7,'Tomato Sauce');

And the the records for PIZZA_TOPPING should have multiple rows of ToppingID for each PizzaID.

CREATE TABLE PIZZA_TOPPINGS
(
    PizzaID INT,
    ToppingID INT,
    CONSTRAINT tb_fk1 FOREIGN KEY (PizzaID)
        REFERENCES Pizza(PizzaID),
    CONSTRAINT tb_fk2 FOREIGN KEY (ToppingID)
        REFERENCES TOPPINGS(ToppingID),
    CONSTRAINT tb_UQ UNIQUE (PizzaID, ToppingID)
);

INSERT INTO PIZZA_TOPPINGS VALUES (1,1);
INSERT INTO PIZZA_TOPPINGS VALUES (1,2);
INSERT INTO PIZZA_TOPPINGS VALUES (1,3);
INSERT INTO PIZZA_TOPPINGS VALUES (2,4);
INSERT INTO PIZZA_TOPPINGS VALUES (2,5);
INSERT INTO PIZZA_TOPPINGS VALUES (2,6);
INSERT INTO PIZZA_TOPPINGS VALUES (2,7);
INSERT INTO PIZZA_TOPPINGS VALUES (3,1);
INSERT INTO PIZZA_TOPPINGS VALUES (3,3);
INSERT INTO PIZZA_TOPPINGS VALUES (3,5);
INSERT INTO PIZZA_TOPPINGS VALUES (4,2);
INSERT INTO PIZZA_TOPPINGS VALUES (5,1);
INSERT INTO PIZZA_TOPPINGS VALUES (6,7);
INSERT INTO PIZZA_TOPPINGS VALUES (6,1);

The technique of searching records like this is called Relational Division

For example, you want to search for pizza's that have ingredients of: Pepperoni, Mushroom, Sausage. 

SELECT  a.PIZZAID, a.NAME, a.PRICE
FROM    Pizza a
        INNER JOIN Pizza_Toppings b
            ON a.PizzaID = b.PizzaID
        INNER JOIN Toppings c
            ON b.ToppingID = c.ToppingID
WHERE   c.Name IN ('Pepperoni', 'Mushroom', 'Sausage')
GROUP   BY a.PIZZAID, a.NAME, a.PRICE
HAVING  COUNT(*) = 3

or pizza's that constains of atleast: Pepperoni, Mushroom,

SELECT  a.PIZZAID, a.NAME, a.PRICE
FROM    Pizza a
        INNER JOIN Pizza_Toppings b
            ON a.PizzaID = b.PizzaID
        INNER JOIN Toppings c
            ON b.ToppingID = c.ToppingID
WHERE   c.Name IN ('Pepperoni', 'Mushroom')
GROUP   BY a.PIZZAID, a.NAME, a.PRICE
HAVING  COUNT(*) = 2

Theses are much better than using any other functions like FIND_IN_SET, REGEXP, etc..

Upvotes: 2

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

Just try this-

SELECT * FROM `pizza_table` WHERE  FIND_IN_SET(2,pizza_topping_id)

OR REGEXP

SELECT * FROM `pizza_table` WHERE pizza_topping_id REGEXP '^2,|,2$|,2,' OR pizza_topping_id =2

Please see the demo : demo

Upvotes: 3

Mihai Stancu
Mihai Stancu

Reputation: 16107

RegEx is slow on very big data sets.

MySQL has a FIND_IN_SET(value, column).

Upvotes: 2

Related Questions