[RESOLVED] Monthly Archive listing the months entries

middleman666

hey people,

I'm trying to create an archive that first gives the month name and year as a header and then list out the entries made in that month and year and then repeat the process for the rest of the rows in the database.

This is my code so far:


<?php 
include('DB-connect.php');
$TableName = 'Name';
$today = date('Y-m-d');
?>


  <table border="0" align="center" cellpadding="0" cellspacing="0">
	<tr>


<?php
// Build SQL Query  

$query = "select *, DATE_FORMAT(date,'%M %y') as monthyear from $TableName 
where date <= \"%$today%\" group by monthyear order by date DESC";

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results
if ($numrows == 0) {
 echo 'There are currently no entries.';
}

// get results
$result = mysql_query($query) or die("Couldn't execute query");
$row= mysql_fetch_array($result);



//start count
$i = 1;

// get results
$result = mysql_query($query) or die("Couldn't execute query");

// now display the results returned
while ($row= mysql_fetch_array($result)) {

echo '<td valign="top">
      <b>'.$row['monthyear'].'</b>';

  echo '<br><b>'.$i.'.</b> <a href="index.php?menu=home&id='.$row['id'].'" target="_parent"><span class="archiveArtistName">'.$row['artist'].'</span> - 
          <span class="archiveTrackName">'.$row['track'].'</span></a>
	    </td>';

  $i++;

}
?>

</tr>
</table>

That only reads out the first entry from each month, is there a way to modify it to read out all the entries from a particular month?

Any help is welcome,
Thanks.

middleman666

I updated my while function to this:

// now display the results returned
while ($row= mysql_fetch_array($result)) {

// Workout Month Duration  

$thismonth = substr($row['date'],0,7);
$id_low = $thismonth.'-01'; 
$id_high = $thismonth.'-31'; 

echo '<td valign="top">
      <b>'.$row['monthyear'].'</b>';

  // Build SQL Query to list entries of month  

  $query = "select *, DATE_FORMAT(date,'%M %y') as monthyear from $TableName 
  where date >= \"%$id_low%\" AND date <= \"%$id_high%\" order by date DESC";
  // get results
  $result = mysql_query($query) or die("Couldn't execute query");
  // now display the results returned
  while ($row= mysql_fetch_array($result)) {

echo '<br><b>'.$i.'.</b> <a href="index.php?menu=home&id='.$row['id'].'" target="_parent">
      <span class="archiveArtistName">'.$row['artist'].'</span> - 
      <span class="archiveTrackName">'.$row['track'].'</span></a>
     ';

$i++;
  }

}
?>
</td>

Now it reads out the month and year and lists the entries of that month and year, but it does not repeat to do the other months.

Does anyone know what I should add to make it repeat untill all the months have been read out?

thanks.

bradgrafelman

You're overwriting the initial $row MySQL resource with the second query that retrieves the event for that month. You need to use different variable names if you expect the first resource to stay intact throughout the loop.

Actually, for something like this, you shouldn't even need a second query (nor use one - why waste resources when you don't need to, right?). I s'pose if I wasn't so exhausted tonight, I'd try to whip up a sample query for you, but seeing as how I am, you might want to pay the folks in the Database forum a visit and ask how to combine those two queryies (and, subsequently, two different loops) into one.

middleman666

hey bradgrafelman,

thanks for your reply, it now works, with the acception of the $i not stopping to count, but I shall fix that in a min.

I'll take your advice and head over to the database forum and see if they can help simplify the method I'm using.

Thanks for your help.