BLOG DESIGN SOLUTIONS If the year and month
BLOG DESIGN SOLUTIONS If the year and month look correct, the posts are retrieved. These posts are identified by using the MONTH and YEAR MySQL functions to match the postdate to the year and month specified in the query string: if ($myposts) { $showbymonth = true; $text = strtotime(”$month/1/$year”); $thismonth = date(”F Y”, $text); } } If the database query returns a result (that is, it finds one or more posts for that month), the $showbymonth flag is set to true so the page knows it is showing a month archive. Then the strtotime and date functions are combined to convert the numeric month and year into more readable text such as April 2005. The strtotime function takes a string that looks like a date and tries to turn it into a timestamp that can be processed by PHP. The date function takes a timestamp and formats it into text (much like the MySQL date_ format function mentioned earlier in the chapter). if (!isset($showbymonth)) { $showbymonth = false; // Select posts grouped by month and year $sql = “SELECT DATE_FORMAT(postdate, ‘%M %Y’) AS monthyear, . MONTH(postdate) AS month, YEAR(postdate) AS year, count(*) AS count . FROM posts GROUP BY monthyear . ORDER BY year, month”; $result = mysql_query($sql); $myposts = mysql_fetch_array($result); } If the $showbymonth flag has not been set (because no posts were found for the specified month, or if no valid month or year were specified), the flag is set to false and the database is queried for all posts. There are a number of new concepts introduced in this SQL query, so I will step through it bit by bit: SELECT DATE_FORMAT(postdate, ‘%M %Y’) AS monthyear, The postdate for each post is formatted as month and year; for example, April 2005. MONTH(postdate) AS month, YEAR(postdate) AS year, The postdate is also formatted as a month and a year separately. These values will be used to construct a link to the archive. count(*) AS count FROM posts GROUP BY monthyear GROUP BY monthyear is used to combine all the posts that have the same value of monthyear; for example, all the posts made in April 2005. The count(*) function can then be used to output the number of posts that have been grouped together for that value of monthyear; in other words, the number of posts made in April 2005. ORDER BY year, month