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

[lynlimz]

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:

 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

[redemption]

you could try the MySQL FIND_IN_SET() function but no gurantees:

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:

SELECT user FROM your_table WHERE your_table.id=name_table.id AND name_table.name LIKE '%$name%';

[lynlimz]

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.

[redemption]

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

[lynlimz]

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.

[pippo]

Hi,

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

Example:

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:

$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"; 

[lynlimz]

Hey,

Thanks! Actually, I've already implemented the JOIN statement.
Thanks again!