PHP MySQL Counting and sorting values

Question

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

ain · Accepted Answer · 2011-09-20 23:30:06Z

3

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

edited Sep 20, 2011 at 23:30

answered Sep 20, 2011 at 23:16

ain

22.8k3 gold badges60 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mkrisch76 Over a year ago

So at that point I would have $query = "SELECT t.image, COUNT(t.image) FROM ( SELECT painting1 as image FROM Art2011 UNION SELECT painting2 as image FROM Art2011 UNION SELECT painting3 as image FROM Art2011 ) AS t GROUP BY t.image"; $result = mysql_query($query) or die(mysql_error()); Then do I need to run a loop to display my answer? I'm new to php and from what I've read, I'm guessing I would use a for loop?

srivani Over a year ago

@ain, the union should be UNION ALL. Otherwise the duplicate rows will be removed and the counts will not be accurate.

ain Over a year ago

Yes, this query would return you a row per imageX (as you listed what would be the desired result in your post) and in PHP you'd fetch the rows until there is no more data.

Trevor · Accepted Answer · 2011-09-20 23:13:01Z

1

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'

answered Sep 20, 2011 at 23:13

Trevor

6,6995 gold badges40 silver badges69 bronze badges

1 Comment

mkrisch76 Over a year ago

So at that point I would have $query = "select count(1) from Art2011 where painting1 = 'image1' or painting2 = 'image1' or painting3 = 'image1'"; $result = mysql_query($query) or die(mysql_error()); Then do I need to run a loop to display my answer? I'm new to php and from what I've read, I'm guessing I would use a for loop?

srivani · Accepted Answer · 2011-09-20 23:31:08Z

1

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";
}

answered Sep 20, 2011 at 23:31

srivani

1,0226 silver badges11 bronze badges

2 Comments

mkrisch76 Over a year ago

Can I fit an ORDER BY in here? I have a total of 106 images and the results so far look like image1 = 15 image10 = 10 image100 = 5 image2 = 21 image20 = 36 image21 = 38 image22 = 109 image23 = 14 image24 = 58 I would just like to display them in order according to title#.

jswolf19 Over a year ago

@user955807, you'll probably want to append something like ORDER BY CAST(SUBSTR(t.image, 6) AS SIGNED).

Collectives™ on Stack Overflow

PHP MySQL Counting and sorting values

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related