CODEWITHSUNDEEP
phpmysql
Zero
Zero

Reputation: 76917

Unique value count of comma separated field (PHP - MySQL)

I have mysql table that looks like this:

id  place   interest
1   place1  a,b,c
2   place2  c,d,e
3   place1  a,e
4   place2  f
5   place2  f
6   place3  g,h

I need to get unique "place" and "interest" values sorted as per the count. So, the output for "place" would be

place2(3)
place1(2)
place3(1)

So, the output for "interest" would be

a(2)
c(2)
e(2)
f(2)
b(1)
d(1)
g(1)
h(1)

is there a way to do this in PHP-Mysql?

So, far I have been able to get simple column data

SELECT place, 
COUNT( * ) AS num 
FROM testtab 
GROUP BY place 
ORDER BY COUNT( * ) DESC

Upvotes: 0

Views: 1151

Answers (4)

jospratik
jospratik

Reputation: 1674

can do this :

$inst_row = '';
foreach($rows as $row){
   $inst_row .= $row['interests'];
}

$inst_values = explode(',', $inst_row);
$inst_count = array_count_values($inst_values);

// $inst_count will return you count as you want ,print_r it and format it accordingly

Upvotes: 0

Nick
Nick

Reputation: 4212

This is for the first result you need 

SELECT place,COUNT(interest)
FROM `testtab`
GROUP by place
ORDER BY COUNT(interest) desc

Upvotes: 0

Orangepill
Orangepill

Reputation: 24645

You can do this.

$place = array();
$interests = array();
foreach($rows as $row){
    if (!isset($place[$row["place"]])){
       $place[$row["place"]] = 0;
    }
    $place[$row["place"]]++;
    $ints = explode(",", $row["interests"]);
    foreach($ints as $int){
        if (!isset($interests[$int])){
             $interests[$int] = 0;
        }
        $interests[$int]++;
    }
}

This will give you the two arrays keyed off of the relevant field with the value being the count. If this is going to be a common action in your application it would make more sense to normalize your data as suggested by AliBZ.

Upvotes: 0

AliBZ
AliBZ

Reputation: 4099

As mysql is not able to hold arrays, its better to build a new table like this:

interest_id interest_name 
1           a
2           b

and another one to keep the relations:

pk id   interest_id
1  1    1
2  1    2

which this id is the id of the records in your main table.

With having this, you can easily use:

select count(*) from THIRD_TABLE where id = YOUR_ID

Upvotes: 1

Related Questions