JENS MALMGREN I create, that is my hobby.

Porting my blog for the second time, editing part 7

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

I am getting closer to the moment when I can start on publish this blog but before I do that I have to fix one important thing, namely: make it possible to create new blog posts. If you followed this series it might come as a surprise that I had not made this earlier. It was not necessary until now. Now it is time to make it possible to create a new blog post.

The current edit functionality works well for existing blog posts. It is not working for removing, adding or renaming the slug of a blog post or the entire post. So I need to make that. I will try to inject this blog post administration functionality into the existing edit posts functionality. Essentially I will try to remake what I did for the categories page, port the category solution to the posts as well. For the categories it looked like this:

I can change a category, delete and I can add categories with this screen. Now I will create the same sort of screen for administration of the posts.

So I will take the design pattern of the work I created in post #48 of this series /post/Porting-my-blog-for-the-second-time-editing-part-3 and port it into the editpost.php functionality. From here on I will call this edit-post because it flows better in written text.

The previous edit-post functionality is based on the slug and the slug only. That is inconvenient when we wants to edit everything including the slug. When the edit-post program starts it will start in the browse mode. That is the mode in which I present a list of posts in a table. So I made that table and it looks like this.

For this table I created a little header that will always stay on top. I did this by specifying the width of each column exactly and the width of the entire table. Almost perfect. This might be inconvenient when editing on a smartphone but that is a challenge to solve later.

When pressing change I managed to make the original edit fields to show. That was great as such but it would not save so when I compared edit-post with the category equivalent then I realized the submit button had to have the ID save_changes to work. Porting snags.

The categories handling in the edit-post had come caveats here and there. This part too was relying on the Slug instead of the ID. I had a query with a sub query just for the purpose of converting the Slug into an ID and that was not needed anymore so the algorithm became simpler.

Then it is time for the delete functionality. Interesting... Had to have a look at the entity relationship chart to see what I had to do:

There are two ways to go here. We start with actually deleting a post. If I were to delete the post then I first had to delete the PostCategory records linked to the Post I want to delete. The same with Feedback and PostURL. So far it is doable. When we delete a PostCategory then I don't want to delete Category records because a Category may show up in any Post. The PostURL is a trickier. Suppose we got a link to an URL only being used in that one Post I would like to delete. Then ideally before deleting the Post I would find out that the URL was being used in one Post only and could be deleted. Then I would delete the PostURL and the finally the Post. This is a lot of work.

There is another way to do this. We could just pretend that the post has been deleted. We put a flag in the post IsDeleted and set this to 1 and then keep it. Obviously all queries presenting Posts need to avoid IsDeleted Post records. Let's try this.

I tried it and it is really comical how I first explains that obviously the queries presenting Posts need to respect the IsDeleted flag but did I? No. So nothing happened. And that was logical. But here comes a little wish. I want to be able to see also deleted Posts. For this I added a checkbox 'Display also deleted posts' to the browse list.

When that is changed the form is resubmitted and the checkbox was set then the query is modified to display also deleted posts and when not set the query only displays visible posts. Now the rendering of the rest of the blog need to learn to use this IsDeleted flag as well. Perhaps I already implemented that I don't remember.

While we are at this subject I would like to point out that although I change the routines for how to name a blog it does not mean that the user of the blog (right now that is only myself) can go ahead and just change the name. It does not work like that. A blog system should be made so that it works for Google in the first place. Then it is made for the editor of the blog. In that order. All those permanent link schemes are pretending it is possible to change the name of a blog-post just like that. That was how it was before but these days a link is something we rely on. You cannot just go and change things. First of all, all names of pages are supposed to stay like they are. Secondly if they don't change then the older versions of the same page should be directed to the last version of the page either by a permanent redirect or a temporary redirect. All that about permanent links is a thing of the past. All links these days are permanent links, or they are properly redirected. Well, I will have to figure out how to implement this properly but I leave it as a to-do item for myself.

Back to the edit functionality. Previously Content, Title and Description could be edited. Now I added possibility to edit IsDeleted, IsPublished, IsCommentsEnabled, PublishedOn and Slug. The field ModifiedOn is not edited by set to the current date-time every time a post changed.

Now we come to some intricate consequences of editing the PublishedOn date, adding new posts or deleting posts. Already when I worked on the import of the old blog I was struggling with the next and previous navigation. I created a solution for this in post #26 of this series:

/post/Porting-my-blog-for-the-second-time-render-posts-part-4

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-editing-part-7.aspx
# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-render-posts-part-3.aspx
if ($linkNextPrevious)
{
	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;
		my $iPositionType = 0;
		
		if ($i == 0)
		{
			$idNext = $arrayIDs[-1 + scalar(@arrayIDs)];
			$iPositionType = 1;
		}
		if ($i ≻ 0)
		{
			$idNext = $arrayIDs[$i-1];
		}
		if (($i + 1) ≺ scalar(@arrayIDs))
		{
			$idPrev = $arrayIDs[$i+1];
		}
		if ($i == -1 + scalar(@arrayIDs))
		{
			$idPrev = $arrayIDs[0];
			$iPositionType = 2;
		}

		print "i: " . $i . "	Current:" . $arrayIDs[$i] . ",	Next: " . $idNext . ", 	Prev:" . $idPrev . ",	On:" . $arrayPublishedOn[$i] . ", PositionType: " . $iPositionType ."
";
		$dbh-≻do('UPDATE Post SET NextID = ?, PrevID = ?, PositionType = ? WHERE ID = ?' , undef, $idNext, $idPrev , $iPositionType, $arrayIDs[$i]);
	}
}

 

Now when editing the PublishedOn I need to run that algorithm to set the next and previous right. I had to port it to PHP. There are differences in perl and PHP but it was an easy task to port the algorithm to PHP. The approach with the import routine is that it does not matter how long time things are taking. That is not entirely the case with the rendering of the PHP. It takes to long to make an update query for every post in the blog. I found a way of making a giant update query where the next and previous was set in one go. That worked very quickly. Made a variable $bPossiblyTheOrderOfPostsHasChange and this is just set to 1 in those cases it is needed.

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-editing-part-7.aspx
if ($bPossiblyTheOrderOfPostsHasChanged == 1)
{
	$result = $mysqli-≻query("UPDATE Post SET PositionType = 0 WHERE IsDeleted = 1") or die("error query.." . mysqli_error($mysqli));
	$result = $mysqli-≻query("SELECT ID, PublishedOn FROM Post WHERE IsDeleted = 0 ORDER BY PublishedOn DESC") or die("error query.." . mysqli_error($mysqli));
	
	$arrayIDs = array();
	$arrayPublishedOn = array();
	$strPrevID = "";
	while ($row = mysqli_fetch_array($result))
	{
		array_push($arrayIDs, $row[0]);
		array_push($arrayPublishedOn, $row[1]);
	}

	$query = "INSERT INTO Post (NextID, PrevID, PositionType, ID) VALUES ";
	$comma = "";
	for ($i = 0; $i ≺ count($arrayIDs); $i++)
	{
		$idNext = -1;
		$idPrev = -1;
		$iPositionType = 0;
		
		if ($i == 0)
		{
			$idNext = $arrayIDs[-1 + count($arrayIDs)];
			$iPositionType = 1;
		}
		if ($i ≻ 0)
		{
			$idNext = $arrayIDs[$i-1];
		}
		if (($i + 1) ≺ count($arrayIDs))
		{
			$idPrev = $arrayIDs[$i+1];
		}
		if ($i == -1 + count($arrayIDs))
		{
			$idPrev = $arrayIDs[0];
			$iPositionType = 2;
		}

		$query .= "$comma ($idNext, $idPrev , $iPositionType, $arrayIDs[$i])";
		$comma = ",";
	}
	$query .= " ON DUPLICATE KEY UPDATE NextID = VALUES(NextID), PrevID = VALUES(PrevID), PositionType = VALUES(PositionType), ID = VALUES(ID)";
	$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
}

Where the original algorithm ordered all posts now this algorithm had to avoid deleted posts as well as not yet published posts so that next and previous walks over the deleted and not yet published posts. Then I started experimented and suddenly the next and previous navigation of the blog got an hiccup?! and failed!

Somehow I had managed to turn around the logic of next and previous in such way it worked until today. Soo strange.

Also at the time I worked on navigation last time in post #26 (/post/Porting-my-blog-for-the-second-time-render-posts-part-4) then I introduced the PositionType. It becomes really easy to query for the first or last post in the blog but I never used that in the navigation. However, when relying on PositionType when deleting a Post the previous and next algorithm will not touch the deleted so I need to seperatly set the PositionType to 0 for all deleted posts. Here is the new navigation with the replaced code in comments.

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-editing-part-7.aspx
# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-localize-date-and-time.aspx
if (array_key_exists("post", $arrayArgs))
{
	$query = "SELECT c.Title AS Title, c.Description as Description, c.Slug AS Slug, c.ID, c.PublishedOn AS PublishedOn, c.Content AS Content, c.PositionType AS PositionType, p.Title AS PrevTitle, p.Slug AS PrevSlug, p.PublishedOn AS PrevPublishedOn, n.Title AS NextTitle, n.Slug AS NextSlug, n.PublishedOn AS NextPublishedOn FROM Post c, Post p, Post n WHERE c.Slug = ? and c.PrevID = p.ID and c.NextID = n.ID;"; // "and c.ID = p.NextID and c.ID = n.PrevID;";

	$query = GetQueryWithData(1, $query,$arrayArgs["post"]);
	$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
	if ($row = mysqli_fetch_array($result))
	{
		$strTitle = $row["Title"];
		$strDescription = $row["Description"];
		$dtPublishedOn = new DateTime($row["PublishedOn"]);
		$strSlug = $row["Slug"];
		$strID = $row["ID"];
		$strPrevTitle = $row["PrevTitle"];
		$dtPrevPublishedOn = new DateTime($row["PrevPublishedOn"]);
		$strPrevSlug = $row["PrevSlug"];
		$strNextTitle = $row["NextTitle"];
		$dtNextPublishedOn = new DateTime($row["NextPublishedOn"]);
		$strNextSlug = $row["NextSlug"];
		$iPositionType = $row["PositionType"];
		$strContent = $row["Content"];
	}
}
else
{
	// $query = "SELECT PublishedOn, Content, Title, Description, Slug, ID 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"];
		// $strDescription = $row["Description"];
		// $dtPublishedOn = new DateTime($row["PublishedOn"]);
		// $strContent = $row["Content"];
		// $strSlug = $row["Slug"];
		// $strID = $row["ID"];
	// }
	// if ($row = mysqli_fetch_array($result))
	// {
		// $strPrevTitle = $row["Title"];
		// $dtPrevPublishedOn = new DateTime($row["PublishedOn"]);
		// $strPrevSlug = $row["Slug"];
	// }
	// $iPositionType = 1;

	$query = "SELECT c.Title AS Title, c.Description as Description, c.Slug AS Slug, c.ID, c.PublishedOn AS PublishedOn, c.Content AS Content, c.PositionType AS PositionType, p.Title AS PrevTitle, p.Slug AS PrevSlug, p.PublishedOn AS PrevPublishedOn FROM Post c, Post p WHERE c.PositionType = 1 and c.PrevID = p.ID;";
	$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
	if ($row = mysqli_fetch_array($result))
	{
		$strTitle = $row["Title"];
		$strDescription = $row["Description"];
		$dtPublishedOn = new DateTime($row["PublishedOn"]);
		$strSlug = $row["Slug"];
		$strID = $row["ID"];
		$strPrevTitle = $row["PrevTitle"];
		$dtPrevPublishedOn = new DateTime($row["PrevPublishedOn"]);
		$strPrevSlug = $row["PrevSlug"];
		$iPositionType = $row["PositionType"];
		$strContent = $row["Content"];
	}
}

Ooooh it is so nice when it starts to work properly! I am really delighted. Heh? Even though comments are disabled they are displayed? What is that? Well they rendering page don't check if the comments should be enabled or not so they are always visible.

So to avoid this I had to define a variable $bFeedbackEnabled at the beginning of post.php. In the same routine handling the next and previous I added the IsCommentsEnabled field and initialized the $bFeedbackEnabled with that.

Then it was just a question of rendering feedback if it was enabled. This is how the available feedback is rendered if available:

$strFeedbackTable = "";
if (isset($strSlug) && $strSlug != "" && $bFeedbackEnabled == 1)
{
	# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-editing-part-7.aspx
	# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-feedback.aspx
	$strFeedbackQuery = "SELECT p.ID, p.Slug, fb.Content, fb.Date FROM Post p, Feedback fb WHERE Slug = ? AND fb.PostID = p.ID AND fb.IsDeleted = 0";
	$result = $mysqli-≻query(GetQueryWithData(1, $strFeedbackQuery,$strSlug)) or die("Error query.." . mysqli_error($mysqli));
	$strFeedbackTable = "≺div class = 'feedback'≻
";
	$iFeedbackCount = 0;
	while ($row = mysqli_fetch_array($result))
	{
		$fbcontent = $row["Content"];
		$fbcontent.preg_replace("/
/", "≺br≻", $fbcontent);
		$dtFeedbackOn = new DateTime($row["Date"]);
		$strFeedbackTable .= "≺div≻";
		$strFeedbackTable .= "≺time datetime = '" . $dtFeedbackOn-≻format(DateTime::ATOM) . "'≻" . $dtFeedbackOn-≻format(DateTime::RFC850) . "≺/time≻
";
		$strFeedbackTable .= "≺div≻" . $fbcontent . "≺/div≻";
		$strFeedbackTable .= "≺/div≻";
		$iFeedbackCount++;
	}
	if ($iFeedbackCount == 0)
	{
		$strFeedbackTable = "";
	}
	else
	{
		$strFeedbackTable .= "≺/div≻
";
	}
	$fbFeedBackNum1 = rand(2 , 5);
	$fbFeedBackNum2 = rand(3 , 7);
} // $strSlug is defined

Here is how it looks when I render the actual feedback form if that is required.

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-feedback.aspx
$strFeedbackForm = "";
$strOptionalPageScript = "";
$strPageSpecificInitialization = "";
if ($bFeedbackEnabled == 1)
{
	$strFeedbackForm = "
				≺form id = 'fbform' action = '/post/$strSlug' method = 'post'≻
					≺textarea id = 'fb' name = 'fb' placeholder = 'Enter your feedback here...'≻≺/textarea≻
					Enter the answer of $fbFeedBackNum1 + $fbFeedBackNum2 here below to unlock the submit button.
					≺input type = 'text' id = 'fbtest' name = 'fbtest' placeholder = '$fbFeedBackNum1 + $fbFeedBackNum2 = ?'≻
					≺input id = 'fbsubmit' type = 'submit' value = 'Submit' disabled = 'disabled'≻
					≺div id = 'fberror'≻Cannot accept feedback with any of these characters≺br≻: [ ] { } ≺ ≻  / + - * = % @ ; & | $ www .co≺br≻If you need to send me a link or something personal you can do that on jens at malmgren dot nl.≺/div≻
				≺/form≻";

	$strOptionalPageScript = "var fbFeedBackSum = " . ($fbFeedBackNum1 + $fbFeedBackNum2) . ";
	";
	$strOptionalPageScript .= file_get_contents("fbvalidation.js");


	$strPageSpecificInitialization = "$('#fb,#fbtest').keyup(keytest);
	";
}

With this I achieved a bunch of things. Now I decided to import the blog "a final time". For this I decided to remove the old blogs filenames at the bottom of each post in analyze.pl

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-editing-part-7.aspx
# Debug! Remove when final.
# $dictStringFieldToValue{"content"} .= "≺br≻" . $filenamePost;

Here it is "commented out". Wohoo.

Done with editing. There are still plenty of things to fix. As time progressed I started to dislike the layout I created. But that is things to fix later because now it is about time to start on publishing this. But that is for the next time.

Enter the answer of 4 + 3 here below to unlock the submit button.
Cannot accept feedback with any of these characters
: [ ] { } < > \ / + - * = % @ ; & | $ www .co
If you need to send me a link or something personal you can do that on jens at malmgren dot nl.

I was born 1967 in Stockholm, Sweden. I grew up in the small village Vågdalen in north Sweden. 1989 I moved to Umeå to study Computer Science at University of Umeå. 1995 I moved to the Netherlands where I live in Almere not far from Amsterdam.

Here on this site I let you see my creations.

I create, that is my hobby.