SQL query to retrieve values ​​from a comma separated column

Question

Possible duplicate:
Split a string in SQL
Search for a comma separated column

In my PHP project, I have a problem writing SQL:

I have a “ consultants ” table with category fields that has values ​​such as:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + id | consultant_name | categories + +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + 1 | AOAOAO | health,insurance,programming, + + 2 | BOBOBO | health,gaming,windows,mobile, + + 3 | CCCCCC | insurance,windows, + + 4 | DDDDDD | mobile, + + . | ...... | ............ + + . | ...... | ............ + + . | ...... | ............ + +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 

And I have an array that contains all the categories with which a consultant can register.

 $arrayOfCategories = $array("health","insurance","programming", "sports","gaming","windows","apple", "mobile","astrology"); 

What I want is an SQL query that should give me output, for example:

 +++++++++++++++++++++++++++++++ + category | occurrence + +++++++++++++++++++++++++++++++ + health | 2 + + insurance | 2 + + programming | 1 + + sports | 0 + + gaming | 1 + + windows | 2 + + apple | 0 + + mobile | 2 + + astrology | 0 + +++++++++++++++++++++++++++++++ 

Any help would be appreciated ...

Thanks in advance...

Answer 1

Poor database design, but interesting question, here is my way to do it (php code):

 $arrayOfCategories = $array("health","insurance","programming", "sports","gaming","windows","apple", "mobile","astrology"); $count = count($arrayOfCategories); $query = ""; for($i = 0; $i < $count; $i++) { $value = $arrayOfCategories[$i]; $query += "SELECT '$value' AS category, COUNT(*) AS occurrence FROM consultants WHERE FIND_IN_SET('$value', categories)"; if($i < $count -1) { $query += " UNION " } } 

The result of the generated query should give you exactly what you want. Often from poor design interesting questions arise ... :)

Answer 2

This is a bad database design. If I was stuck with this, I would not use SQL to get the entries, but my application.

Create a new array called $ eventence, this will be the index of each word and score. Select each line and separate the contents of the categories with a comma. Number this resulting array and increment for each word.

I'm a little rusty in PHP, so here is my suggestion in Perl:

 my %occurrence; while ( my ($categories) = $sth->fetchrow_array() ){ my @cats = split(',', $categories); foreach my $c (@cats){ $occurrence{$c}++; } } 

One of the advantages of this method is that you will find any misused categories and should not update your SQL when adding a new category.

Answer 3

If you have a table with categories, you can do it as follows:

 SELECT cat.name, COUNT(*) FROM categories cat JOIN consultants con ON con.categories LIKE '%'+cat.name+'%' 

However, I find this inconvenient, and you should consider normalizing the db scheme so that there is a mapping table for the relationship category of consultants