i'm going round in circles - basically i'm trying to create a list of blog posts by month
ie for 2010
Jan (45)
Feb (33)
Mar (35)
etc & then clicking on the month would produce a display of that months posts
i can do the select of posts by month & display them no problem -
i can do a select by month no problem but then i've got to have 12 sets of seperate queries on the same page
i managed to get this sql query working in phpmyadmin but i cant work out how to actually display the results on the page as above so that i can then add in a link to send a get statement to get that months results:
SELECT DATE_FORMAT(MONTH(post_date),'%m/%d/%y' ) AS unixdate , COUNT(post_date) AS counted FROM wp_posts WHERE post_status='publish' AND YEAR(post_date)=2010 GROUP BY MONTH(post_date)
which i would then add in something like a href=month.php?name=$month&year=$year
this is the query on the php page
<?php
include 'config_blog.php';
include 'opendb.php';
$result = mysql_query("SELECT DATE_FORMAT(MONTH(post_date),'%m/%d/%y' ) AS unixdate , COUNT(post_date) AS counted FROM wp_posts WHERE post_status='publish' AND YEAR(post_date)=2010 GROUP BY MONTH(post_date)", $connection) or die("error querying database");
$i = 0;
while($result_ar = mysql_fetch_assoc($result)){
?>
and then ideally something like:
<href=month.php?name=<?php echo $result_ar['MONTH']; ?>&year=<?php echo $result_ar['YEAR']; ?>
<?php
$i+=1;
}
?>
if this makes sense