While Looping Issue

[chasethemetal]

I have a database table named count. Inside lives some fields for example, date, song, playcount.I've managed using SUM() to get the total playcount based by date. Nice, so I have a way to look at daily TOTAL play counts. How it works is every song has a date and the playcount is an integer that gets updated every time someone plays a song, so I sum() playcount based on the date. Anyways back to the issue. For instance I have 2 songs, so the fields look like thistablename:countsong playcount datesong1 12 07/19/11song2 25 07/19/11But what is stumping me is trying to get a break down based by song and day. The trickory behind it is it needs to be looped/echo'd out in this manor.['$date' , $song1count, $song2count]I just cant think of a way to sort by date and have it loop the different songcounts NEXT to each other... Everything I've attempted will result in this...['07/19/11', 12]['07/19/11', 25]When it needs to spit it out like...['07/19/11', 12, 25]Any pointers/ideas would be much appreciated!

[niche]

is song playcount date one column or three?

[chasethemetal]

Sorry that didnt look good. 3 columns.

[justsomeguy]

Loop through the results and build an array for output with the fields you want, then loop through that array to print it. It could be an array where the keys are dates, and each element is an array of the song counts for that day.

[chasethemetal]

Thanks. I get what your saying but I'm having troubles thinking it through... Here is my code...It prints like so. It's obviously wrong because I'm just calling the SUM(count) twice.. I'm just a little lost here getting better every day though!['07/19/2011' , 12, 12]; ['07/19/2011', 20, 20];but it should be['07/19/2011', 12, 20];

$query = "SELECT date, SUM(count) FROM playcount GROUP BY uid DESC LIMIT 2";		$result = mysql_query($query) or die(mysql_error());				 		while($row = mysql_fetch_array($result)){									$str.="['".$row['date']. "', " . $row['SUM(count)']. ", " . $row['SUM(count)']. "];";					 		}				echo $str;

[justsomeguy]

You're building a string, not an array. Build it in an array so that you can figure out if you've already seen a certain date.

$records = array();while($row = mysql_fetch_array($result)){  if (!isset($records[$row['date']]))	$records[$row['date']] = array(); // create a new sub-array if this is a new date  $records[$row['date']][] = $row['SUM(count)']; // add the count to the array for the date}print_r($records);

That creates an array called $records, where the keys in the array are the dates. Each element in the array is another array that contains the counts from the query. You can loop through that and display your results for each date.

foreach ($records as $date => $counts){  echo 'Records for ' . $date . ':<br>';  for ($i = 0; $i < count($counts); $i++)	echo $counts[$i] . '<br>';}

[chasethemetal]

Thank you! I was able to get the results I was seeking with this method. I have not yet used Arrays much in php. Next thing to read on. Thank you so much.