Eva 20 March 2016 Sunday, 20-Mar-16 20:54:29 CET

Eva 27 March 2016 Sunday, 27-Mar-16 21:23:37 CEST

Monday, 21-Mar-16 22:03:00 CET

Porting my blog for the second time, editing part 3

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

We are well into April of 2016 by now. It is over one year since the accident when I destroyed my domain controller server and since last summer Windows 2003 is no longer supported by Microsoft. But never the less this project is progressing one step at a time. Okay I had a bit of flu the last couple of weeks and I had a week of holidays from this project. Since last time I went over the old blog posts and made sure that all parts of already made posts have source code inside pre tags. To see how this works I imported the entire blog again and it works really well.

Now It is time to start edit categories. Or rather make it possible to edit categories. I need to be able to add categories and make it possible to edit the data of a category but suppose I want to delete a category, how am I going to do that? So for this I need to be able to browse categories. Essentially this is a list of categories. With that list I need to be able to add, delete and change items in the list. When I change a category I need to bring up all the data of the category and make it possible to change it and save the changes.

But first I need to fix the naming of the edit command. Right now I got one edit command but that is for editing posts only. I change this into editpost to avoid future misunderstandings. When that is done I can add the command editcats as well. For this I renamed all post.php to editpost.php. Then I started making room for the new command #editcats.

# http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-editing-part-3.aspx
if (isset($_POST['search']) && $_POST['search'] != "")
{
	if ($_POST['search'] == "#editpost" || $_POST['search'] == "#editcats")
	{
		if (!startsWith( $_SERVER['HTTP_REFERER'], "http://" . $domain))
		{
			header("Location: http://$domain");
			die;
		}
		
		if (array_key_exists("post", $arrayArgs))
		{
			$_SESSION['post'] = $arrayArgs["post"];
		}

		$strCommand = ltrim($_POST['search'], '#');
		$_SESSION['command'] = $strCommand;

		if (isset($_SESSION['JensBlogLoggedInUser']))
		{
			
			header("Location: http://$domain/" . $strCommand . ".php");
			die();
		}
		else
		{
			header("Location: http://$domain/login.php");
			die();
		}
	}
	else
	{
		$_SESSION['search'] = $_POST['search'];
	}
}

As you perhaps recall from post #46 (https://www.malmgren.nl/post/Porting-my-blog-for-the-second-time-editing.aspx) I use the searchfield of posts to enter commands to the blog engine. That way I need to do no visual changes when I add more functionality. Here above I check for a situation that I receive a command. It can be either '#editpost' or '#editcats'. Then also that leads to a php page with that same name if the user was logged in. Otherwise a detour is done through the login functionality.

Then it is time for creating the new functionality. I made a new page 'editcats.php':

For this browse form application I created a simple table for the category records displayed in a fixed height div with overflow set to auto so that when the content exceeds the the table then automatically scroll-bars will appear. At first I made two flat text columns with the text Change and Delete that I later can change into interactive buttons.

≺div id = "catbrowse"≻
≺?php
#  http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-editing-part-3.aspx
$query = "SELECT c.ID, c.Title, c.Description, c.ID, ct.Description AS ctDescription FROM Category c, CategoryType ct WHERE ct.ID = c.TypeID ORDER BY c.Description";
$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
$strCatList = "≺table≻";
while ($row = mysqli_fetch_array($result))
{
	$strCatList .= "≺tr≻";
	$strCatList .= "≺td≻" . $row['ID'] . "≺/td≻";
	$strCatList .= "≺td≻" . $row['Title'] . "≺/td≻";
	$strCatList .= "≺td≻" . $row['Description'] . "≺/td≻";
	$strCatList .= "≺td≻" . "Change" . "≺/td≻";
	$strCatList .= "≺td≻" . "Delete" . "≺/td≻";
	$strCatList .= "≺/tr≻";
}
$strCatList .= "≺/table≻";
echo $strCatList;
?≻
≺/div≻

Most of the time it is nice to move ahead in smaller steps to be sure of that the steps are correct. I created the change button and displayed a message that a button had been clicked.

Here is how I made the button. Like so:

while ($row = mysqli_fetch_array($result))
{
	$strCatList .= "≺tr≻";
	$strCatList .= "≺td≻" . $row['ID'] . "≺/td≻";
	$strCatList .= "≺td≻" . $row['Title'] . "≺/td≻";
	$strCatList .= "≺td≻" . $row['Description'] . "≺/td≻";
	$strCatList .= "≺td≻" . "≺button name = 'change' id='" . $row['ID'] . "'≻Change≺/button≻" . "≺/td≻";
	$strCatList .= "≺td≻" . "Delete" . "≺/td≻";
	$strCatList .= "≺/tr≻";
}

And to pick up a click of a change button I added a jQuery event handler to react to clicks of change buttons to present the ID on the screen. Like so:

$("button[name='change']").click(function()
{
	alert("Clicked change button of ID: " + this.id);
});

So far this is easy. Now it is time to get into the mechanics of this. Suppose the user clicks the change button, what happens then? Ideally we need to serve the data that belongs to that row and put it into editable fields and then hide the table of categories until the user clicked save.

There are two ways of doing this. Either one can use traditional ugly "round-trips" page reloads to the server with form actions that triggers when the user clicks a submit button or you could do it in a more modern way with ajax calls where the page stays as it is.

So I tossed the dice to decide what method to use and the dice came up with... the old method. That is fine. Another time I will dive into ajax.

With the old method all the change buttons needs to be submit buttons. When clicked they trigger a reload of the page giving the server a chance to process the button click. The challenge is that I got one button for every category. I really want a convenient way of finding out what row to change. For this I use a little client side event handler after the button is clicked before the form is submitted to the server. In that even handler I set an hidden field with the ID of the clicked category button. Like so:

$("input[value='Change']").click(function()
{
	$("input[name='changeid']").val(this.id);
});

The event-handler here applies to all input fields with value = 'Change'. When clicked the hidden field with name 'changeid' is set to the ID of the Change button.

≺input type="hidden" name="changeid" value""≻

This is how the hidden changeid field is created. Then the submit sends the form with the changeid value to the server and the form action is to load this same php page.

≺form id = "catsform" action = "editcats.php" method = "post"≻

At the top of the editcats.php file the changeid value needs to be read. Also there needs to be different modes for the page enabling different functions. For this I created this routine:

$iEditModeIsOneAndBrowseModeIsZero = 0;
$iEditModeCategoryID = 0;

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
	if (isset($_POST['changeid']) && $_POST['changeid'] != "")
	{
		$iEditModeIsOneAndBrowseModeIsZero = 1;
		$iEditModeCategoryID = $_POST['changeid'];
	}
}

Perhaps a surprisingly long variable name but it is no doubt what it is about. That is good. As I already said, one step at a time. So if changeid is set and it is not an empty string. In my next iteration if iEditModeIsOneAndBrowseModeIsZero was 0 then it should render only the browse. If it was 1 it should render the change form:

≺form id = "catsform" action = "editcats.php" method = "post"≻
≺?php
	if ($iEditModeIsOneAndBrowseModeIsZero== 0)
	{

		#  http://www.jens.malmgren.nl/post/Porting-my-blog-for-the-second-time-editing-part-3.aspx
		$query = "SELECT c.ID, c.Title, c.Description, c.ID, ct.Description AS ctDescription FROM Category c, CategoryType ct WHERE ct.ID = c.TypeID ORDER BY c.Description";
		$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
		$strCatList = "≺div id = 'catbrowse'≻≺table≻";
		while ($row = mysqli_fetch_array($result))
		{
			$strCatList .= "≺tr≻";
			$strCatList .= "≺td≻" . $row['ID'] . "≺/td≻";
			$strCatList .= "≺td≻" . $row['Title'] . "≺/td≻";
			$strCatList .= "≺td≻" . $row['Description'] . "≺/td≻";
			$strCatList .= "≺td≻" . "≺input type = 'submit' value = 'Change' id='" . $row['ID'] . "'≻";
			$strCatList .= "≺td≻" . "Delete" . "≺/td≻";
			$strCatList .= "≺/tr≻";
		}
		$strCatList .= "≺/table≻≺/div≻";
		$strCatList .= "≺input type='hidden' name='changeid' value=''≻";
		echo $strCatList;
	}
	
	if ($iEditModeIsOneAndBrowseModeIsZero== 1)
	{
		$dictCategoryIDToDescription = array();
		$query = GetQueryWithData(0, "SELECT ID, Description FROM CategoryType");
		$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
		while ($row = mysqli_fetch_array($result))
		{
			$dictCategoryIDToDescription[$row['ID']] = $row['Description'];
		}
		
		$strCatQuery = "SELECT c.ID, c.Title, c.Description, c.Slug, c.Content, c.TypeID FROM Category c WHERE c.ID = ?";
		$query = GetQueryWithData(1, $strCatQuery, $iEditModeCategoryID);
		$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));

		if ($row = mysqli_fetch_array($result))
		{
			?≻
				ID:≺br≻
				≺div style = 'border:1px solid #ccc;background-color:#eee;'≻≺?php echo $iEditModeCategoryID ?≻≺/div≻
				Title:≺br≻
				≺input type = 'text' id = 'title' name = 'title' placeholder = 'Title' value = '≺?php echo EscapeContent($row['Title']); ?≻'≻≺br≻
				Description:≺/br≻
				≺input type = 'text' id = 'description' name = 'description' placeholder = 'Description' value = '≺?php echo EscapeContent($row['Description']); ?≻'≻≺br≻
				Slug:≺/br≻
				≺input type = 'text' id = 'slug' name = 'slug' placeholder = 'Slug' value = '≺?php echo EscapeContent($row['Slug']); ?≻'≻≺br≻
				Type:≺/br≻
				≺select name = "typeid"≻≺?php
				foreach ($dictCategoryIDToDescription as $key =≻ $val) {
					if ((string)$key == $row['TypeID'])
					{
						echo '≺option selected value="'.$key.'"≻'.$val.'≺/option≻';
					}
					else
					{
						echo '≺option value="'.$key.'"≻'.$val.'≺/option≻';
					}
				}?≻
				≺/select≻≺br≻
				Content:≺br≻
				≺textarea class="ckeditor" id="myEditor" name="myEditor" cols="100" rows="20"≻≺?php echo EscapeContent($row['Content']); ?≻≺/textarea≻≺br≻
				≺input type = 'submit' value = 'Save Change' id='save_changes'≻
				≺input type = 'submit' value = 'Cancel' id='cancel_changes'≻
				≺input type='hidden' name='saveid' value='≺?php echo $iEditModeCategoryID ?≻'≻
			≺?php
		}
		else
		{
			?≻No data to edit.≺?php
		}
	}
?≻

≺/form≻

In the save form mode I also used the hidden field construction for storing the ID of the category that had been edited, in this case it was called 'saveid'. One additional feature in this case was that the type of category is queried from the CategoryType table. There is currently only two records 'Year' and 'Category' in this table but suppose one day I have an entire list then it makes sense to solve it like this already now.

At the beginning of the php page I adapted the routine that figures out what stage the page is loaded into.

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
	if (isset($_POST['changeid']) && $_POST['changeid'] != "")
	{
		$iEditModeIsOneAndBrowseModeIsZero= 1;
		$iEditModeCategoryID = $_POST['changeid'];
	}
	if (isset($_POST['saveid']) && $_POST['saveid'] != "")
	{
		$iEditModeCategoryID = $_POST['saveid'];
		$query = getquerywithdata(0, "UPDATE Category SET Slug = ?, Title = ?, Description = ?, TypeID = ?, Content = ? WHERE ID = ?",
										$_POST['slug'],
										$_POST['title'],
										$_POST['description'],
										(int)$_POST['typeid'],
										ParseContentEncodeImages($_POST['myEditor']),
										$iEditModeCategoryID);
		$result = $mysqli-≻query($query) or die("error query.." . mysqli_error($mysqli));
	}
}

If the 'saveid' was available in the POST array then that is detected and the routine for saving the data is executed.

At this point just for convenience I added the CategoryType to the table as well. The next bigger feature in this project is to create the 'Add' button.

First obviously I need to see the button before I can do anything with it. Added it to the browse rendering. Like so:

$strCatList .= "≺br≻≺input type = 'submit' value = 'Add'≻";

I add an event handler for the Add button click to set a changeid value that I can recognize and do something with.

$("input[value='Add']").click(function()
{
	$("input[name='changeid']").val('Add');
	$("input[name='deleteid']").val("");
});

So in case of clicking a change button the changeid is a number, the ID of that row. When clicking Add the changeid is 'Add'. That line with deleteid I will come back to shortly. As expected when clicking the button the page is reloaded and during the load of the page we can detect that a category should be added. Here at this point we get into a 'problem solving dilemma'. There are two ways to go from here:

1. One method is to create a new empty entry in the database. Then pretend as if the user had clicked change of that new entry and let the user edit it. If the user click save then the entry is saved as a regularly changed entry. The challenge comes if the user clicked cancel. In that case the new entry should be removed again.
2. Another method is to delay creating the new entry.  Then when the user clicked save then save the data. If the user clicked cancel then it is just a question of not entering the data.

If there is no cancel button to press then there is no problem! In that case option one is the simplest solution because I already created the functionality for the change mode. So I go for option 1.

if (isset($_POST['changeid']) && $_POST['changeid'] != "")
{
	$iEditModeIsOneAndBrowseModeIsZero = 1;
	$iEditModeCategoryID = $_POST['changeid'];
	if ($iEditModeCategoryID == 'Add')
	{
		$query = "INSERT INTO Category SET TypeID = 2, Slug = 'added'";
		$result = $mysqli-≻query($query) or die("error query.." . mysqli_error($mysqli));
		$iEditModeCategoryID = $mysqli-≻insert_id;
	}
}

Here above the Change detectection is augmented with the Add situation. We create an empty record and then let the change functionality get it and present the fields for editing it.

Now without any further ado lets move on to the delete functionality. When deleting a category we need first to delete all connections with the posts. When that is done we can delete the category itself. But first, we need a button for this. In the browse table.

$strCatList .= "≺td≻" . "≺input type = 'button' value = 'Delete' id='" . $row['ID'] . "'≻";

This is the similar to how the Change button was created but you notice this is a button and not a submit - button. There is a small difference. This button will not automatically trigger a form post. I need it to be like that so that I can cancel it.

Before I continue it is about time with a disclaimer. I really want to be minimalist about various libraries. What I do in the future I will find out then but right now I want almost no 'unnecessary' library loaded. So here we have a delete button but when clicking on it I want do display a dialog box. This could be a really good moment for loading another section of UI libraries into my blog engine but I am going to avoid that, I will make my own dialog box handling. And I am going to make it very simple.

Anyhow, after clicking on the delete and we go ahead with deleting I will use the same method as for change but I will have a separate hidden field for this. Like so:

$strCatList .= "≺input type='hidden' name='deleteid' value=''≻";

Look here it is, the deleteid field already mentioned in the Add button event handler. When the add button was clicked we cleared also the deleteid value just to be sure it was emtpy. So when the delete button is clicked what happens next? Here is the event handler for all the delete buttons:

$("input[value='Delete']").click(function(event)
{
	$("#confirmdelete span").text(this.id);
	$("#confirmdelete").show();
	$("input[value='Change']").attr("disabled","disabled");
	$("input[value='Delete']").attr("disabled","disabled");
	$("input[value='Add']").attr("disabled","disabled");
	$("input[name='deleteid']").val(this.id);
	$("input[name='changeid']").val("");
});

As you can see we are going to show a style block of the page with the ID confirmdelete. We set the ID of a span and then we disable all Change, Delete and Add buttons. Just to be sure in case the change was not empty we make it empty now. And finally we load the ID of the clicked Delete button into the deleteid field. The confirmdelete is defined like this:

#confirmdelete {
    display: none;
    position: absolute;
    left: 100px;
    top: 100px;
    width:350px;
    height:100px;
    text-align:center;
    z-index: 1000;
    background-color:#fff;
    border:2px solid #000;
}

Important to notice here is that this div is initially hidden, ie display: none. Here is how the div looks like:

≺div id="confirmdelete"≻
      ≺p≻Are you sure you want to delete this category?≺br≻(Category with ID: ≺span≻...≺/span≻)≺/p≻
	  ≺input type = 'submit' value = 'Yes' id='deleteyes'≻
	  ≺input type = 'submit' value = 'Cancel' id='deletecancel'≻
≺/div≻

This is how it looks like:

I placed the code of the confirmdelete dialog outside the form so even though they are submit buttons they will not submit the form. Just to confuse everybody. From here we have two options, Yes and Cancel.

In case the user clicks on the Cancel button then the following event handler will run:

$("#deletecancel").click(function(event)
{
	$("input[name='deleteid']").val("");
	$("input[name='changeid']").val("");
	$("#confirmdelete").hide();
	$("input[value='Change']").removeAttr("disabled");
	$("input[value='Delete']").removeAttr("disabled");
	$("input[value='Add']").removeAttr("disabled");
});

In this situation we just clear the deleteid value. Then we hide the dialogbox. Just to be sure we also clear the changeid in case it was set. Then we make all buttons enabled.

If the user clicked the Yes button in the dialog then this event handle will run:

$("#deleteyes").click(function(event)
{
	$("#confirmdelete").hide();
	$("input[name='changeid']").val("");
	$("#catsform").submit();
});

So with this I can pick up the form by ID and submit it. Such submit will trigger a reload of the page where 'deleteid' is set. Let's have a look at that:

if (isset($_POST['deleteid']) && $_POST['deleteid'] != "")
{
	$iEditModeCategoryID = $_POST['deleteid'];
	$query = getquerywithdata(0, "DELETE FROM PostCategory WHERE CategoryID = ?", $iEditModeCategoryID);
	$result = $mysqli-≻query($query) or die("error query.." . mysqli_error($mysqli));
	$query = getquerywithdata(0, "DELETE FROM Category WHERE ID = ?", $iEditModeCategoryID);
	$result = $mysqli-≻query($query) or die("error query.." . mysqli_error($mysqli));
	$_POST['deleteid'] = "";
	header("location: http://$domain/post/" . $_SESSION['post']); # We are done, leave the page.
}

So this is it. Actually this is an impressive little blogpost where I got a complete browse form paradigm database editing page running with only php and jquery. I started on this post on 21 March and finished it on 6 April.

In my next post I will look into improving the handling of images. I need to make it possible to add new images and I need to get rid of that annoying feature that you paste image data right into the post as a base64 encoded data block.

Until then, talk to you later!