BenM BenM - 5 months ago 27
MySQL Question

MySQL to group unique items across multiple columns with COUNTs for each?

firstly I know this is MySQL and I know I should be using MySQLi... so forgive me for that ...

I've been racking my brain all day over this, I am sure it SHOULD be simple. I have a table of products, and columns within the table for each product like colour, material, size, etc

I want to create a sidebar/filter when displaying results, so a user can filter the results to say just show "red" products, or just metal products etc etc.

I have got the mysql/php working to do this dynamically but I cannot for the life of me work out how to use COUNT within this to also show the number of each.

What I want is if you click a category and 10 products are within that category, the statement will create a dynamic filter based on all the various attributes (columns) from those 10 products (only showing unique) as so:

Colour:
Red (2)
Yellow (4)
Pink (1)
Blue (1)
Green (2)

Material:
China (3)
Paper (2)
Metal (5)


Code I have that works but doesn't have the count for each:

$sql=mysql_query("SELECT
(SELECT GROUP_CONCAT(DISTINCT colour) FROM $table_products_description WHERE products_id IN (".$ids.")) as Colour,
(SELECT GROUP_CONCAT(DISTINCT material) FROM $table_products_description WHERE products_id IN (".$ids.")) as Material");

foreach(mysql_fetch_assoc($sql) as $key=>$value)
{
$html.='<h3>'.$key.'</h3>';
$subvals=explode(",",$value);
foreach($subvals as $subval)
{
$html.='<p>'.$subval.'</p>';
}
}


Where $ids is an array of product ids for any given category or search result.

That displays this result which is nice, but has no counts by each:

**Colour**
Red
White
Blue
Green
Yellow
Black
Orange

**Material**
Fine Bone China
Ceramic
Cast Iron
Porcelain


I can get it to work nicely with just 1 column, using totally different statement as below, but how can I expand this to multiple columns?

select group_concat( concat( colour,'(',qty,')') separator ', ') Colour
from (
select colour, count(*) qty
from $table_product_description
group by colour
) s


The above displays the values AND the counts for each but I can't get the above to work across lots of columns no matter what I try.. any ideas?

**Colour**
Red (3)
White (1)
Blue (2)
Green (1)
Yellow (1)
Black (1)
Orange (1)


So to summarize I need to select all unique values from multiple columns within a single table and then output all the resulting values with the total counts for each.

Any help massively appreciated!

Answer

You cannot aggregate (count) two different things in one row. This query...

SELECT 'Colour' AS category, colour AS value, count(*) AS qty
FROM $table_product_description
WHERE products_id IN($ids)
GROUP BY colour
UNION ALL
SELECT 'Material' AS category, material AS value, count(*) as qty
FROM $table_product_description
WHERE products_id IN($ids)
GROUP BY material

...will give you sth like this:

category | value | qty
Colour   | red   | 5
Colour   | blue  | 3
Material | metal | 2
Material | paper | 6

If you want to play with group_concat() you may wrap this query into:

SELECT a.category, group_concat( concat( a.value,'(',a.qty,')') separator ', ') AS list
FROM (
    ...above query...
) AS a
GROUP BY a.category

Data access

mysql_fetch_assoc($sql) returns one row each time it's called or false if there's nothing (more) to fetch. It's usually done with while() loop like this:

while ($row = mysql_fetch_assoc($sql)) {
    //For combined query $row in each iteration becomes:
    // 1: ['category' => 'Colour', 'list' => 'red(5), blue(3)']
    // 2: ['category' => 'Material', 'list' => 'metal(2),...']
}