Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    7

    Unanswered: FIND_IN_SET and IN not working for my situation

    Hello,

    I'm having a hard time trying to fetch results based on a comma seperated value.

    I have a table called preferences.
    It contains:

    uid | cats
    ----------
    1 | 1,2,3,5
    2 | 3,7,2,4
    3 | 7,3,6,1
    4 | 3,1,7,9

    Now, what I'm trying to do is select results from this table based on categories chosen from the search page.

    If I am searching categories 2, 3 and 9 I would like to make sure these ids exist within the 'cats' field. What I expect to receive from the example above is:

    uid
    ---
    1
    2
    4

    The query I'm using looks like:

    ...
    WHERE FIND_IN_SET(p.cats, '2,3,9')

    and I've tried:
    WHERE p.cats IN (2,3,9)

    and I've also tried:
    WHERE p.cats IN ('2','3','9')

    and I've also tried:
    WHERE p.cats IN ('2', '3', '9')

    But they just don't seem to be working properly.
    Any help would be appreciated.

    Thanks in advance

  2. #2
    Join Date
    Feb 2005
    Posts
    7
    Okay, with help from my programmer, we are trying to make a new custom function in MySQL. It's not working. Here is the function:

    Code:
    create function FIND_SET_IN_SET(IN input TEXT, IN STRLIST TEXT) RETURNS INT
    BEGIN
    
    Declare delimiter VARCHAR(10) default ',';
    DECLARE cur_position INT DEFAULT 1 ;
    DECLARE remainder TEXT;
    DECLARE cur_string VARCHAR(1000);
    DECLARE delimiter_length TINYINT UNSIGNED;
    DECLARE cur1 CURSOR FOR select value from SplitValues;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    DECLARE val varchar(1000);
    DECLARE retval INT default 0;
    
    
    DROP TEMPORARY TABLE IF EXISTS SplitValues;
    CREATE TEMPORARY TABLE SplitValues (
    value VARCHAR(1000) NOT NULL PRIMARY KEY
    ) ENGINE=MEMORY;
    SET remainder = input;
    SET delimiter_length = CHAR_LENGTH(delimiter);
    WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
    SET cur_position = INSTR(remainder, delimiter);
    IF cur_position = 0 THEN
    SET cur_string = remainder;
    ELSE
    SET cur_string = LEFT(remainder, cur_position - 1);
    END IF;
    IF TRIM(cur_string) != '' THEN
    INSERT INTO SplitValues VALUES (cur_string);
    END IF;
    SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);
    END WHILE;
    
    OPEN cur1;
    REPEAT
        FETCH cur1 INTO val;
        set retval = FIND_IN_SET(val, STRLIST);
        IF retval != 0 THEN
            set done = 1;
        END IF;
    UNTIL done END REPEAT;
    CLOSE cur1;
    
    DROP TEMPORARY TABLE IF EXISTS SplitValues;
    
    return retval;
    END;
    But the error we receive (both of us) is this:

    "MySQL said:

    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(IN input TEXT, IN STRLIST TEXT) RETURNS INT
    BEGIN"

    Please help! Thanks so much.

  3. #3
    Join Date
    Mar 2004
    Posts
    480
    why wouldn't you just normalize your data?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by psalzmann
    If I am searching categories 2, 3 and 9 I would like to make sure these ids exist within the 'cats' field. What I expect to receive from the example above is:

    uid
    ---
    1
    2
    4
    what happened with uid 3? it contains cat 3, which is one of the cats you're looking for, correct?

    tip: always double-check your examples to make sure they are consistent

    you and your programmer can save yourselves gobs of time -- and at the same time, ensure that your app will scale properly -- by normalizing the data

    searching for values in a comma-delimited colulmn is possible, but requires a table scan, which means that as your app grows, your queries will get slower and slower and slower...

    probably not a good idea to code this behaviour in at the beginning, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Posts
    7
    Thanks folks.

    I wasn't aware that MySQL couldn't handle n,n,n,n type data. As I'm sure it does, it appears that by fixing my problem above all that is required is to create a seperate table:

    uid | catid
    ----------

    After running some tests, the information returned is exactly what is required.

    Thanks guys. Data normalization is something I'm going to be focusing on a little more the upcoming days. I'm not as keen to MySQL as you guys so I am having a little harder time normalizing my data.

    Once again, I appreciate your time in replying to my topic!

    If I can add rep points, I will

    Regards,
    Peter

Posting Permissions

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