asked on

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

Rimvis

Hi movieprodw,

You can try this approach:

MySQL String Splitter
http://www.tero.co.uk/scripts/mysql.php

movieprodw

ASKER

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

Rimvis

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?

movieprodw

ASKER

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?

ASKER CERTIFIED SOLUTION

Rimvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

movieprodw

ASKER

Thanks for your help