Monday, 05-Oct-15 20:04:00 CEST

Porting my blog for the second time, render posts part 2

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

A couple of posts ago /post/Porting-my-blog-for-the-second-time-setting-up-Apache2 I created a little todo list of what I had to do still. That list had 5 steps and I am still in the early stage of step 5. Why do I mention this? Have you ever been to the a lecture by a professor and he starts by telling you there are three steps for X where X can be anything professors like to talk about. He thoroughly writes step 1 on the white board and starts explaining. Then after some time he writes step 2 and then he continues. After some time the lesson is over and you are sitting there wondering when step 3 would come. I just hate that, that is why. We are at step 5 and we will be at step 5 for a long time.

Today I am thinking about the next and the previous. Actually only on previous. How do I in MySQL create a query that produces the data of the latest blog post but at the same time also produce the date and also slug of the previous post?

To try out this challenge I go absolutely wild with SQL queries. Here is a nice overview of joins in SQL http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins I think I could have used an outer join somehow to accomplish what I want but MySQL don’t have outer join. They got UNION but that is not the same. My solution is to make a query that takes the two first posts and I include the fields I need to present the data of the first page.

mysql≻ SELECT PublishedON, LEFT(Content,20) AS Content, Title, LEFT(Slug,20) AS Slug FROM Post ORDER BY PublishedOn DESC LIMIT 2;
+---------------------+----------------------+-----------------------------------------+----------------------+
| PublishedON         | Content              | Title                                   | Slug                 |
+---------------------+----------------------+-----------------------------------------+----------------------+
| 2015-04-06 20:57:00 | ≺p≻≺img style="float | A pouch for Samsung Galaxy S4           | Here-is-how-to-sew-a |
| 2015-02-23 14:11:00 | ≺div class="entry-co | A gentleman doesn’t talk about politics | A-gentleman-doesnt-t |
+---------------------+----------------------+-----------------------------------------+----------------------+
2 rows in set (0.00 sec)

That will do for now. In the real query I don’t do LEFT this here above is just so that I can display the listing here with a more convenient line length.

$query = "SELECT PublishedON, Content, Title, Slug FROM Post ORDER BY PublishedOn DESC LIMIT 2";
$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
if ($row = mysqli_fetch_array($result))
{
	$strTitle = $row["Title"];
	$strPublishedOn = $row["PublishedON"];
	$strContent = $row["Content"];
}
if ($row = mysqli_fetch_array($result))
{
	$strPrevTitle = $row["Title"];
	$strPrevPublishedOn = $row["PublishedON"];
	$strPrevSlug = $row["Slug"];
}

I just loads the date one after another. The first is the current post and the second is the previous post.

In this case I don’t need any next arrow so I need to "disable" the next cell of the navigation table.

≺style≻
    .nav td:last-child
    {
        background:#fff;
    }
≺/style≻

This is really not the way to do it but for now it is sufficient. The interesting thing now is that now I can click the previous link for the first time but there is no handling of that situation. So... Let’s build that. But that is for the next time.