current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question

3 Answers 3

up vote 2 down vote

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
share|improve this answer
    
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? –  mkrisch76 Sep 20 '11 at 23:20
    
@ain, the union should be UNION ALL. Otherwise the duplicate rows will be removed and the counts will not be accurate. –  srivani Sep 20 '11 at 23:28
    
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. –  ain Sep 20 '11 at 23:29
    
@srivani Thanks, good catch, fixed –  ain Sep 20 '11 at 23:30
up vote 1 down vote

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'

share|improve this answer
    
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? –  mkrisch76 Sep 20 '11 at 23:21
up vote 1 down vote

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";
}
share|improve this answer
    
That's great! THX!!! –  mkrisch76 Sep 20 '11 at 23:37
    
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#. –  mkrisch76 Sep 20 '11 at 23:38
    
@user955807, you'll probably want to append something like ORDER BY CAST(SUBSTR(t.image, 6) AS SIGNED). –  jswolf19 Sep 21 '11 at 3:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.