CODEWITHSUNDEEP
phpmysqlcount
mkrisch76
mkrisch76

Reputation: 63

PHP MySQL Counting and sorting values

I'm new here. In case I'm breaking some kind of protocol by asking the question in the wrong area or overlooking something in a previous search I apologize.

I have a table titled Art2011.

I am collecting data in three columns and would like to combine and count like values.

painting1      painting2       painting3
-----------------------------------------
image6         image4          image3
image4         image1          image4
image8         image1          image3
image2         image9          image6
image6         image4          image3
image4         image1          image4
image8         image1          image3
image2         image9          image6

How would I query, count and display the results in php to look like this?

image1 = 4
image2 = 2
image3 = 4
image4 = 5
etc...

Thank you in advance! Michael

Upvotes: 1

Views: 473

Answers (3)

srivani
srivani

Reputation: 1022

Adding to @ain's post :

$sql = "SELECT t.image as image, COUNT(t.image) as cnt FROM ( SELECT painting1 as image FROM Art2011 UNION ALL SELECT painting2 as image FROM Art2011 UNION ALL SELECT painting3 as image FROM Art2011 ) AS t GROUP BY t.image"; 
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
    echo $row['image']." = ".$row['cnt']."\n";
}

Upvotes: 1

ain
ain

Reputation: 22749

Try

SELECT t.image, COUNT(t.image)
FROM (
   SELECT painting1 as image FROM Art2011
  UNION ALL
   SELECT painting2 as image FROM Art2011
  UNION ALL
   SELECT painting3 as image FROM Art2011
) AS t
GROUP BY t.image

Upvotes: 3

Trevor
Trevor

Reputation: 6689

For each image, you can use this query to get the count:

select count(1) from Art2011 where painting1 = 'image1' or painting2 = 'image1' or painting3 = 'image1'

Upvotes: 1

Related Questions