Wednesday, 25-Nov-15 21:12:00 CET

Porting my blog for the second time, left sidebar

This is post #35 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.

It is time to create the left sidebar. I must admit that it was not so easy to create the query of the left sidebar. The biggest problem I had was that developing the query in the MySql console application gave a different result compared to when the query is evaluated from within PHP.

To avoid the difference between MySql console and PHP I decided to develop the query from within PHP. To facilitate this I made my own MySql console application. It is a PHP page with an edit field and a button 'Evaluate'. When pressing the button the text in the edit field is sent to the PHP page and it lets MySql evaluate the query and when the result comes back it is presented as an HTML table.

It is absolutely dangerous to have such a program on the live webserver but since the server is currently only running offline I feel fine with this. When I publish this website I will make sure there is no query program with full access to the database.

Here is a useful part of my MySql console program. It is a PHP routine that can display the result of any query in the form of a simple HTML table:

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-left-sidebar.aspx
function QueryToTable($ip_strQuery, $ip_mysqli)
{
	$result = $ip_mysqli-≻query($ip_strQuery) or die("Error query.." . mysqli_error($ip_mysqli));
	$fields_num = mysqli_num_fields($result);
	$strTable = "≺table border='1'≻≺tr≻";
	for($i=0; $i≺$fields_num; $i++)
	{
		$field = mysqli_fetch_field($result);
		$strTable .= "≺th≻{$field-≻name}≺/th≻";
	}
	$strTable .= "≺/tr≻
";
	while($row = mysqli_fetch_row($result))
	{
		$strTable .= "≺tr≻";
		foreach($row as $cell)
		{
			$strTable .= "≺td≻$cell≺/td≻";
		}
		$strTable .= "≺/tr≻";
	}
	mysqli_free_result($result);
	return $strTable;
} // QueryToTable()

With my query console application in PHP I could create the MySql query that I need for the the left sidebar. This query is so far in this project one of the most complex queries I ever created for MySql. The technique I am using here is something often being used to select n records from each group. In my case the group is the Category. I want 9 Posts from each Category.

-- Level 4
SELECT u4.FileName as u4FileName, u4.Width as u4Width, u4.Height as u4Height, c.pc2rowNum, c.c1Name, c.p2Slug
FROM
(
	-- Level 3, subquery c
	-- All Posts from a limited to 9 Posts per Category
	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, 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 = 'Jasmine-tea'
			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 ≺ 10
) AS c JOIN PostURL pu4 JOIN URL u4 ON pu4.PostID = c.pc2PostID AND pu4.URLID = u4.ID
WHERE u4.IsFileContent = 1 AND u4.TagName = 'img'

The inner most part, level 1, of the query is where it all starts. In my test case a Post called 'Jasmine-tea' was the current Post. The innermost part of the query gets a list of all Categories that the current Post is connected to.

Also the inner most query is the place where the user defined variables of the 'n records per group' are initialized, @pc2rowNum and @pc2cat. In the MySql console the variables are auto initialized but when running the query from PHP they need explicit initialization, or it will not work properly.

In level two all other Posts in the database sharing the same Categories as the current Post are listed and they are provided a row number. The row number of these Posts are reset each time the Category is switched.

In level three all Posts from the previous query with a row number greater than nine are removed from the list.

In level four all images available in the Posts of the previous query are gathered.

When the query is evaluated in PHP there is a small routine generating the html for the sidebar. It looks like this:

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

In this solution I decided to hide all categories of the years. I am not sure about this will stay but for now that feels all right.

In the CSS file I added a rule for these side bar images:

/* http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-left-sidebar.aspx */
#leftcolumn a img
{
	width: 97px;
	margin:3px;
}

I am pleased with this sidebar. Next time I will work on the right sidebar.