Porting my blog for the second time, render posts

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

So far I created a Perl program to load the XML from the old blog into a database. Then I created a layout for presenting the blog. Now it is time to put things together, to render pages of the blog website. Up until now I had a static HTML document for testing the layout. Now I need to transfer that to the web server, my Ubuntu server, and change a PHP page to produce pages of that layout. When I worked out how to do URL rewriting then I already created a PHP program for testing, see /post/Porting-my-blog-for-the-second-time-setting-up-Apache2. That is the page where I will insert my layout. Just like that. Copy and Paste.

Now suppose we arrive at the website and we need to find the latest blog post. How do you do that with MySQL?

mysql≻ SELECT PublishedON FROM Post ORDER BY PublishedOn DESC LIMIT 1;
+---------------------+
| PublishedON         |
+---------------------+
| 2015-04-06 20:57:00 |
+---------------------+
1 row in set (0.00 sec)

First of all I cut out all the CSS from my layout page and place that in a separate file that I include from my php so that it is out of the way while I work on other things. I just need some sort of trick to figure out that it is going to work so I decide to insert this query in the php page and then present the result as title of the document. Like this:

$strPublishedOn = "";
if (count($arrayArgs) ≻ 0)
{
	print_r($arrayArgs);
}
else
{
	print "No valid args≺br≻";
	
	$query = "SELECT PublishedON FROM Post ORDER BY PublishedOn DESC LIMIT 1";
	$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
	if ($row = mysqli_fetch_array($result))
	{
		$strPublishedOn = $row["PublishedON"];
	}
}
?≻
≺!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"≻
≺html≻
≺head≻
	≺meta charset="utf-8" /≻
	≺meta name="viewport" content="width=device-width, initial-scale=1"≻
	≺title≻≺?php echo $strPublishedOn; ?≻≺/title≻
	≺link rel="stylesheet" href="jensblog.css" type="text/css" /≻

And it works. It looks like this:

To celebrate this achievement I decided to display also the content from that same blog post. To my surprise the content that came out of the database did not have the image URLs resolved that I worked on so much, see /post/Porting-my-blog-for-the-second-time-images-part-5. It turns out that the core of the image processing routine of my perl program was broken.

So I had to fix that. If you go back to the post in the link here above you will find the line that failed is commented in the snippet here below.

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-render-posts.aspx
#$tag =~ s/^(.*?)($strParsedURL)(.*?)$/$1{URL:$iDatabaseIDofThisURLentry}$3/;
$tag =~ s/(.*?)(https?://|/image)(.+?)(["’])/$1{URL:$iDatabaseIDofThisURLentry}$4/;

Now I replaced the incorrect line with a working URL replacement.

In I expected to find errors like these at this point. Now when I start present the data from the database it will become visible. When it is visible I will start to notice when I made mistakes in the import. The good thing with this approach is that I can go back and fix the import. At some point I will have to decide that the blog is ready for production and that is the point when I cannot go back and fix things.