SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
-
Jul 13, 2002, 20:42 #1
- 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 | +---+---------------------+
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
-
Jul 14, 2002, 00:26 #2
- 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);
Code:SELECT user FROM your_table WHERE your_table.id=name_table.id AND name_table.name LIKE '%$name%';
-
Jul 14, 2002, 07:08 #3
- 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
-
Jul 14, 2002, 18:28 #4
- 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 `,':
-
Jul 15, 2002, 00:45 #5
- 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
-
Jul 15, 2002, 01:57 #6
- 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.
-
Jul 15, 2002, 04:48 #7
- 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