Select id in multi varchar field

[huinchaman]

Hi need help,
table category
id int
description varchar

table products
id int
id_cat varchar
etc..

id | id_cat
1 | 1, 2, 3
2 | 3,4,6
3 | 1, 5 , 7
.
.
.

I need something like "Select * from produts where id_cat in ( 1 )",but does not work

[traq]

Nope, won't work.

Storing multiple values (a list of numbers, in this case) in a single column makes it impossible (or at least awkward/inefficient/unreliable) for MySQL to use those values. Whenever you're tempted to do so, you should think twice. (You might find this thread relevant.)

To work with your current DB design, you'd have to use LIKE:

SELECT `whatever` FROM `products` WHERE `id_cat` LIKE "%1%"

However, you should see how fragile this query is (it might return false positives, or not match rows it should), especially considering that the lists are separated by a comma, but start and end with bare numbers, and may also have whitespace in the mix. It is also very inefficient, since the categories cannot be indexed (MySQL has to read the entire table for every query).

Instead, you should create a separate table to store the relationships between your products and their categories:

-- your product table:
CREATE TABLE `product`(
    `id` INT NOT NULL
    -- the product id number.
    -- might be AUTO_INCREMENT, or however you choose it.
    -- . . . your other cols . . .
   ,PRIMARY KEY(`id`)
)ENGINE=InnoDB;
-- The default MyISAM engine does not support the foreign keys we'll be using later.

-- this table will define possible categories for your products:
CREATE TABLE `category`(
    `cat` INT NOT NULL
    -- an id for the category - again, AUTO_INCREMENT or however you want to define it
   ,`name` VARCHAR(100) NOT NULL
    -- category name
   ,PRIMARY KEY(`cat`)
   ,UNIQUE KEY(`name`)
    -- category names must be unique
)ENGINE=InnoDB;

-- now, a table to define relationships between the two:
CREATE TABLE `product_category`(
    `id` INT NOT NULL 
    -- this matches the id of the product
   ,`cat` INT NOT NULL 
    -- this is the category number the product belongs to
   ,PRIMARY KEY(`id`,`cat`)
    -- the id + cat combination must be unique (no duplicate relationships!)
   ,FOREIGN KEY(`id`) REFERENCES `product`(`id`)
    -- this means that every id here must match an id in the product table
    ON DELETE CASCADE 
    -- this means that when a product id is deleted, MySQL will delete any records here that used it
    ON UPDATE CASCADE 
    -- this means that when a product id is changed, MySQL will update any records here that used it
   ,FOREIGN KEY(`cat`) REFERENCES `category`(`cat`)
    -- likewise, every category number must have a matching record in the category table
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

This way, MySQL will manage the product->category relationships for you, and it is easy + reliable to select the one(s) you want:

SELECT `p`.`whatever` 
   FROM `product` `p`,`product_category` `c`
   WHERE `p`.`id` = `c`.`id`
   AND `c`.`cat` IN( 1 );
   -- or "AND `c`.`cat` IN( 1,5,7 )", or whatever categories you want :)

[djr33]

I agree completely with traq. However, if you really really want to do it this way (I'd call it the "wrong way"), then it is possible, with one change--
You need to add a final comma to the end of the field and one to the beginning as well. This way every number has a comma after it and one before it. Now you can unambiguously identify all numbers in this way:
LIKE '%,1,%'
You know that there are no other digits after the 1 because there are commas surrounding it. You'll get no false positives and no false negatives either.
That will work. But as traq has suggested, there are much better ways to do this.

[traq]

You need to add a final comma to the end of the field and one to the beginning as well. This way every number has a comma after it and one before it. Now you can unambiguously identify all numbers in this way:
LIKE '%,1,%'
You know that there are no other digits after the 1 because there are commas surrounding it. You'll get no false positives and no false negatives either.
That will work.

Thanks Daniel - that's a good explanation of what I was getting at: the list format would have to be very strictly standardized (in a way that the given example is not). For example, the extra whitespace in some of the example rows would defeat this query.

',1,2,3,' LIKE '%,1,%' -- matches
',1 , 2 , 3,' LIKE '%,1,%'  -- fails

I don't know how many existing records you have, but if it's more than just a few, it quickly becomes a chore to "proofread" and correct them all (not to mention that you've now taken on the ongoing task of making sure all new records match the proper format).

[djr33]

Yes, that's all correct. And I missed the white-space issue in the originally posted data. But it's still solvable (you could do a replace operation removing all spaces while searching, for example), but it would be inefficient.
My point: it's possible to search systematically for a value within a field, rather than just for the value of the whole field.
Your point (and also one I agree with): it's best to avoid all of this and have one value per field, no exceptions. If not, you will either get bad results (as we've discussed), or it will be very inefficient. One of the great parts of MySQL is that it's so fast. Work with it, rather than against it, to keep the searching fast.

But... if you really want to keep something like the current format, you can, and depending on what you're doing it might not be all that bad.


And by the way, the problems of making old records conform to the new format should be easy enough with a PHP script designed to replace all of the old badly formatted values-- add the new commas, remove all white-space, etc.




From a database theory point of view, you're currently doing it the wrong way. You think you're "saving space" or keeping it "simpler", but the whole purpose of databases is to lay out the information as broadly as possible, with one value per field, and nothing more. It's important that you understand this.

From a practical point of view, it may be fine to do that, as long as you fix a few things to keep the format consistent. It's your choice.




One reason to choose the "proper way" is that it will make other operations much easier. At the moment searching for matches can be done easily enough. But if you want to start doing things like replacing information within a field, that's much harder. And some operations just won't work: for example, sorting.
So if you only ever need to worry about these values independently when searching, doing it this way won't be a big problem. But to make sure you're not limiting yourself for later, you may want to consider putting in the extra work (actually, in some ways it's less work) to do it the "proper way".

[huinchaman]

Thanks all, but I can not change the structure of the database,

the only thing you can do is :
$id is variable from PHP

SELECT DISTINCT id, description from products
where (id_cat = "$id" or id_cat like "%,$id,%" or id_cat like "$id,%")

[traq]

Understood. You can use LIKE - it'll "work," if you're careful. It's just not the best way to do it; and it may also require editing some of the existing records, if they don't comply with the necessary format.

[huinchaman]

understand. but my difficulty is that I can not modify tables bdd.
Regards

[traq]

In your example, some of the records had whitespace between the numbers and the commas, and some didn't. Is this the case in the actual records?

If so, can you edit the records?