Create hierarchical view widget for WordPress posts

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>

A quick demo can be seen at here