Tuesday, 06-Oct-15 20:09:00 CEST

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

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

When I get the data for a specific post I would like to be able to get all the data I need in one query. Later I will introduce subsequent queries but for the initial presentation it would be nice to have one big query getting all the data I need. Especially the previous and next post is a bit of a challenge. I tried various methods to achieve this and it could be so that if I had an outer join function I might be able to achieve my goal. So what can I do without outer join? I decided to introduce new links to the Post entity, namely a Next and Previous link. These links are allowed to be null.

Adding two loops to the Post entity in the E-R diagram of the jensblog database.

When I see these two loops to the Post entity itself I wonder if it is still easy to delete all the data of the database. I wonder if syncing up the changed database will work properly. A friend of mine, a very successful businessman, tells me that you should not let fear stand in the way of your goals. So I fear nothing, not even Schrödingers Tables so I just press the sync button to sync this E-R diagram to the Ubuntu server, but I hold my breath a little.

There was nothing to fear. Just annoying. MySQL workbench would not let me create PrevID and NextID at the same time. First I had to create PrevID, sync the model and then create NextID and sync again. Oh well it is there now.

This is the routine in my Perl program that sets up the Next and Previous:

my $sth = $dbh -≻ prepare('SELECT ID, PublishedOn FROM Post ORDER BY PublishedOn DESC');
$sth-≻execute( );
my @row;
my @arrayIDs = ();
my @arrayPublishedOn = ();
my $strPrevID = "";
while (@row = $sth-≻fetchrow_array)
{
	push(@arrayIDs, $row[0]);
	push(@arrayPublishedOn, $row[1]);
}

for (my $i = 0; $i ≺ scalar(@arrayIDs); $i++)
{
	my $idNext = -1;
	my $idPrev = -1;
	
	if ($i ≻ 0)
	{
		$idNext = $arrayIDs[$i-1];
	}
	if (($i + 1) ≺ scalar(@arrayIDs))
	{
		$idPrev = $arrayIDs[$i+1];
	}
	print "i: " . $i . "	Current:" . $arrayIDs[$i] . ",	Next: " . $idNext . ", 	Prev:" . $idPrev . ",	On:" . $arrayPublishedOn[$i] ."
";
	if ($idNext ≻= 0 and $idPrev ≻= 0)
	{
		$dbh-≻do('UPDATE Post SET NextID = ?, PrevID = ? WHERE ID = ?' , undef, $idNext, $idPrev , $arrayIDs[$i]);
	}
	if ($idNext ≺ 0 and $idPrev ≻= 0)
	{
		$dbh-≻do('UPDATE Post SET PrevID = ? WHERE ID = ?' , undef, $idPrev , $arrayIDs[$i]);
	}
	if ($idNext ≻= 0 and $idPrev ≺ 0)
	{
		$dbh-≻do('UPDATE Post SET NextID = ? WHERE ID = ?' , undef, $idNext , $arrayIDs[$i]);
	}
}

So now I can query a record like this:

mysql≻ select ID, PrevID, NextID from Post order by PublishedOn desc;
+-----+--------+--------+
| ID  | PrevID | NextID |
+-----+--------+--------+
| 319 |    158 |   NULL |
| 158 |    278 |    319 |
| 278 |    269 |    158 |
| 269 |    256 |    278 |
| 256 |     64 |    269 |
|  64 |     46 |    256 |
|  46 |    400 |     64 |
| 400 |    222 |     46 |
| 222 |    183 |    400 |

With PrevID and NextID I can get the data I want in one query. But that is for the next time.