Tuesday, 22-Dec-15 22:02:00 CET

Porting my blog for the second time, left sidebar part 1

This is post #39 of my series about how I port this blog from Blogengine.NET 2.5 ASPX on a Windows Server 2003 to a Linux Ubuntu server, Apache2, MySQL and PHP. A so called LAMP. The introduction to this project can be found in this blog post /post/Porting-my-blog-for-the-second-time-Project-can-start.

I had a look at the result of the blog so far and I noticed that the left sidebar had some flaws. Last time I worked on the left sidebar was in post #35 of this series about how I develop this blog. I found two issues:

• Sometimes the category header is not displaying, why is that?
• When there are no images to display the sidebar will become empty. That looks ugly.

The category mechanism was designed so that it would display the category every time the row number became one. All other times it would not display the category. But what happens when post #1 don't have any image to display. Then row #1 is not in the list hence no category. I solved this by storing the category in a variable and when there is a category switch then it is displayed in the sidebar. Here is the improved rendering of the left sidebar:

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-left-sidebar-part-1.aspx
$result = $mysqli-≻query($StrLeftSideQuery) or die("Error query.." . mysqli_error($mysqli));
$strSideBar = "";
$strPreviousCategoryName = "";
while ($row = mysqli_fetch_array($result))
{
	if (preg_match("/d{4}/", $row["c1Name"]))
	{
		continue;
	}
	
	if ($strPreviousCategoryName != $row["c1Name"])
	{
		$strSideBar .= "≺p≻" . $row["c1Name"] . "≺/p≻";
	}
	$strSideBar .= "≺a href = '/post/" .$row["p2Slug"]. "'≻≺img src = '/media/" . $row["u4FileName"] ."'≻≺/a≻";
	$strPreviousCategoryName = $row["c1Name"];
}

It is amazing that this more robust idea also made the routine simpler as well. I kept the part of the algorithm filtering categories of years. Later on I will decide what to do with year categories. We still got the question of what to do if there are no images? At first I thought that if there are no images then we use another more textual oriented algorithm but later I discovered that it is perfectly fine to have a textual list as well so I just put it under the images. For this to work I essentially took level 2 of the query I created in post 35 and added a column Title to it:

SELECT b.*
FROM
(
	-- Level 2, subquery b
	-- All Posts with the same Categories as the current Post.
	-- pc2rowNum row numbers reset on change of Category.
	-- Get all Posts except current Post: pc2.PostID != a.p1ID
	SELECT p2.Slug as p2Slug, p2.Title as p2Title, a.c1Name, pc2.PostID as pc2PostID,
		(SELECT @pc2rowNum := IF(@pc2cat = pc2.CategoryID, @pc2rowNum + 1, 1)) as pc2rowNum,
		(SELECT @pc2cat := pc2.CategoryID) as pc2Cat
	FROM PostCategory pc2 JOIN Post p2 JOIN
	(
		-- Level 1, subquery a.
		-- All Categories of the current Post. Initialize pc2rowNum and pc2cat.
		SELECT p1.ID as p1ID, p1.Slug as p1Slug, pc1.CategoryID as pc1CategoryID,
			c1.Name as c1Name, p1.PublishedOn as p1PublishedOn,
			(SELECT @pc2rowNum := 0), (SELECT @pc2cat := 0)
		FROM Post p1, PostCategory pc1, Category c1
		WHERE p1.ID = pc1.PostID AND pc1.CategoryID = c1.ID AND p1.Slug = ?
		ORDER BY CategoryID, PublishedOn DESC
	)
	AS a
	WHERE pc2.CategoryID = a.pc1CategoryID AND pc2.PostID != a.p1ID AND p2.ID = pc2.PostID
) AS b
WHERE b.pc2rowNum ≺ 6

The rendering of this is straight forward:

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-left-sidebar-part-1.aspx
$StrLeftSideQuery2 = GetQueryWithData($StrLeftSideQuery2,$strSlug);
$result = $mysqli-≻query($StrLeftSideQuery2) or die("Error query.." . mysqli_error($mysqli));
$strPreviousRowNumber = "";
while ($row = mysqli_fetch_array($result))
{
	$strRowNumber = $row["pc2rowNum"];
	if ($strRowNumber == "1" && $strPreviousRowNumber != $row["pc2rowNum"])
	{
		$strSideBar .= "≺p≻" . $row["c1Name"] . "≺/p≻";
	}
	$strSideBar .= "≺a class = 'catlink' href = '/post/" .$row["p2Slug"]. "'≻" . $row["p2Title"] . "≺/a≻";
	$strPreviousRowNumber = $row["pc2rowNum"];
}

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-left-sidebar-part-1.aspx
if ($strSideBar == "")
{
	$strSideBar = "≺p≻This post is one-of-a-kind so there are no other posts related to this post.≺/p≻";
}

In this algorithm I don't filter for years. As a little extra I test if the $strSideBar is emtpy and if that is the case then I set a text that this is a one-of-a-kind post.

This will do for now.