Recently i got a chance to create a custom widget to display WordPress posts in hierarchical way, first By Year, then by Months and at last by Date.
So the widget displays following output:
I used custom mysql queries to get the data in the order i want. I created a custom table named “months” to keep the month ids and the months names.
<?php $mainMonthQry = "SELECT distinct YEAR(post_date) AS 'year', mon.month_name AS 'month', MONTH(post_date) AS 'monthid' FROM wp_posts post inner join months mon on mon.month_id = MONTH(post_date) where post_type = 'post' and post_status = 'publish' order by post_date desc"; $sth = $db->prepare($mainMonthQry); $sth->execute(); $resultMonths = $sth->fetchAll(); $oldYear = ""; $isFirstMonth = 0; ?>
So, after getting the results its time to create an unordered list hierarchical structure.
<ul> <?php foreach($resultMonths as $month) { $isFirstMonth = $isFirstMonth + 1; if($oldYear == "") { $oldYear = $month['year']; ?> <li class="year"> <a href="http://kamranrafi.com/<?php echo $month['year']; ?>/" class="collapsible withicons margin0"><?php echo $month['year']; ?></a> <ul class="months"> <?php } if($oldYear != $month['year']) { $oldYear = $month['year']; $isFirstMonth = 1; //this is added so first month of each year should be expanded ?> </ul> </li> <li class="year"> <a href="<?php echo 'http://kamranrafi.com/'.$month['year'].'/'.$month['monthid']; ?>/" class="collapsible withicons margin0"><?php echo $month['year']; ?></a> <ul class="months" style=""> <?php } ?> <li> <a href="<?php echo 'http://kamranrafi.com/'.$month['year'].'/'.$month['monthid']; ?>/" class="collapsible withicons margin0<?php echo ($isFirstMonth != 1 ? "" : ""); ?>"><? echo $month['month']." ".$month['year']; ?></a> <ul style="<?php echo ($isFirstMonth != 1 ? "" : ""); ?>"> <?php $qryMonthPosts = "SELECT ID, post_date FROM wp_posts post where post_type = 'post' and post_status = 'publish' and MONTH(post_date) = ".$month['monthid']." and YEAR(post_date) = ".$month['year']." order by post_date desc"; $sth = $db->prepare($qryMonthPosts); $sth->execute(); $resultMonthPosts = $sth->fetchAll(); foreach($resultMonthPosts as $monthPost) { $date = strtotime($monthPost['post_date']); $dateDisply = date("m/d/y", $date); ?> <li> <label> <?php echo $dateDisply; ?> </label> <a href="http://kamranrafi.com/?p=<?php echo $monthPost['ID']; ?>">View</a> </li> <?php } ?> </ul> </li> <?php } ?> </ul>