Monday, 11-Jan-16 22:21:00 CET

Porting my blog for the second time, categories part 3

This is post #43 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 https://www.malmgren.nl/post/Porting-my-blog-for-the-second-time-Project-can-start.aspx.

In this post I finish the categories.php page! At the time of writing I already finished the page so I will just briefly talk about the page, how it is made etc.

≺?php
# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-categories-part-3.aspx
$strContent = "";
$strLeftSideBar = "";
$domain = "jensblog.nl";
$strSlug = "";
$strID = "";

$dtPublishedOn = null;
$strTitle = "";
$strPrevTitle = "";
$strPrevSlug = "";
$dtPrevPublishedOn = null;
$strNextTitle = "";
$strNextSlug = "";
$dtNextPublishedOn = null;
$iPositionType = 0;

include "dbconnection.php";
include "generic.php";

$strTitle = "Category";
$strDescription = "";
$strLeftSideBar = "";
$strCategorySlug = "";
$strTitle = "";

if (array_key_exists("category", $arrayArgs) && $arrayArgs["category"] != "")
{
	$strCategorySlug = $arrayArgs["category"];
	$query = GetQueryWithData("SELECT Title, Description, Content FROM Category WHERE Slug = ?", $strCategorySlug);
	$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
	if ($row = mysqli_fetch_array($result))
	{
		$strTitle = $row["Title"];
		$strDescription = $row["Description"];
		$strContent = "≺h1≻$strTitle≺/h1≻" . $row["Content"];
		
		$query = GetQueryWithData("
		SELECT p.Slug AS pSlug, p.Title AS pTitle, p.PublishedOn AS pPublishedOn
		FROM Category c, PostCategory pc, Post p
		WHERE pc.CategoryID = c.ID AND p.ID = pc.PostID AND c.Slug = ?
		ORDER BY p.PublishedOn DESC", $strCategorySlug);
		$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));

		$strContent .= "≺table≻≺tr≻≺th≻Post≺/th≻≺th≻Published On≺/th≻≺/tr≻";
		while ($row = mysqli_fetch_array($result))
		{
			$dtPublishedOn = new DateTime($row["pPublishedOn"]);
			$strContent .= "≺tr≻≺td≻≺a href = '/post/" .$row["pSlug"]. "'≻" . $row["pTitle"] . "≺/a≻≺/td≻≺td≻≺time datetime = '" . $dtPublishedOn-≻format(DateTime::ATOM) . "'≻" . $dtPublishedOn-≻format(DateTime::RFC850) . "≺/time≻≺/td≻≺/tr≻";
		}
		$strContent .= "≺/table≻";
		
		
		$query = GetQueryWithData("SELECT c.Slug, c.Title FROM Category c WHERE c.TypeID = 1 AND c.Slug ≺≻ ?", $strCategorySlug);
		$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
		$strLeftSideBar = "≺p≻≺b≻Category≺/b≻≺p≻";
		while ($row = mysqli_fetch_array($result))
		{
			$strLeftSideBar .= "≺a href = '/category/" .$row["Slug"]. "'≻" . $row["Title"] . "≺/a≻≺br≻";
		}
		$strLeftSideBar .= "≺p≻≺b≻Year≺/b≻≺/p≻";
		$query = "SELECT * FROM (SELECT c.Slug, c.Title FROM Category c WHERE c.TypeID = 2) AS a ORDER BY Slug DESC";
		$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
		while ($row = mysqli_fetch_array($result))
		{
			$strLeftSideBar .= "≺a href = '/category/" .$row["Slug"]. "'≻" . $row["Title"] . "≺/a≻≺br≻";
		}
		$strLeftSideBar .= "";
	}
	else
	{
		PrepareForAllCategories();
	}
}
else
{
	PrepareForAllCategories();
}

function PrepareForAllCategories()
{
	global $strTitle;
	global $strContent;
	global $strLeftSideBar;
	global $mysqli;
	
	$strTitle = "Categories in my blog. Years I made things";
	$query = "SELECT c.Slug, c.Title, c.Description, COUNT(*) as Count FROM Category c, PostCategory pc WHERE c.TypeID = 1 AND pc.CategoryID = c.ID GROUP BY Slug";
	$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
	$strContent = "≺h1≻Category≺/h1≻≺p≻On this page you find all categories in my blog.≺/p≻≺table≻≺tr≻≺th≻Title≺/th≻≺th≻Description≺/th≻≺th≻Count≺/th≻≺/tr≻";
	while ($row = mysqli_fetch_array($result))
	{
		$strContent .= "≺tr≻≺td≻" . $row["Title"] . "≺/td≻≺td≻≺a href = '/category/" .$row["Slug"]. "'≻" . $row["Description"] . "≺/a≻≺/td≻≺td≻" . $row["Count"] . "≺/td≻≺/tr≻";
	}
	$strContent .= "≺/table≻≺h1≻Year≺/h1≻≺table≻≺tr≻≺th≻Year≺/th≻≺th≻Count≺/th≻≺/tr≻";
	$query = "SELECT * FROM (SELECT c.Slug, c.Title, c.Description, COUNT(*) as Count FROM Category c, PostCategory pc WHERE c.TypeID = 2 AND pc.CategoryID = c.ID GROUP BY Slug) AS a ORDER BY Slug DESC";
	$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
	while ($row = mysqli_fetch_array($result))
	{
		$strContent .= "≺tr≻≺td≻≺a href = '/category/" .$row["Slug"]. "'≻" . $row["Title"] . "≺/a≻≺/td≻≺td≻" . $row["Count"] . "≺/td≻≺/tr≻";
	}
	$strContent .= "≺/table≻";

	$queryLatestPostInEveryCategory = "
	-- Level 2
	SELECT a.* FROM
	(
		-- Level 1, subquery a
		SELECT c.Title AS cTitle, c.Slug AS cSlug, p.Slug AS pSlug, p.Title AS pTitle, p.PublishedOn AS pPublishedOn
		FROM Category c, PostCategory pc, Post p
		WHERE c.TypeID = 1 AND pc.CategoryID = c.ID AND p.ID = pc.PostID
		ORDER BY p.PublishedOn DESC
	)
	AS a GROUP BY a.cSlug";
	
	$strLeftSideBar .= "≺p≻≺b≻Latest post of every Category:≺/b≻≺/p≻";
	$result = $mysqli-≻query($queryLatestPostInEveryCategory) or die("Error query.." . mysqli_error($mysqli));
	while ($row = mysqli_fetch_array($result))
	{
		$dtPublishedOn = new DateTime($row["pPublishedOn"]);
		$strLeftSideBar .= "≺p≻≺b≻≺a href = '/category/" .$row["cSlug"]. "'≻" . $row["cTitle"] . "≺/a≻≺/b≻ ≺time datetime = '" . $dtPublishedOn-≻format(DateTime::ATOM) . "'≻" . $dtPublishedOn-≻format(DateTime::RFC850) . "≺/time≻≺br≻≺a href = '/post/" .$row["pSlug"]. "'≻" . $row["pTitle"] . "≺/a≻≺/p≻";
	}
	$strLeftSideBar .= "≺/table≻";
}

PreparePage(
	$strTitle,
	$strDescription,
	"",
	3, /* Category */
	$strContent,
	"/category/".$strCategorySlug,
	"",
	$strLeftSideBar,
	"",
	"");
?≻

Line 28 is important. That is the point during rendering when it is decided how to react on the input given by the arguments to the page. If there exists a category argument and it is not empty then we go on processing the page as if it was about a specific category handled on line 30 and forward. If not then we present the page as an overview of all categories. That is handled on line 78.

So if we take this sequentially and continue to talk about line 30 and forward then it start with that we pick out that category slug we just received. We assign this to the variable $strCategorySlug. This the specific category.

When we know the category page we are at then we can populate the Title, Description and Content of the category from the database. For the main content area of the page on line 46 we start generating a table, listing all posts connected to the current category. At line 57 we start creating the left sidebar. That is an simple bare bones overview of all categories in the blog.

There are two situations when we present all categories in the main content area and that is when a specific category is not specified, at line 78. It is also displayed when there is a category but it is not available in the database and that happens at line 73. In both these cases the function PrepareForAllCategories is called. This function creates a overview of all categories but with counts of how many posts are available in each category.

This is it for the categories.

Now I have two tabs left 'About' and 'Jens Art'. When I created that I will do another round of import. Then I come to editing and adding posts. Oh well, there is work to do.

But that is for the next time.