Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

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

0
movieprodw
Asked:
movieprodw
  • 3
  • 3
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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
 
movieprodwAuthor Commented:
Thanks for your help
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now