Advanced Search

Results 1 to 9 of 9

Thread: Select id in multi varchar field

  1. #1
    Join Date
    Feb 2013
    Location
    Chile
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Select id in multi varchar field

    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
    Last edited by huinchaman; 02-10-2013 at 05:06 PM.

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,621
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    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:
    Code:
    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:
    Code:
    -- 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:
    Code:
    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 :)
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  3. #3
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,154
    Thanks
    260
    Thanked 690 Times in 678 Posts

    Default

    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.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  4. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,621
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by djr33 View Post
    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.
    Code:
    ',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).
    Last edited by traq; 02-10-2013 at 09:11 PM.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  5. #5
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,154
    Thanks
    260
    Thanked 690 Times in 678 Posts

    Default

    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".
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  6. #6
    Join Date
    Feb 2013
    Location
    Chile
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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,%")

  7. #7
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,621
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    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.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  8. #8
    Join Date
    Feb 2013
    Location
    Chile
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

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

  9. #9
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,621
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    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?
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

Similar Threads

  1. Change BG of styled select field
    By elektrobank in forum JavaScript
    Replies: 6
    Last Post: 09-16-2011, 04:43 PM
  2. dynamically select a tab from db field!
    By Honey237 in forum Dynamic Drive scripts help
    Replies: 0
    Last Post: 05-14-2009, 05:42 AM
  3. form validation using a select field
    By jc_gmk in forum Dynamic Drive scripts help
    Replies: 4
    Last Post: 05-20-2008, 03:07 PM
  4. Multi field search recordset
    By bigleo23 in forum PHP
    Replies: 1
    Last Post: 01-23-2008, 04:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •