I have three tables in mySQL - category, topics, and comments. I'm trying to count the total comments to a topic and group each country code by the total count. At the moment there is several country codes that appear multiple times in comments_location in the comments table which contains the different country codes (e.g. US,UK,CH,ETC). However, these country codes can appear multiple times in comments_location. I'd like to group the comments_location (e.g. the comments_location has 3 UK records, 2 US records, etc - rather than appearing three times, I'd like to group the comments_location (by a single UK,US,ETC) and show the total number of records for each country). Below is an example of an attempt but it's not working:
// should there be a FOR statement and where should it be placed?
//query comments where comment location = (US,UK,ETC.)
$sqlcomment = mysql_query("SELECT * FROM comments WHERE comments_location='NEED TO RUN THROUGH EACH COUNTRY CODE AND GROUP e.g. (GROUP US - run count, then GROUP UK - run count, ETC)'");
//get comment total (run count)
$commentnumber = mysql_num_rows($sqlcomment);
//echo each variable into a string one by one (should the FOR be placed here?)
$variable1 = "CODE: NEED VARIABLE FOR COMMENTS_LOCATION, value: $commentnumber, ";
echo $variable1;
Ideally, the echo $variable1 should show CODE: US, value: 2240, CODE: UK, value: 6240, CODE: CH, value: 3240, etc continue looping until all the possible country codes have been listed.
Do I need to store an array of all the possible country codes and place them WHERE comments_location is in $sqlcomment?
Please try the following query:
$sqlcomment = mysql_query("SELECT comments_location,count(comments_id)as count FROM comments GROUP BY comments_location");
echo'<pre>';
while($row=mysql_fetch_array($sqlcomment)){
print_r($row);
}
echo'</pre>';
comments_id was a guess, adapt that when required.
This should give you n results, while n=Count of the Countries. You will have per row then the Country + the Count.