Mark’s Site

I’ve been seriously lacking motivation to do anything except work recently, so I had a good think about my office setup, and I realised I enjoy working so much because my office rocks! But I think one of the main reasons for me liking my office so much is my new Logitech 2300 speaker system. Sound in here is just, well, awesome. And as you all know I do love my music, from classical through to rock and metal. So I thought to myself, why not move my kickass speakers under the house where the workout equipment is, then whenever I’m working and feel like I want to listen to music and chill out for a bit, instead of just putting on some tunes and surfing the web, I can go and work out while I listen to some good music through some really good speakers. I tried it for a first time today and I really enjoyed it. Jack Jack (my dog) was with me, and he loves any chance to be with Daddy while he’s working out (he gets right into the punching bag)

So that’s my strategy …making my workout area a more enjoyable place to be, so I WANT to be there, and not just for the workout, but also for the chance to chill out listening to my tunes.

I’ll try and follow up on this in a month and let you know how it’s been going. Hopefully I’ll have put on some inches on the arms by then

Current Setup

I got my new PC setup on a pretty slim budget about 6-12 months ago, that gave me a good motherboard (quad core compatible, 1333FSB, solid capacitors, all the trimmings), along with the lowest model Core 2 Duo processor, which at the time was an e4300 1.8Ghz, and it has served me very well.

I did get that particular CPU because everyone was raving about its overclocking abilities, but after numerous attempts I can’t seem to get things running well with any overclock. Things seem fine at 2.4Ghx, but them I go to start the machine the next day and it fails to turn the monitor on, or the fan will just rev up to max and nothing else happens. I end up having to reset the CMOS everytime, which as you can imagine is REALLY annoying. So this leads me to questions ….

1. Do I just forget overclocking and accept the e4300 as a decent core 2 duo at 1.8Ghz? (either I have a bad batch of e4300 for overclocking, or I suck at overclocking (but at such a small OC going to 2.4, I don’t see how I could really go wrong)
2. Do I look at going to Quad Core and finally getting a decent clock speed upwards of 2.4/2.6Ghz?

Enter Quad Cores

I’ve heard many great things about quad core, primarily the fact that they OWN dual cores (understandable why). My friend has a quad core desktop machine and a dual core laptop (that has a similar CPU to my e4300) and the quad beats it by 40 seconds in a super pi test over 1M, which is insanely good.

More questions …

Is my setup slow now? No, but I have noticed it slow down quite a few times under load when I am in full developer mode (which is most of the week). What is developer mode? I’ll have SQL Studio open, Visual Studio 2005 open and compiling every 10 minutes or so as I code … I’ll have a remote desktop connection going (or two - one to my local server, one to live), iTunes going in the background and several Firefox windows open, and sometimes an IE window open as well. And on other days, substitute SQL Studio and Visual Studio for Zend and two SSH windows. I’ll also have both MSN Chat and Skype open and Gmail open. You can understand why things would slow down, and why I am considering a good quad core investment.

Which leads me to think that I WILL get a Quad Core, sooner than later, but this leads to more interesting questions, such as which quad core to go with? Intel have released their new range now of Q9xxx quad cores which have the new 45nm process technology (better energy and processor power/technology), and various reviews show slightly better results over the Q6xxx range (as you’d expect of 1+ year of development), but this also means that massive price drops of the older Q6xxx series!

SO … do I pay more now for the new 45nm technology, which gives me slightly more power, better energy efficiency, or go with an older chip at a REALLY good price (like HALF the price of the new Q9xxx series)? I mean let’s face it, in another year, the ‘expensive’ quad core now will be replaced by yet another version, which will force IT to drop in price …do I just want to ignore all that and say, ‘All I want is a Quad Core, regardless of latest/greatest?’. If that’s the case, then the cheaper Q6600 is definitely the way to go. They haven’t yet, but should have a price drop to around the $220 mark (AU), which is definitely in my ‘happy’ price range. (unhappy price range starts at $260)

When you have your database diagram at a point where you can see all the relationships among your entities, it’s time to get a database schema output of your diagram. The MySQL WorkBench does a great job of creating your schema based on the diagram you create. MSSQL Management Studio is another product that is great at visual development, going from ER diagram, to creating your schema for you. There’s no reason why you can’t use MySQL’s WorkBench (because at time of writing it is free to use), but if you use a different product that does not automatically generate your schema for you, you’ll have to do the schema manually (which i highly recommend as it will teach you more about Sql), however that lies outside the scope of this article

Once you have your schema generated, the first thing you need to do is run it on a database to make sure the generation process was truly successful and no problems occur. If your application is hosted by a hosting company, they will have tools in place to let you login to your SQL server and run scripts like this. If not, find another hosting provider

If problems occur, you’ll need to debug your schema (cut parts out, see what is stopping it from running successfully) until you figure out the issues and fix them.

When your schema has run successfully, you are ready to start coding

Real world example

The database diagram you saw earlier of my website was very easy to convert to a SQL schema. Using MySQL’s WordBench software that helped to create the database diagram, I just exported to SQL script Makes live so much easier than writing it yourself, trust me, I’ve been there While writing SQL yourself is very important for your understanding of SQL, once you know your way around all the main syntax, why not let software do the job for you? And the beauty of this WorkBench software is you can SEE all of your tables as entities in a database diagram, and all of the relationships between tables, which, when writing SQL from scratch, can be difficult to get your head around at times, especially for large systems.

This was the output (pretty cool huh?)

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

-- -----------------------------------------------------
-- Table `person`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `person` ;

CREATE  TABLE IF NOT EXISTS `person` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `first_name` VARCHAR(255) NULL ,
  `last_name` VARCHAR(255) NULL ,
  `email_address` VARCHAR(255) NULL ,
  `phone_number` VARCHAR(255) NULL ,
  `mobile_number` VARCHAR(255) NULL ,
  `address` TEXT NULL ,
  `created_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NOT NULL ,
  PRIMARY KEY (`id`) )
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `role` ;

CREATE  TABLE IF NOT EXISTS `role` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `description` VARCHAR(255) NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `priority` INT NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_role_member (`created_by_member_id` ASC) ,
  INDEX fk_role_member1 (`modified_by_member_id` ASC) ,
  CONSTRAINT `fk_role_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_role_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `member`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `member` ;

CREATE  TABLE IF NOT EXISTS `member` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `person_id` INT UNSIGNED NULL ,
  `role_id` INT UNSIGNED NULL ,
  `login_name` VARCHAR(45) NULL ,
  `login_password` VARCHAR(45) NULL ,
  `bad_login_attempts` INT UNSIGNED NULL ,
  `bad_login_inactive_time_set` DATETIME NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_member_person (`person_id` ASC) ,
  INDEX fk_member_member (`created_by_member_id` ASC) ,
  INDEX fk_member_member1 (`modified_by_member_id` ASC) ,
  INDEX fk_member_role (`role_id` ASC) ,
  CONSTRAINT `fk_member_person`
    FOREIGN KEY (`person_id` )
    REFERENCES `person` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_member_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_member_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_member_role`
    FOREIGN KEY (`role_id` )
    REFERENCES `role` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `menu_item`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `menu_item` ;

CREATE  TABLE IF NOT EXISTS `menu_item` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `seo_name` VARCHAR(255) NULL ,
  `display_order` INT UNSIGNED NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_menu_item_member (`created_by_member_id` ASC) ,
  INDEX fk_menu_item_member1 (`modified_by_member_id` ASC) ,
  CONSTRAINT `fk_menu_item_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_menu_item_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `member_session`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `member_session` ;

CREATE  TABLE IF NOT EXISTS `member_session` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `login_time` DATETIME NULL ,
  `login_ip_address` VARCHAR(255) NULL ,
  `member_id` INT UNSIGNED NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_session_member (`member_id` ASC) ,
  CONSTRAINT `fk_session_member`
    FOREIGN KEY (`member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `policy_area`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `policy_area` ;

CREATE  TABLE IF NOT EXISTS `policy_area` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `created_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `permissions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `permissions` ;

CREATE  TABLE IF NOT EXISTS `permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `view_all_permission` VARCHAR(1) NULL ,
  `view_own_permission` VARCHAR(1) NULL ,
  `modify_permission` VARCHAR(1) NULL ,
  `delete_permission` VARCHAR(1) NULL ,
  `role_id` INT UNSIGNED NULL ,
  `policy_area_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_permissions_role (`role_id` ASC) ,
  INDEX fk_permissions_module (`policy_area_id` ASC) ,
  CONSTRAINT `fk_permissions_role`
    FOREIGN KEY (`role_id` )
    REFERENCES `role` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_permissions_module`
    FOREIGN KEY (`policy_area_id` )
    REFERENCES `policy_area` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `page_category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `page_category` ;

CREATE  TABLE IF NOT EXISTS `page_category` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `seo_name` VARCHAR(255) NULL ,
  `parent_page_category_id` INT UNSIGNED NULL ,
  `display_order` INT UNSIGNED NULL ,
  `menu_item_id` INT UNSIGNED NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_page_category_menu_item (`menu_item_id` ASC) ,
  INDEX fk_page_category_member (`created_by_member_id` ASC) ,
  INDEX fk_page_category_member1 (`modified_by_member_id` ASC) ,
  INDEX fk_page_category_page_category (`parent_page_category_id` ASC) ,
  CONSTRAINT `fk_page_category_menu_item`
    FOREIGN KEY (`menu_item_id` )
    REFERENCES `menu_item` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_category_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_category_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_category_page_category`
    FOREIGN KEY (`parent_page_category_id` )
    REFERENCES `page_category` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `page`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `page` ;

CREATE  TABLE IF NOT EXISTS `page` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `page_category_id` INT UNSIGNED NULL ,
  `role_id` INT UNSIGNED NULL ,
  `name` VARCHAR(255) NULL ,
  `seo_name` VARCHAR(255) NULL ,
  `summary` TEXT NULL ,
  `content` MEDIUMTEXT NULL ,
  `meta_keywords` VARCHAR(255) NULL ,
  `meta_description` VARCHAR(255) NULL ,
  `view_count` INT UNSIGNED NULL ,
  `display_order` INT UNSIGNED NULL ,
  `option_allow_comments` VARCHAR(1) NULL ,
  `option_show_on_front_page` VARCHAR(1) NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_page_page_category (`page_category_id` ASC) ,
  INDEX fk_page_member (`created_by_member_id` ASC) ,
  INDEX fk_page_member1 (`modified_by_member_id` ASC) ,
  INDEX fk_page_role (`role_id` ASC) ,
  CONSTRAINT `fk_page_page_category`
    FOREIGN KEY (`page_category_id` )
    REFERENCES `page_category` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_role`
    FOREIGN KEY (`role_id` )
    REFERENCES `role` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `page_comment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `page_comment` ;

CREATE  TABLE IF NOT EXISTS `page_comment` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `article_id` INT UNSIGNED NULL ,
  `comment` TEXT NULL ,
  `comment_made_by` VARCHAR(45) NULL ,
  `created_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_article_comment_article (`article_id` ASC) ,
  CONSTRAINT `fk_article_comment_article`
    FOREIGN KEY (`article_id` )
    REFERENCES `page` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `page_rating`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `page_rating` ;

CREATE  TABLE IF NOT EXISTS `page_rating` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `rating` INT NULL ,
  `article_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_article_rating_article (`article_id` ASC) ,
  CONSTRAINT `fk_article_rating_article`
    FOREIGN KEY (`article_id` )
    REFERENCES `page` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `document`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `document` ;

CREATE  TABLE IF NOT EXISTS `document` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `filename` VARCHAR(45) NULL ,
  `filepath` VARCHAR(45) NULL ,
  `filesize` VARCHAR(45) NULL ,
  `filetype` VARCHAR(45) NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_document_member (`created_by_member_id` ASC) ,
  INDEX fk_document_member1 (`modified_by_member_id` ASC) ,
  CONSTRAINT `fk_document_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_document_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `page_document_rel`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `page_document_rel` ;

CREATE  TABLE IF NOT EXISTS `page_document_rel` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `page_id` INT UNSIGNED NULL ,
  `document_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_page_document_rel_page (`page_id` ASC) ,
  INDEX fk_page_document_rel_document (`document_id` ASC) ,
  CONSTRAINT `fk_page_document_rel_page`
    FOREIGN KEY (`page_id` )
    REFERENCES `page` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_document_rel_document`
    FOREIGN KEY (`document_id` )
    REFERENCES `document` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

An ER diagrams (or database diagram) allows you to lay out your entire application’s database schema: the building blocks of your application. And we all know, with poor foundations, buildings crumble. The same is true for applications Put simply, the ER diagram describes the relationships between all the entities in your system.

In later articles, I’ll guide you through my CMSs entities in more detail (yes, the very application that all this is written in!). For now, just know that laying out your database entities in this way is the most important step to your application’s success. You need to have foresight of your application, put yourself in your user’s shoes, and imagine all the ’stuff’ they (and you) will want in terms of functionality.

For example: If your application uses Pages, will you want a page_content variable as well as a page_summary? How long will your database fields be for each table? Will you allow 40 characters or 255 characters for fields like titles and names? Will you want to record the Member who is modifying the Page separately from the Member who created the Page? Will you want to record the Member against the Page at all? What about date stamps for your entities? Do you want to use timestamps or actual datetime information? You will need to consider all of these things when designing your database diagram.

By far, the best tool to create your ER diagram is from MySQL, called MySQL WorkBench (at time of writing, it can be downloaded from here)

Real world example

With the above mentioned software, I created a database diagram for my website, with the relationships between everything, separated into the modules/layers that I wanted (in my case: Security, Members and Pages) I won’t go through it all, you can click on the image and look at all the relationships and fields yourself

Now while that looks pretty compact, that took me the best part of a week at 2-3 hours a night to get it finished. It was a matter of adding an entity, linking it to other tables where necessary, taking a step back and making sure I was doing the right thing in terms of my whole system. You need to be critical of you own application at this early stage. Think ahead by all means, imagine you as a user and as a systems administratory. What fields do all your database entities really need?

All programmers, beginners to advanced, encounter bugs on a daily basis. The secret to becoming a good programmer is testing your work thoroughly, so the bugs stop with you! We can group errors into various categories, regardless of what programming language we’re using. Once you understand the errors, you’ll be better equipped to fix them when they are encountered.

Syntax (Parse) Errors

Probably the most common error, the syntax error occurs when your code is syntactically incorrect. You’ve forgotten something that the compiler needs in order to understand the code you’ve written, and is unable to parse the code. A typical example with PHP is forgetting the semicolon at the end of each line. If you forget just one semicolon, you can bring an entire application to its knees.

Example (see line 3 for error):

<?php
$high=1000;
$low=12
echo $high.' '.$low;
?>

Other common syntax errors are forgetting opening or closing brackets of one or many if/while/for loops in your code.

Example (see line 4 for error):

<?php
$high=1000;
$low=12;
while($high > $low)
   echo $high;
   $high = $high-1;
}
?>

Semantic (Runtime) Errors

Semantic errorrs occur when PHP is expecting one type of variable (array, integer etc), but gets something different (like a string).

For example, you may have a loop that processes all elements of an array. If you pass an integer instead of an array, while your code may syntactically be correct (they are all just variables, so your code will pass the syntax rules), PHP is unable to run your request because it does not have the correct variables in the places it needs them, hence a semantic, or runtime error errors.

Environment Errors

If your PHP scripts rely on some other software/script, external to PHP, then it is always a good idea to write robust code that can inform you, or at least handle the situation when those external scripts are unavailable.

For example, your PHP scripts may rely on connecting to a MySql database in order to store information. What happens to your PHP scripting if that MySql database is suddenly unavailable? If you haven’t checked that the MySql database is available before you start doing ‘mysql’ stuff, then an environment error will occur.

Logic Errors

My favourite type of error, because the results of which are usually very bad Your PHP code is syntactically correct, but the results of your code are not what you expect because your logic is wrong.

For example, you may have a script that emails you whenever you change your account details, or change your password, but instead of just emailing you, your script unintentionally emails all users in your database.

Another logic error may occur with loops, when under certain conditions, your loop fails to complete, kicking off a never-ending loop that can bring your server to its knees, not just the application
Example (never-ending loop, because the variable $high never gets decreased):

<?php
$high=1000;
$low=12;
while($high > $low){
   echo $high;
}
?>

Some Advice

After all my years of coding, I still hit all these types of errors all the time. To become a good programmer you just have to keep all of these things in mind as you write your code, and eventually you will have less errors. But the key to becoming to good programmer is testing your work before releasing your code.

For example, if you have a PHP and MySql application, load up the MySql with 10s or 100s of records, then see how your script actually runs with a real data load. Step through every single screen when you test, and verify that each function works as expected. Try all types of user input you can imagine. Can you break your own application? If you can when testing, I guarantee a user will. You may think of this as mundane, but it really doesn’t take that long to test every single page, and when you get used to doing this, you’ll fix the bugs in your code straight away so that your application doesn’t come back from unsatisfied users when your code breaks.

How SQL Injection Occurs

The first thing you need to know about websites is you can NEVER trust that the user’s input will be as you expect. Most of you know this and design your forms to only allow certain input. You add both client-side and server-side validation to fields to ensure only integers are accepted on certain fields, emails are correct and so forth. However, while this ensure your application is robust, it does not ensure protection against injection attacks, because a hacker will examine your pages to find a field that allows input of any character (most likely, a general textarea control).

Once a hacker finds a page with one such field and finds that there’s no validation on it, it’s party time.

The Hack Occurs How?

Take for example, an application that has a contacts table and a users table. Our application is written to include a standard contact form, with two fields, email_address and message.

You should always have validation on the email_address field so that you get a valid email address to which you can later reply to. However, most programmers leave no validation whatsoever on the message field, and this is an entry for an attack.

Let’s say my query for the contact form’s input is as follows:

<?php
$var_strSql = "INSERT INTO contacts ( ";
$var_strSql .= "email_address ";
$var_strSql .= ",message) ";
$var_strSql .= "VALUES (";
$var_strSql .= "'".$var_email_address."' ";
$var_strSql .= ",'".$var_message."');";
?>

As there is no validation on the message field, let me give you an example of how an attack will occur (square brackets indicate start and end of input):

Example Input:

$var_email_address = [example@emailaddress.com]
$var_message = ['); DELETE FROM users;]

SQL will see/execute what?:

INSERT INTO contacts (
email_address
,message)
VALUES (
'example@emailaddress.com'
,''); DELETE FROM users;';

Consequence:
All information deleted from our users table

Assumptions:
This assumes our SQL permissions allow DELETE permissions, and from my experience they probably do, because other aspects of our systems require delete permissions, so most programmers tend to have one SQL user with the standard set of permissions: INSERT, UPDATE, SELECT and DELETE.)

The Bad News

For those of you who have stringent permissions setup and do not have DELETE permissions allowed, you are not safe because a hacker can still run whatever INSERT statements they want (and if they have UPDATE privilieges, they can corrupt your data), which can results in some even more disturbing news than a simple DELETE.

The Even More Bad News

Unfortunately, I need to inform you that a hacker does not just have the ability to write a few extra INSERT/DELETE/UPDATE statements in these situations. SQL is a programming language on its own, allowing SQL to be written to include loops, allowing hackers to input loops of INSERT statements, flooding your SQL table and consequently crashing your server.

For example:
Example Input:

var_email_address = [example@emailaddress.com]
var_message = [''; DECLARE @COUNT INTEGER;
SET @COUNT=1000;
WHILE @COUNT > 0
BEGIN
INSERT INTO contacts (email_address, message)
VALUES (''test'', ''some text'');
@COUNT = @COUNT - 1;
END]

SQL will see/execute what?:
SQL will execute this as 1000 INSERT statements.

Now imagine the effect on your server if we set the value of @Count to a hundred thousand Fun stuff huh?

The Good News

The good news is there are safeguards. You just need to be aware of them and then you can write secure code. If you think the answer is as simple as a single quote replacement function, think again The fact is hacking attempts can occur on plain integer fields where no quotes are present to BLOB/VARCHAR fields where character encoding can be used to represent a single quote differently to how humans would interpret it. To find out the answers, stay tuned.

Mark: Yay, the official document! We’re married. She is mine forever! *rubs hands together with evil look on face*

The Ceremony

Mark: Some other pics of the ceremony itself are below:

Mark: The Gang

Mark: The Siedles (+1)

Mark: The Loves

Mark: The Girls …one on the left is mine

Mark: The Money Shot

Where We Stayed

Mark: The place we stayed was called The Daintree Escape. See, here’s a pretty picture of it:

Mark: Yay

Mark: And look, a driveway with pretty trees

Sights

Mark: But now onto some of the cool sights from The Daintree at Cairns …

Mark: This was my favourite place. If I remember correctly, Casey and I scoped this place out first very early morning (after we dropped Dad off at golf), and I loved it so much we just had to go again with everybody because I was so excited about it. No photo can really do it justice. It’s just so beautiful an area, perfect for a skirmish match. It’d be like Far Cry but for real! That’d be awesome! If I was a game designer, I’d so do a FPS around this environment. AWESOME.

Mark: This was a really cool tree …the whole area just looked so green …us Southern Queenslanders just don’t get that sort of green down here. So I had to get a picture of it.

Mark: And how could we forget the hire car …Rego just had to take a picture so we wouldn’t forget it

Mark: The fruit tasting place. The big thing I remember about this was that we missed the Ice Cream tasting because we had to rush back for the boat ride and Rego wasn’t happy

Mark: The boat ride …so relaxing, for the first two hours …then I got very bored and just wanted to sleep, but then we tried to spot crocs, which was kinda cool … everyone seemed to enjoy it and the food was cool

Mark: The beach - was really pretty …you just don’t get beachside views where the cliffs come down the to coast like they do at Cairns. A big thanks to the Dad’s for photos, all turned out really well.

Just as functions are a way of grouping code that performs some specific purpose, classes are a way of grouping similar functions together. You can think of it as another step in keeping everything nicely organised

I find classes especially useful when working with databases in my web applications, so I’ll give you some examples along those lines.

What are classes?

In my world of organising data, I define the various ‘things’ in my system as objects. For example, an application may have ‘users’ and ‘products’. Consider these as two different objects, each of which require certain core functionality. Users may need to update their details, change their password, login. Products may just need their price updated, or other details. Each of these real world functions (update details, change password, update price etc) can be considered a core function of that object; something that needs to happen for this object to exist in your application. An object then, could be described as a combination of data and methods. And a way for us to define an object is by creating a class. The class is a template for an object. Following our examples, all we need to do is create a class, called ‘User’, and then create functions within this class for each of those real world functions. Then you have something that looks like this:

<?php
class User{
    var $id;
    var $name;
    var $password;
    var $details;

    function change_password($newpassword){
        #put code here to change user's password
    }

    function change_details($newdetails){
        #put code here to change user's details
    }
}
?>

Having written your class, to change a user’s details from within your application, you just need to call the class function, like so:

<?php
User::change_details('I am 26 years old and like chocolate.');
?>

Now that’s a very simple example, but you get the idea. It’s a good idea to get into the habit of using classes, because if your code ever does need to scale, or you become involved in a bigger project, classes will provide certain benefits, as we will further discuss.

An example data request when working with web databases:

• User asks for information from PHP page (Eg. Show me all products)
• PHP page asks PHP classes for data ( Eg. Get all products)
• PHP classes interact with database to securely get information from database (Eg. SELECT * FROM products)
• PHP classes give information back to PHP page
• PHP page returns information to user
• User marvels at the magic of the internet

Now we could, of course, just bypass the classes altogether and have the PHP page ask the database to get all information. So why use classes?

Why use classes?

Classes promote:

1. Less time for development. Once our classes are written, we can re-use those same functions over and over, and classes also encourage code re-use across different applications you develop. Need another application with Products functionality? Take the classes/database from that previous project and whack them straight into your new project, done!
2. Less code and less clutter in our application. Having classes takes the clutter out of our PHP pages, making them easier to read. This makes your life easier, as well as anyone else who may be reading/adding to your code in the future.

Classes solve the problem of scale with your code. The project can grow to massive proportions, but if you have your classes setup correctly, the transition is relatively painless compared to the alternative. Once you see a ‘classes’ folder in a web application, you know where all the logic is. You can then analyse the classes and get a feel for how an application works, and scale/modify it with ease.

Real world example

When your applications start to get larger in size, having a separation layer between interface logic and your database is a major bonus, as you can quickly find your way to all your SQL logic (you just have to look in your class files). This also means that if you decide to change to a different database engine, you can just make all the necessary modifications in your classes and the rest of the system will not be effected.

A great example of this happened to me very recently with this CMS. I wanted to know how many queries were actually happening behind the scenes in the creation of a page. Yes I had classes setup, but I’d taken it a step further (and this is something I recommend) and had a MySQL database class that all the OTHER classes talked to. Think of it as an interface to your actual database. Because I had classes that all talked to this main database class, all I had to do was add one global variable in my config called $NUM_QUERIES, then increment that variable in the SQL function that executes my SQL queries (one line change for my entire application in my MySQL class and config file!).

Suddenly, by the end of a page load I could output the $NUM_QUERIES variable and see that page1 had 66 queries. This was WAY too many queries, so I investigated and found I had a SQL query in some recursive logic (never a good idea), so I took SQL out of the loop and my number of queries went down to < 20 (which was more acceptable considering the security checks I have in place). So lessons learned, and very useful information for me as a systems administrator. Finding this performance problem only took about 10 minutes to implement across my entire application, all because I setup my system with classes

Once you’ve got those classes written, you can sit back and enjoy making the classes do all the hard work for you as you go about making screens that users will interact with in order to manage content. If you have well written and flexible classes, writing your application screens can be a real joy because the classes have simplified a lot of the hard work you would have had to do.

Example: Want a list of all the Pages in the system? Assuming your class function is written to return an array of Pages, and is called ‘db_select_all’, you can just call them as follows:

<?php
$page_array = Page::db_select_all();
//yay, now you have an array of all your pages that you can do stuff with ...how easy was that!
?>

What Exactly Is ‘Beyond’?

Writing an Interface

So you’ve got classes and a database, the next step you need to master is design. You need to create screens that your users can interact with in order to manage content, so knowing HTML (in the case of web applications) is pivotal. But don’t stress, HTML is easy to learn. If you can master TABLES, you can position things pretty much however you like, and learn some basic CSS (Cascading Style Sheets) and you’ll be able to pretty up those screens so users actually enjoy using your system

User Interface Design

Knowing HTML is only part of the equation. You need to have a good grounding in user interface design, which is how easy and intuitive the screen is for a user to manage. If you have an ‘all in one’ list and details screen, things can get crowded and confusing if you’re not careful. Where do you position those buttons to Save/Cancel? What do you call those buttons? What colours do I use for maximum effect? How do I lay out my form fields and labels? How do I structure a good menu system? All fun questions that you need to think long and hard on

Integration of PHP into HTML

Once you have some basic screen design experience, you need to learn to integrate PHP scripting into those designs, to actually make the screens function. There’s a lot of debate in industry as to whether this scripting should be kept completely separate from the design, or integrated. I’m an integrated fan, simply because I know HTML, CSS and PHP inside out so I don’t need to keep PHP separate from my HTML because I understand what everything’s doing. I strongly urge you to know that combination of technologies inside out also, it will give you a good edge when it comes to web applications. If, however, you are paying a designer to design your screens and they don’t know or want any PHP scripting interfering with their pretty designs, you need to use separate PHP response pages to handle all the form requests and use as little PHP scripting in the design pages as possible, not a fun task. If you learn HTML yourself, I think you’ll find the integrated method far more flexible.

When you have your database schema created, it’s time to move into coding mode

You can take one of two directions at this point. Classes or no classes. I’ll talk about both here.

The Classes Approach

I always use a very basic object oriented approach where possible and treat database entities as objects, which in turn become my classes. I say ‘basic’ because the world of OO is, imho, overly complex for many a web application and unnecessary, so I keep things simple and use classes purely as a wa of grouping my code, which I find allows for a very organised application

To continue, if, in my schema, I have page, page_category, member, I will create classes mapped to these entities, named Page, Page_Category and Member, and map all the database schema fields as class variables.

Your classes act as your pipeline to your database. If any database entities change, so do your classes. You can now understand the importance of taking your time with the database diagram, and making sure you’ve thought of everything you can, because once you start this step, you really don’t want to be changing your database. If you do you’ll just be creating more work for yourself.

Example: An example class structure for a database entity Page:

Arguments For

This structure, to me, is very easy to understand, whether I’ve been working on an application for years, or am brand new to the development team and want to find my way around. If I’m new, I just have to visit the classes to see how to interact with the database. Nice, simple and organised.

Arguments Against

Some programmers (stubborn fools imho) argue against the object oriented approach of programming and say that using classes is just overhead and takes more time to process. They argue that it offers no more organisation of your code, but I find this very hard to believe as I’ve tried not using classes in projects and I always find my application scales much more easily if I use classes (as it’s far easier to find things!). So my point is, anyone who argues against using classes is an unorganised git who will quickly get in over their head when their applications need to scale

The No Classes Approach

If you choose to take this path, at least be organised and create functions that do the ‘talking’ to the database entities. If you don’t, and you 1) change some database entities, or 2) get more programmers into your project, your application will take longer for new people to learn what’s going on, as your SQL code will be all over the place, and making changes will require first hand knowledge about where all SQL calls in your code are.

So let’s continue assuming you have groups of functions that do all your SQL work This just means you need to name your functions with care, so it’s easy to know what entities you are updating.

Example: I might create a function like so

When coding, I’ll know relatively easily what the name of my function is to run an INSERT statement to the page entity, provided of course that I have documentation in place to point me towards the right function (see, classes would make things easier to find)