num_row help, in while loop

[mattm1712]

hi im trying to make an archive of images viva year and months and want to display how many images were posted in each month, i have wrote this script but cant see why it wont echo the number of images posted in each month.

 

<?php

include 'connect.inc';
$imagearc =   mysql_query("SELECT * FROM images GROUP BY year DESC");
      while($ln = mysql_fetch_assoc($imagearc))
      {
         echo $ln[year];
         echo "<br>";
         $year = $ln[year];
         $months=   mysql_query("SELECT * FROM images WHERE year=$year GROUP BY month DESC");
         while($mo = mysql_fetch_assoc($months))
         {
         $month = $mo[month];
         $mon =  mysql_query("SELECT * FROM images WHERE month=$month");
         $numm = mysql_num_rows($mon);
         echo "<a href=''>";
         echo $mo[month];
            
         echo "</a>";
         echo $numm;
         echo "<br>";
      }
            
   }
?>

 

can anyone help?

 

cheers matt

 

MOD EDIT: code tags added.

[PFMaBiSmAd]

So, do you actually have the `year` and `month` stored in separate columns or do you have the DATE (YYYY-MM-DD) stored in a column with a DATE data type?

 

Also, you may have seen this suggestion posted in the forum, but it is almost never a good design to perform queries inside of loops. You can perform one query using a SELECT COUNT(*) term and a GROUP BY year, month term to get mysql to do everything you need.

[Pikachu2000]

When posting code, enclose it within the forum's

 . . . 

BBCode tags.

[PFMaBiSmAd]

Based on the output your code was producing, here's how you can use one query -

<?php

include 'connect.inc.php'; // use a .php file so that someone cannot get your database connection information
$query = "SELECT year,month,COUNT(*) as cnt FROM images GROUP BY year DESC, month DESC";
if(!$result = mysql_query($query)){
// query failed, handle the error here...
die(mysql_error());
} else {
// query executed without any errors
if(!mysql_num_rows($result)){
	// query returned zero rows
	echo "There are no matching rows in the database!";
} else {
	// query returned row(s), process the data here...
	$last_heading = null; // remember the heading, initialize to a value that will never exist in the data
	while($row = mysql_fetch_assoc($result)){
		// detect a change in the heading
		if($last_heading != $row['year']){
			$last_heading = $row['year']; // remember the new heading
			// heading changed, output the new heading here...
			echo "{$row['year']}<br />";
		}
		// output each piece of data
		echo "<a href=''>{$row['month']}</a>{$row['cnt']}<br />";
	}
}
}
?>

[mattm1712]

thank you guys sorted now with your help, cheers matt