SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Having to 'explode' a field in mysql query and compare each with a variable

    Hi,

    This might be a little to complex to explain what I'm trying to get at. Here goes.

    I have a field in my sql table that contains a list of names separated by a 'comma' (,).
    The number of names for the field in each row varies, depending on the user.

    so a table in that form would be like this:
    Code:
     ID   NAME
    +---+---------------------+
    | 1 | name1,name2,name3   |
    +---+---------------------+
    | 2 | name6,name7         |
    +---+---------------------+
    | 3 | name1,name7         |
    +---+---------------------+
    I'm trying to work out a sql query that would list users whose field contains 'name1' for example.

    How would I go about that?

    I've thought about retrieving every row in the table and using the php function 'explode' to retrieve the individual names and determine whether to show or not. But that would be rather intensive for the server.

    Is there a function that can do the above in a mysql query and return only the required results?

    Thank you for your help. Let me know if you require more clarification on this issue. thanks
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you could try the MySQL FIND_IN_SET() function but no gurantees:
    Code:
    SELECT user FROM your_table WHERE your_table.id=name_table.id AND FIND_IN_SET('$name', name_table.name);
    or you can simply use this... but that would match both 'Ray' and 'Raymond' if you're looking for 'Ray' only:
    Code:
    SELECT user FROM your_table WHERE your_table.id=name_table.id AND name_table.name LIKE '%$name%';

  3. #3
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Unfortunately, setting the field type as 'SET' would not work.

    Is there a way that I can use explode(',', col_name) in the mysql query?

    Thanks for your help though.
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    from what i understand from the MySQL manual, FIND_IN_SET() doesn't require the column to be a SET... it only has to be a string list, which (as detailed below) is a string composed of comma separated values... but like i said i won't know if it'd work cos i haven't tried it... you should though...

    from the MySQL documentation
    FIND_IN_SET(str,strlist)
    Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimised to use bit arithmetic! Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a `,':
    i don't know if MySQL has an explode() or similar function

  5. #5
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I did give it a try. Even setting the colum to set which worked. but without it, it just produces some error.

    Thanks for your help. appreciated it.
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  6. #6
    FreeBSD The Power to Serve silver trophy pippo's Avatar
    Join Date
    Jul 2001
    Location
    Italy
    Posts
    4,514
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I'm going outside the scope of this thread,
    but could not be better to design your table in another way?

    Example:
    Code:
    TABLE name
    id
    the_name
    
    TABLE user
    id
    etc
    etc
    
    TABLE user_name
    user_id ( primary key )
    name_id ( primary key )


    *** edited ***
    The query could be something like:

    PHP Code:
    $query "SELECT * FROM name, user, user_name WHERE name.the_name = '$search_name' AND user_name.name_id = name.id AND user.id = user_name.name_id"
    Last edited by pippo; Jul 15, 2002 at 02:06.
    Mr Andrea
    Former Hosting Team Advisor
    Former Advisor of '03

  7. #7
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Thanks! Actually, I've already implemented the JOIN statement.
    Thanks again!
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein


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
  •