Query Tricky Distict

Hello,

I am using the below query to find all of the trails someone has traveled.

I have the trails stored like:

id|trail
1|trail1
2|trail2
3|trail1, trail6, trail2
4|trail3
5|trail2, trail5

The problem is that they were setup as one per field but now we have a multiselect so they have have more then one in a field and they are separated by ', '.

$result3 = mysql_query("SELECT DISTINCT trail FROM cms_activities WHERE user_id = '".$_COOKIE['member_id']."'") 
or die(mysql_error());  

$trails = mysql_num_rows($result3);

Open in new window

LVL 1
movieprodwAsked:
Who is Participating?
 
RimvisCommented:
Does that mean you can't create any database objects, like tables and functions?

Of course, you can do it at PHP side, with explode function for example:
http://php.net/manual/en/function.explode.php

But I'm not a PHP expert, I wouldn't be able to help you further with that.
0
 
RimvisCommented:
Hi  movieprodw,

You can try this approach:

MySQL String Splitter
http://www.tero.co.uk/scripts/mysql.php
0
 
movieprodwAuthor Commented:
I am sorry but I still can not figure this out, I have tried the method you sent but I can not find the common ground using just one table.

Would love some help
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
RimvisCommented:
There are approaches that are using cursors to process the string. This one for example:
http://stackoverflow.com/questions/3908966/mysql-procedure-to-load-data-from-staging-table-to-other-tables-need-to-split-u/3909888#3909888

But I would stick to the original solution. You could create a table with sequential numbers and use it in your SELECTs. Is there something that prevents you from doing it?
0
 
movieprodwAuthor Commented:
Well all the data and code is there and I only have access to the page that retrieves the code.

I wonder if there is a way to pull the rows, break them up and store them in an array then count them?
0
 
movieprodwAuthor Commented:
Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.