Where to begin

July 27, 2004 at 3:30 pm Leave a comment

That’s often the question when writing a decent sized project… I already have my database schema created, otherwise I wouldn’t even be asking this question…

In this case, there are really two very seperate parts of this project.
1. The presentation to the end user. With categories and data in the database, one portion of the application should be the user interface.
2. Where does all the content come from? The ADMIN area, of course…

Since you really need to have data before you can display it, I decided that item 2 (above) is the place to start…

Priority #1: The ADMIN area must be secure… After all, who wants hackers logging in and changing your FAQs to FUs… An administrator user and password must be used to gain access, and each and every page must be secure. Since I was unsure of the best way to handle this, I turned to Harry… Harry who? Harry Fuecks, that’s who. He wrote the PHP Anthology (a 2 volume set), which gives good, solid solutions to common PHP problems. I’ll probably write a review later, but so far I’ve not even read half of it. Included is the excellent SPLIB, a good library of PHP classes. For example, Sessions and MySQL are easy to use in PHP, but Harry has classes for both. Why use classes? When a third class ties two others together (such as the Auth class), it makes things so nice… (I did have to apply my iisCompat.php include file to get Auth working, so there may yet be a bit of work in it, even with this set of books.)

Anyhow, with Authentication handled, I needed a menu…

Since I’m using an ASP based FAQ solution currently, I’ve decided to use the HTML menus that it comes with as a base.

With that easily handled, I set off to handle the Category section.

My categories are significantly different than the Categories of my ASP FAQ system. My ASP sysem uses flat categories, not even allowing one level of sub-categories. They are sorted alphabetically when displayed to the user. I want an unlimited number of sub-categories, and I want to choose how they will be displayed to the end user. (The sort order)…

To this end, my Category table includes these fields:

(Actually, there are a few more, but I’m not doing anything with that functionality yet, so we’ll leave them out of this, thank you.)

The ID field is an autonumber field which MySQL will control. The Name field is (gasp!) the Name of the category. The ParentID is the ID of the parent category (if this is a sub-category). Top level categories have a ParentID of 0. Finally, there is SortOrder. This field is an INT, and tells us the order (in a list) that this category will be displayed.

First up was adding a category… Done in very little time.

Next up was sorting… This took a bit of thought, but I finally came up with a good solution. I decided on “Up” and “Down” links in the table next to each category name. The Up and Down links were returning you to the same PHP page, but with an action of “swap” in the URI, and with ID1 and ID2 being the Category ID’s of the two categories that are having their sort orders swapped.

Next up? Editing a category… Almost too easy…

Finally, Removing a category.. Now, this was tricky. As Kevin Yank’s book (Build your own Database Driven Website using PHP and MySQL) pointed out, deleting data from a database is about the hardest part, at least when there are relationships to other tables.

In my case, there are relationships to other tables.

First, each FAQ question is going to be assigned to at least one category. Not too much trouble here… Since I’m using a lookup table, I’ll just delete all entries for the deleted category. I’ll need to point out to the Administrator that there are “unfiled” questions so they can manually file them, but that’s later.

Secondly, since I’m using the ParentID field, I need to set that to 0 for any categories who’s ParentID pointed to the deleted category. Easy enough.

Lastly, the SortOrder field… This is a tough one.. I actually thought on this a bit over the course of a few days before settling on this method as the best… But I still think there should be a better way. When a new category is added, the SortOrder field is set to the number of the new category, so it will be displayed last in the list. If a category is deleted, this would leave a gap if we do nothing, which only causes problems when future category changes take place. So, we must revisit EVERY category that has a SortOrder higher than the one of the deleted category, and decrement it by one.

Code to do this would look something like:

for ($x = $sortOrder+1; $x query($sql){
$sql = 'UPDATE category SET SortOrder = ' . ($x -1) . ' WHERE SortOrder = ' . $x . ';';
$result = $db=>query($sql);

Of course, after writing this, I sat back and thought: This is insane… There has got to be a better way… Issuing a separate SQL query for almost every category in the list is… well, too stupid for words. Honestly, for a small category table like this it wouldn’t be a big deal, but I needed to “think SQL” better… After a bit of thought and thumbing through the Advanced SQL chapter of Kevin Yank’s book, I came up with the “better way”. This query:

$sql = “UPDATE category SET SortOrder = SortOrder – 1 WHERE SortOrder > ” . $sortOrder;

Using that query statement, I only need a single Query and MySQL does all the heavy lifting.

So, that’s it for the category table manipulation… I would suppose that the questions table would be next…


Entry filed under: PHP.

PHP under IIS: Server Variables Optimization

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


July 2004
    Aug »

Most Recent Posts

%d bloggers like this: