QRdvark.com

Creating A PHP Application

Creating A PHP Application

Contents

  1. Abstract
  2. The Database
  3. User CRUD
  4. Create A User
  5. Verify User
  6. Login
  7. Log Out
  8. Blog CRUD
  9. Create A Category
  10. Delete A Category
  11. Create A Blog Entry
  12. Retrieve Blog Entries
  13. Update A Blog Entry
  14. Delete A Blog Entry
  15. Sod This, Just Give Me The Blog
  16. Credits

Abstract

In this introductory tutorial the process of building a complete php application is explored. The application is a simple blog and uses a MySQL database to store all the data. The blog application contains a user system for the adminstrator to post new blog entries. The final approval for each blog entry is handle by the administor. Assumed knowledge is basic use of HTML and CSS to render the display.

This tutorial will suit those looking to begin with PHP and have never written an entire application. No external resources will be used in the building of the application and all code will be generated here. This tutorial does not make use of Object Oriented code which may be more efficient.

Each section of the tutorial introduces new concepts from the PHP language that are used frequently in many PHP applications and websites. Many functions are used and explanations given for each as they are introduced.

The Database

As mentioned above, the blog data will be stored and retrieved from a MySQL database. PHP provides many simple to use MySQL functions for this purpose, but first, the database needs to be created. The command line below shows how to create the database from the command line.

mysqladmin create blog -u root -p

Following this the system will prompt the user for the root password and when given the new database, named blog, will be created. From there a username and password will need to be set for the blog application, after all, using root to interface to the database would be poor form.

mysql blog -u root -p

Once again, a password is required, and when given, the mysql prompt is ready to take commands. Here is the command to create a user for the database named blog.

GRANT ALL ON blog.* TO blog_master@localhost IDENTIFIED BY 'blog_password';

With the database successfully created, and a username and password in place exit the database and login again using the new username and password.

mysql blog -u blog_master -p

When prompted, type in the password for the blog_master.

The database tables are themselves quite basic. A table of blog categories, and a table of blog_content. The blog_category table need only contain the blog category id, and the blog category name.

The blog content table has a little extra. This table will contain the blog id, the blog category id, the blog headline, the blog text, and the blog date. The blog id will be an auto incremented value to avoid duplication and provide a primary key to ensure fast indexed lockup's. The blog category id will be a foreign key which references the blog category id in the blog category table. To this end, the tables must make use of the InnoDB table type.

The tables will look like this:


CREATE TABLE blog_users (
  blog_user_id int(11) NOT NULL auto_increment,
  blog_user_name varchar(20) NOT NULL,
  blog_user_password char(40) NOT NULL,
  blog_user_email varchar(254) NOT NULL,
  blog_user_access_level int(1) NOT NULL default 0,
  PRIMARY KEY (blog_user_id),
  UNIQUE KEY blog_username (blog_user_name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE blog_categories (
  blog_category_id int(11) NOT NULL AUTO_INCREMENT,
  blog_category_name varchar(50) NOT NULL,
  PRIMARY KEY (blog_category_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE blog_content (
  blog_content_id int(11) NOT NULL AUTO_INCREMENT,
  blog_category_id int(11) NOT NULL,
  blog_user_id int(11) NOT NULL,
  blog_content_headline varchar(50) NOT NULL,
  blog_content_text text NOT NULL,
  blog_content_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  blog_publish int(1) NOT NULL default 0,
  PRIMARY KEY (blog_content_id),
  FOREIGN KEY (blog_user_id) REFERENCES blog_users(blog_user_id) ON DELETE CASCADE,
  FOREIGN KEY (blog_category_id) REFERENCES blog_categories(blog_category_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Note in the blog_content table that the blog_category_id, and the blog_user_id fields are set as a foreign key and that they are set to ON DELETE CASCADE. This means if a blog category is deleted, all blog content that is related to it will also be deleted from the blog_content table, also, if a blog user is deleted, all blog entries by that user will be deleted. Be careful.

Copy and paste the database schema above into the command line to create the tables. Be careful if using a third party application such as phpmyadmin to talk to MySQL as many of these applications have poor support for foreign keys.

With the database set up complete, the first steps can be made to start blogging.

User CRUD

Before any blog entries can be added by users, the users must first be added to the system. An admin user will also be required to oversee, or moderate, the blog entries. The method of creating, retrieving, updating and deleting database entries is commonly referred to as CRUD

  • Create
  • Retrieve
  • Update
  • Delete

These a are the basic tasks needed to build the blog most PHP applications. Indeed, they are possibly the most commonly used tasks of all database driven applications and web sites, and hence, get their own acronym. But before any data can go into the database, a database connection needs to be made. This will be the first file and will be included in all files where a database connection is required.

Create a directory called includes in which to keep all the php scripts that are created for the blog. This will keep things tidy and easy for maintenance purposes. In the includes directory create a file called conn.php which will look like this:


<?php

/*** mysql hostname ***/
$hostname 'localhost';

/*** mysql username ***/
$username 'blog_master';

/*** mysql password ***/
$password 'blog_password';

/*** connect to the database ***/
$link = @mysql_connect($hostname$username$password);

/*** select the database ***/
$db mysql_select_db('blog'$link);

?>

In the beginning of the above script, several variables are set which can then be used with the mysql_connect() function to connect to the datbase. Once the database connection is established, the database can be selected with the mysql_select_db() function. The resource returned by the mysql_select_db() function is checked later in the script to see if a valid connection has been made and that the database is available.

If this file were to be accessed on its own, no data would be shown, not even if there was an error. The use of the @ symbol before mysql_connect() is used to suppress errors. This is rarely required and should be used only where appropriate. It is used in this instance to suppress the error as a check will soon be made on the validity of the $db link that follows.

Create A User

Now that a database connection can be made, users can be added to the system. Each user will have a user name, and password, and an access level. The access level will detirmine what files they have access to. Having regular users access the administration files would be disasterous, so care must be taken to ensure that users recieve only the amount of access they require.

Initially, the users will be confronted with a form which prompts for user name and password. When complete an email will be sent to the specified email address and the user will have 24 hours in which to verify the sign up or the entry will be deleted.

The process of verifying a user is quite simple. In the email that is sent to the user, the user id is sent in a link along with a randomly generated verification code. The user must access the link provided, and enter the verification code. Upon success, the user is logged in and can submit blog entries.

To avoid repitition of commonly used HTML components, a simple template can be made of the components and included with the PHP include language construct. As a language construct, include is not really a function, but is often thought of as a function to avoid confustion. Here, a header.php and footer.php file will be created with elements common to all HTML files in the system, such as the DOCTYPE, title, body etc.

Create a directory called includes and in the includes directory, create a template called header.php and in it put the following HTML.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>QRdvark.com BLOG</title>
</head>
<body>

The footer file contains even less and has only the closing body and html tags. Create a file in the includes directory named footer.php an it put the following HTML.

</body>
</html>

With these two files in place, the form to add users can be created. As the form is part of a CRUD system, it will server two purposes. To add users and then to edit users. This means saves duplication and saves coding time. It is a simple HTML form and will accept a user name and password.

The form itself is included in the adduser.php file which will be created next. The form introduces the PHP isset() function and the PHP ternary operator. The isset() function, as the name suggests, is used to check if a variable is set. The ternary operator is like a PHP if/else. So the following two snippets are the same.


<?php
    
/*** using if / else ***/
    
if(isset($variable))
    {
        echo 
$variable;
    }
    else
    {
        echo 
'';
    }

    
/*** using ternary ***/
    
echo isset($variable) ? $variable '';

The use of isset() and the ternary operator can make the form quite compact when checks need to be made if variables are set or not. The user_form.php will look like this.


<h2><?php echo isset($heading) ? $heading ''?></h2>
<form action="<?php echo isset($form_action) ? $form_action ''?>" method="post">
<dt><label for="blog_user_name">Username</label></dt>
<dd>
<input type="text" id="blog_user_name" name="blog_user_name" value="<?php echo isset($blog_user_name) ? $blog_username ''?>" maxlength="20" />
</dd>

<dt><label for="blog_password">Password</label></dt>
<dd>
<input type="password" id="blog_user_password" name="blog_user_password" value="" maxlength="20" />
</dd>

<dt><label for="blog_password2">Confirm Password</label></dt>
<dd>
<input type="password" id="blog_user_password2" name="blog_user_password2" value="" maxlength="20" />
</dd>

<dt><label for="blog_user_email">Email Address</label></dt>
<dd>
<input type="text" id="blog_user_email" name="blog_user_email" value="<?php echo isset($blog_user_email) ? $blog_user_email ''?>" maxlength="254" />
</dd>

<input type="hidden" name="form_token" value="<?php echo isset($form_token) ? $form_token ''?>" />
<dd>
<input type="submit" value="<?php echo isset($submit_value) ? $submit_value 'Submit'?>" />
</dd>
</dl>

When including the above form, several issues need to be resolved. The form variables need to be set, including the form token. The form token is set to ensure that the form that is being posted to the script, is in fact from the same server and not some malicious users form. The form token variable is destroyed when the form is successfully submited to avoid re-posting the form with the browser refresh button.

The form token is stored in a session. PHP sessions are a method of storing variables and making the available from page to page. A variable set on one page, can be accessed from other pages, quite a neat feature. When using sessions, the first line of a script must be session_start(). More information on sessions can be found in the Introduction To PHP Sessions tutorial.


<?php

    
/*** begin our session ***/
    
session_start();

    
/*** set a form token ***/
    
$form_token md5(rand(time(), true));

    
/*** set the session form token ***/
    
$_SESSION['form_token'] = $form_token;

    
/*** set the form action ***/
    
$form_action 'adduser_submit.php';

    
/*** set the form submit button value ***/
    
$submit_value 'Add User';

    
/*** include the header.php file ***/
    
include 'includes/header.php';

    
/*** include the user form ***/
    
include 'user_form.php'

    
/*** include the footer.php file  ***/
    
include 'includes/footer.php';
?>

When accessed with the browser the form is displayed with the relevant variables values filled in, such as the form action, the submit button value, and the form token. The form will post to a PHP script named adduser_submit.php. This script will check the values posted from the form, and if they are valid, put them into a database and then email the user with a verification code which will be used later to activate the account. Sounds easy right?

The adduser_submit.php script will look like this.


<?php
error_reporting
(E_ALL);

/*** begin session ***/
session_start();

/*** include the header file ***/
include 'includes/header.php'

/*** an array to hold errors ***/
$errors = array();

/*** check the form has been posted and the session variable is set ***/
if(!isset($_SESSION['form_token']))
{
    
$errors[] = 'Invalid Form Token';
}
/*** check all fields have been posted ***/
elseif(!isset($_POST['form_token'], $_POST['blog_user_name'], $_POST['blog_user_password'], $_POST['blog_user_password2'], $_POST['blog_user_email']))
{
    
$errors[] = 'All fields must be completed';
}
/*** check the form token is valid ***/
elseif($_SESSION['form_token'] != $_POST['form_token'])
{
    
$errors[] = 'You may only post once';
}
/*** check the length of the user name ***/
elseif(strlen($_POST['blog_user_name']) < || strlen($_POST['blog_user_name']) > 25)
{
    
$errors[] = 'Invalid User Name';
}
/*** check the length of the password ***/
elseif(strlen($_POST['blog_user_password']) <= || strlen($_POST['blog_user_password']) > 25)
{
    
$errors[] = 'Invalid Password';
}
/*** check the length of the users email ***/
elseif(strlen($_POST['blog_user_email']) < || strlen($_POST['blog_user_email']) > 254)
{
    
$errors[] = 'Invalid Email';
}
/*** check for email valid email address ***/
elseif(!preg_match("/^\S+@[\w\d.-]{2,}\.[\w]{2,6}$/iU"$_POST['blog_user_email']))
{
    
$errors[] = 'Email Invalid';
}
else
{
    
/*** escape all vars for database use ***/
    
$blog_user_name mysql_real_escape_string($_POST['blog_user_name']);
    
/*** encrypt the password ***/
    
$blog_user_password sha1($_POST['blog_user_password']);
    
$blog_user_password mysql_real_escape_string($blog_user_password);
    
/*** strip injection chars from email ***/
    
$blog_user_email =  preg_replace'((?:\n|\r|\t|%0A|%0D|%08|%09)+)i' ''$_POST['blog_user_email'] );
    
$blog_user_email mysql_real_escape_string($blog_user_email);

    
/*** if we are here, include the db connection ***/
    
include 'includes/conn.php';

    
/*** test for db connection ***/
    
if($db)
    {
        
/*** check for existing username and email ***/
        
$sql "SELECT
            blog_user_name,
            blog_user_email
            FROM
            blog_users
            WHERE
            blog_user_name = '
{$blog_user_name}'
            OR
            blog_user_email = '
{$blog_user_email}'";
        
$result mysql_query($sql);
        
$row mysql_fetch_row($result);
        if(
$row[0] == $blog_user_name)
        {
            
$errors[] = 'User name is already in use';
        }
        elseif(
$row[1] == $blog_user_email)
        {
            
$errors[] = 'Email address already subscribed';
        }
        else
        {

            
/*** create a verification code ***/
            
$verification_code uniqid();

            
/*** the sql query ***/
            
$sql "INSERT
                INTO
                blog_users(
                blog_user_name,
                blog_user_password,
                blog_user_email,
                blog_user_access_level,
                blog_user_status)
                VALUES (
                '
{$blog_user_name}',
                '
{$blog_user_password}',
                '
{$blog_user_email}',
                1,
                '
{$verification_code}')";

            
/*** run the query ***/
            
if(mysql_query($sql))
            {
                
/*** unset the session token ***/
                
unset($_SESSION[\form_token']);

                /*** email subject ***/
                $subject = '
Verification code';

                /*** email from ***/
                $from = '
test@QRdvark.org';

                /*** the message ***/
                $path = dirname($_SERVER['
REQUEST_URI']);
                $message = "Click the link below to verify your subscription\n\n";
                $message .= '
http://'.$_SERVER['HTTP_HOST'].$path.'/verify.php?vc='.$verification_code;

                /*** set some headers ***/
                
$headers 'From: webmaster@example.com' "\r\n" .
                
'Reply-To: webmaster@example.com' "\r\n" .
                
'X-Mailer: QRdvark MAIL';

                
/*** send the email ***/
                
if(!mail($blog_user_email$subject$message$headers))
                {
                    
$errors 'Unable to send verification';
                }

                
/*** unset the form token ***/
                
unset($_SESSION['form_token']);
            }
            else
            {
                
$errors[] = 'User Not Added';
            }
        }
    }
    else
    {
        
$errors[] = 'Unable to process form';
    }
}

/*** check if there are any errors in the errors array ***/
if(sizeof($errors) > 0)
{
    foreach(
$errors as $err)
    {
        echo 
$err,'<br />';
    }
}
else
{
    echo 
'Sign up complete<br />';
    echo 
'A verification email has been sent to '.$blog_user_email;
}

/*** include the footer file ***/
include 'includes/footer.php';

?>

The structure of the above script takes the form of an if/elseif/else ladder. This allows the code execution to step through a variety of checks before the operational part of the script is executed. The checks are many and provide the minimum security checking needed to secure a PHP application.

Stepping down the ladder, the first uses isset() to check that the form token has been sent, this ensures the form that POSTed the data is not a hackup on a third party machine. The second check also uses the isset() function, but this time checks four variables in one swoop. This feature of the isset() function makes the if/elseif/else ladder much shorter and saves time on coding mulitple isset()'s for each POST variable.

A check is then performed on the form token to be sure that the token POSTed matches the token stored in the SESSION. If the tokens do not match and error is generated. From there variables are checked for length. This is important as a malicious user may try to inject variables with lengths shorter or longer than the length expected, or the length permitted by the database. This would cause an error that may give away internal information about your system.

A check is made on the validity of the email as well as on the length. There is no regular expression known to man that will successfully match all valid emails per the RFC. This one will catch all sane email address.

If success, the script then prepares for use with the datbase. It is imperative that any variables that are to be used in an SQL statement are proplerly escaped to prevent SQL injection. It is at this time, the users email address is sanitized also, not only for SQL injection, but for email header injection also. All variables that are to be used in emails should be proplerly sanitized to prevent header injection, which is a leading cause of SPAM. A further check is made to ensure the username, or the email address is not already is not already in use. If it is already in use, an error is generated. If all is well, the new user data is added to the database and and email is sent to the user with a verification code.

Verify User

When a new user is created as above, an email is sent to the users email address with a verification code. The URL in the link contains a section like this:
verify.php?vc=48ba88c9efeef
This means the file verify.php will have access to the verification code via PHP super global named $_GET['vc'].


<?php
        
/*** check if verification code is present ***/
    
if(!isset($_GET['vc']))
    {
        
$error 'Invalid Verification Code';
    }
        
/*** check verification code is a string of 13 chars ***/
    
elseif(strlen($_GET['vc']) != 13)
    {
        
$error 'Verification Code Invalid';
    }
    else
    {
        
/*** escape the code ***/
        
$blog_verification_code mysql_real_escape_string($_GET['vc']);

        
/*** include the database connection ***/
        
include 'includes/conn.php';

        
/*** check for a valid connection ***/
        
if($db)
        {
            
/*** the update SQL ***/
            
$sql "UPDATE
                blog_users
                SET
                blog_user_status=1
                WHERE
                blog_user_status='
{$blog_verification_code}'";

            
/*** run the query ***/
            
$result mysql_query($sql);

                        
/*** check for affected rows ***/
            
if(mysql_affected_rows($link) != 1)
            {
                
$message 'Unable to verify';
            }
            else
            {
                
$message 'Verification Complete, please log in to submit blog';
            }    
        }
    }
?>
<h1>Verification</h1>

<p><?php echo $message?></p>

The verify.php file itself is quite simple in comparison to the previous file. Once again the isset() function is used, this time to check that the verification code is present in $_GET['vc']. Then the length of the string is check and if all is well, the verification code is prepared for use in the database using mysql_real_escape_string().

The SQL query is run and updates the user status of the user with the matching verification code. By using the mysql_affected_rows() function it is possible to check if the SQL query was successful.

Login

Now that a user can be created and register with the system, it is possible for the user to log into the system. But first, a method of navigation is required. The navigation menu will be common to all pages, so can be part of the header. This means only a single copy is every needed and saves code duplication.

The new includes/header.php file will look like this.


<?php
        
/*** if a user is logged in ***/
        
if(isset($_SESSION['access_level']))
        {
                
$log_link 'logout.php';
                
$log_link_name 'Log Out';
        }
        else
        {
                
$log_link 'login.php';
                
$log_link_name 'Log In';
        }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>QRdvark.com BLOG</title>
<style type="text/css">
.menu ul{
        color: green;
        list-style: none;
}
.menu ul li{
        padding:
        display: inline;
        float: left;
        padding: 2px 8px;
}

hr{
        clear: both;
}

</style>
</head>
<body>
<div class="menu">
<ul>
<li><a href="index.php">Home</a></li>
<li><a href="<?php echo $log_link?>"><?php echo $log_link_name?></a></li>
</ul>
<hr />

The first change that is noticable in the header.php file is that the first block of code at the top contains a check if the SESSION variable named access_level is set. If a user is logged in, the value of the link is set to logout. If the user is not logged in, the values are set to provide a link to log in. The menu is very basic and provides a small CSS unordered link list.

The login form is basic also, and will POST to a login_submit.php file, which will do some checking and attempt to log th e user into the sytem.

The login form will look like this.


<?php 
        
/*** start the session ***/
        
session_start();

        
/*** include the header file ***/
        
include 'includes/header.php';

        
/*** set a form token ***/
        
$_SESSION['form_token'] = md5(rand(time(), true));
?>

<h1>Blog Login</h1>
<p>
Please supply your username and password.
</p>
<form action="login_submit.php" method="post">
<input type="hidden" name="form_token" value="<?php echo $form_token?>" />
<dl>
<dt>Username</dt>
<dd><input type="text" name="blog_user_name" /></dd>

<dt>Password</dt>
<dd><input type="password" name="blog_user_password" /></dd>
<dd><input type="submit" value="Login" /></dd>
</dl>
</form>

<?php include 'includes/footer.php'?>

No surprises in the login form. It contains the obligatory session_start() function that must be used any time an interaction with session variables is required. The form token is set and the header file is included. The form is simple and contains the form token and inputs for username and password, followed by the inclusion of the footer.php file.

When the form is submitted, something different happens. The form token is checked, the username and passwords are validated and sanitized, but instead of displaying a message, the script will redirect the user to the index.php page, which will be created now.

The index.php file will look like this.


<?php include 'includes/header.php'?>

<h1>QRdvark.com Blog</h1>
<p>
Welcome to the QRdvark.com Blog...
</p>

<?php include 'includes/footer.php'?>

The index file will grow quite substantially further on as blog entries need to be retrieved, but for now, this is all that is required.

The login_submit.php file will look like this.


<?php

    
/*** begin output buffering ***/
    
ob_start();

    
/*** begin session ***/
    
session_start();

    
/*** check the form has been posted and the session variable is set ***/
    
if(!isset($_SESSION['form_token']))
    {
        
$location 'login.php';
    }
    
/*** check all fields have been posted ***/
    
elseif(!isset($_POST['form_token'], $_POST['blog_user_name'], $_POST['blog_user_password']))
    {
        
$location 'login.php';
    }
    
/*** check the form token is valid ***/
    
elseif($_SESSION['form_token'] != $_POST['form_token'])
    {
        
$location 'login.php';
    }
    
/*** check the length of the user name ***/
    
elseif(strlen($_POST['blog_user_name']) < || strlen($_POST['blog_user_name']) > 25)
    {
        
$location 'login.php';
    }
    
/*** check the length of the password ***/
    
elseif(strlen($_POST['blog_user_password']) < || strlen($_POST['blog_user_password']) > 25)
    {
        
$location 'login.php';
    }
    else
    {
        
/*** escape all vars for database use ***/
        
$blog_user_name mysql_real_escape_string($_POST['blog_user_name']);

        
/*** encrypt the password ***/
        
$blog_user_password sha1($_POST['blog_user_password']);
        
$blog_user_password mysql_real_escape_string($blog_user_password);

        
/*** if we are here, include the db connection ***/
        
include 'includes/conn.php';

        
/*** test for db connection ***/
        
if($db)
        {
            
/*** check for existing username and password ***/
            
$sql "SELECT
            blog_user_name,
            blog_user_password,
            blog_user_access_level
            FROM
            blog_users
            WHERE
            blog_user_name = '
{$blog_user_name}'
            AND
            blog_user_password = '
{$blog_user_password}'
            AND
            blog_user_status=1"
;
            
$result mysql_query($sql);
            if(
mysql_num_rows($result) != 1)
            {
                
$location 'login.php';
            }
            else
            {
                
/*** fetch result row ***/
                
$row mysql_fetch_row($result);

                
/*** set the access level ***/
                
$_SESSION['access_level'] = $row[2];

                
/*** unset the form token ***/
                
unset($_SESSION['form_token']);

                
/*** send user to index page ***/
                
$location 'index.php';
            }
        }
    }

    
/*** redirect ***/
    
header("Location: $location");

    
/*** flush the buffer ***/
    
ob_end_flush();

?>

This time, when the form is submitted, the user is redirected the index.php page if the login is successful. If the login fails, the user is redirected back to the login page to try again. The first line of code begins the output buffering which allows PHP to no send any headers to the browser, which would cause an error as headers are sent when using session_start and again when using the header() function.

The structure maintains the if/elseif/else ladder and traverses through some of the checks used earlier to validate and sanitize the user inputs. At the end of the script, the header() function redirects and finally, the ob_end_flush() function sends the whole thing to the browser.

Note that the Login link in the menu changes to Log Out when a user logs in.

Log Out

Now that a user can log in to the system, a method is required to log out. As the link for the log out is already in the menu, it is simply a matter of creating the logout.php file. This file only needs to check that the access_level SESSION variable is set, and if it is, unset() it, and then redirect the user back to the index.php page.

The logout.php file will look like this.


<?php

        
/*** begin output buffering ***/
        
ob_start();

        
/*** begin session ***/
        
session_start();

        
/*** check the form has been posted and the session variable is set ***/
        
if(isset($_SESSION['access_level']))
        {
                unset(
$_SESSION['access_level']);
        }

        
/*** redirect ***/
        
header("Location: index.php");

        
/*** flush the buffer ***/
        
ob_end_flush();
?>




Blog CRUD

The blog CRUD, as the name suggest comprises four components.

  • Create
  • Retrieve
  • Update
  • Delete

To Create or add a blog entry, there must first be some categories to add them to.

Create A Category

Before data can be retrieved from the database, it must first be put in. The most common way of user input into a database with PHP is with a HTML form. Because a form is a client side interface, the door is left open to abuse as the content the end user puts into the form, may not be the type of content expected or, if a malicious users wants to get nasty, may to to compromise the system. It is the job of the PHP developer to close these security doors and the basic lesson is this:

NEVER TRUST USER INPUT

NEVER TRUST USER INPUT

NEVER TRUST USER INPUT

As this blog is a multi-user system, precautions must be taken to avoid mistakes and mis-use. The form itself is quite simple and is simply HTML.

Creating categories is a function of the administrator, and so the script to add a category must only be availble to the admin user. The user access level for the adminstrator is 5. This can be checked as the access level is stored in a session, so when the admin lands on the add_category.php page, access can be checked, and if the access level is not correct, the user will be redirected to the index.php page.

Create a file named add_category.php in the main directory, as we are going to add a category to the blog. The add_category.php file in will look like this:


<?php

    
/*** begin output buffering ***/
    
ob_start();

    
/*** include the header file ***/
    
include 'includes/header.php';

    
/*** check access level ***/
    
if(!isset($_SESSION['access_level']) || $_SESSION['access_level'] != 5)
    {
        
header("Location: index.php");
        exit;
    }
    else
    {
        
/*** set a token ***/
        
$form_token uniqid();
        
$_SESSION['form_token'] = $form_token;
    }
?>

<h3>Add Category</h3>
<p>
Category names must contain only alpha numeric characters and underscore, space or comma.
</p>
<form action="add_category_submit.php" method="post">
<input type="hidden" name="form_token" value="<?php echo $form_token?>" />
<input type="text" name="blog_category_name" />
<input type="submit" value="Add Category" />
</form>

<?php
    
include 'includes/footer.php';
    
ob_end_flush();
?>

This file makes use of output buffering to manage the headers. Headers are send from the included header.php file with session_start() and this would produce a warning. The script checks that the user who is accessing the page is an administrator by checking first that the access_level variable is set and that its value is 5. If this is not true, the user is forward off to the index page gracefully. I the access is from the administrator, then the form is shown in the page.

The file is a simple HTML form that contains the required input text field to add a category. Note that the name of the input matches the name in the database. This is not strictly required but gives clarity when dealing with variables.

The form action is the add_category_submit.php file. This is the file that will process the form data, and if all is well, will add the category to the database. To achieve this several things need to happen.

  • The form field must contain a valid name
  • The connection must be made to the database
  • The category must be added
  • A response is required

As the category name must be a string containing less than 50 characters, because the database field is defined as VARCHAR(50), it is simple to validate that this is what has been supplied by the form. The database connection is made by simply includeing the conn.php file. When a file is included with PHP, it is the same as if the code were written where the include occurs. Following this, the category is added to the database and a thank you message created. The add_category_submit.php file will look like this.


<?php
    error_reporting
(E_ALL);

        
/*** begin output buffering ***/
        
ob_start();

        
/*** include the header file ***/
        
include 'includes/header.php';

        
/*** check access level ***/
        
if(!isset($_SESSION['access_level']) || $_SESSION['access_level'] != 5)
        {
                
header("Location: index.php");
                exit;
        }
        else
        {
        
/*** check the form has been posted and the session variable is set ***/
        
if(isset($_SESSION['form_token'], $_POST['form_token'], $_POST['blog_category_name']) && preg_match('/^[a-z][a-z\d_ ,]{2,49}$/i'$_POST['blog_category_name']) !== 0)
        {
            
/*** if we are here, include the db connection ***/
            
include 'includes/conn.php';

            
/*** test for db connection ***/
            
if($db)
            {
                
/*** excape the string ***/
                
$blog_category_name mysql_real_escape_string($_POST['blog_category_name']);

                
/*** the sql query ***/
                
$sql "INSERT INTO blog_categories (blog_category_name) VALUES ('{$blog_category_name}')";

                
/*** run the query ***/
                
if(mysql_query($sql))
                {
                    
/*** unset the session token ***/
                    
unset($_SESSION['form_token']);

                    echo 
'Category Added';
                }
                else
                {
                    echo 
'Category Not Added';
                }
            }
            else
            {
                echo 
'Unable to process form';
            }
        }
        else
        {
            echo 
'Invalid Submission';
        }
    }

    
/*** flush the buffer ***/
    
ob_end_flush();
?>

It is quite clear from the above code, that ninety percent of the code is dedicated to error checking. This is perhaps the most important part of dealing with input from users. Note that once again, the blog_category_name variable matches the name in the database, and that the naming convention is maintained throughout the script.

The use of the preg_match() function contains several validation tasks rolled into a single function. Regular expressions are an excellent tool for validating user input to ensure what is posted from a form, is what expected when processing the data. This regular expression allows the use of alpha numeric characters and underscore, space and comma. This permits a good variety of characters to use for any sane category name.

The form and form submit now work as expected and categories can be added easily, A form token has been used to ensure teh validity of the form and to prevent page refresh. The form token can be stored in a session with the add_category.php form, and then checked in the add_category_submit.php script, and if the POST is successful, the token is deleted, thus preventing further POSTings of the form.

Delete A Category

The process of deleting a category is very similar to that of adding a category, with a few small changes the code is mostly the same. The form maintains the session token and the only difference is that the category names and id's are extracted from the database to be used in the form with a drop-down select.

The process of gathering the information for the form is kept at the top of the script. This means all the application logic is separated from the display logic. This will be important as scripts, and indeed, applications, become larger. The form script itself looks like this.


<?php
error_reporting
(E_ALL);

    
/*** begin output buffering ***/
    
ob_start();

    
/*** include the header file ***/
    
include 'includes/header.php';

    
/*** check access level ***/
    
if(!isset($_SESSION['access_level']) || $_SESSION['access_level'] != 5)
    {
        
header("Location: index.php");
        exit;
    }
    else
    {
        
/*** set a token ***/
        
$form_token uniqid();
        
$_SESSION['form_token'] = $form_token;

        
/*** include the database connection ***/
        
include 'includes/conn.php';

        
/*** check for a valid db connection ***/
        
if($db)
        {
            
/*** sql to get all the blog categories ***/
            
$sql "SELECT
                blog_category_id,
                blog_category_name
                FROM
                blog_categories"
;

            
$result mysql_query($sql);
            if(!
is_resource($result))
            {
                echo 
'Unable to get category listing';
            }
            else
            {
                
/*** create an empty array ***/
                
$categories = array();

                
/*** loop over the results and add them to the array ***/
                
while($row mysql_fetch_array($result))
                {
                    
$categories[$row['blog_category_id']] = $row['blog_category_name'];
                }
            }
        }
        else
        {
            echo 
'Database connection failed';
        }
    }
        
?>

<h3>Delete Category</h3>
<p>
<?php
    
if(sizeof($categories == 0))
    {
        echo 
'No Categories Available';
    }
    else
    {
        echo 
'Select a category name for deletion';
    }
?>
</p>
<form action="del_category_submit.php" method="post">
<select name="blog_category_id">
<?php
    
foreach($categories as $id=>$cat)
    {
        echo 
"<option value=\"$id\">$cat</option>\n";
    }
?>
</select>
<input type="submit" value="Delete Category" onclick="return confirm('Are you sure?')"/>
</form>

<?php include 'includes/footer.php'?>

Note the delete button has a small javascript onclick added which pops up a confirm window asking the users to confirm the delete when the button is pressed.

Moving to the includes/del_category_submit.php file, once again there is many similarities with the add_category_submit.php file. The same process is involved with small changes to the error checking to validate that the blog_category_id is indeed a number. The inclusion of the database connection class and connection resource is same and only the SQL query changes. A small addition when the deletion is completed is the mysql_affected_rows() line which returns the number of affected rows from the previous mysql operation. This can be used to display the number of rows deleted which should be one.

The del_category_submit.php file looks like this.


<?php

        
/*** begin output buffering ***/
    
ob_start();

    
/*** include the header file ***/
    
include 'includes/header.php';

    
/*** check access level ***/
    
if(!isset($_SESSION['access_level']) || $_SESSION['access_level'] != 5)
    {
        
header("Location: index.php");
        exit;
    }
    else
    {
        
/*** check the form has been posted and the session variable is set ***/
        
if(isset($_SESSION['form_token'], $_POST['blog_category_id']) && is_numeric($_POST['blog_category_id']))
        {

            
/*** if we are here, include the db connection ***/
            
include 'includes/conn.php';

            
/*** test for db connection ***/
            
if($db)
            {
                
/*** excape the string ***/
                
$blog_category_id mysql_real_escape_string($_POST['blog_category_id']);

                
/*** the sql query ***/
                
$sql "DELETE FROM blog_categories WHERE blog_category_id = $blog_category_id";

                
/*** run the query ***/
                
if(mysql_query($sql))
                {
                    
/*** unset the session token ***/
                    
unset($_SESSION['form_token']);

                    
/*** affected rows ***/
                    
$affected mysql_affected_rows($link);

                    echo 
"$affected Category Deleted";
                }
                else
                {
                    echo 
'Category Not Deleted';
                }
            }
            else
            {
                echo 
'Unable to process form';
            }
        }
        else
        {
            echo 
'Invalid Submission';
        }
    }
?>

Once again the code shows mostly error checking, this is the life of the PHP programmer. The task is not one of how make things right, but of how to stop things going wrong. There are many aspects of simple scripts which need to be dealt with that are often overlooked. By attending to the smaller details, PHP scripts and applications are robust and easy to maintain.

Add several categories such as Computers, Programming and Cars. These will be the categories used as the tutorial progresses.

Create A Blog Entry

With the categories now available, the real work of CRUD can begin. Of course, the beginning is the creation of a blog entry. Once again a form will be used to enter the data but this form will not be as simple as the previous. A little forward thinking is needed as a blog entry may need to be edited to correct some aspect of the entry. The form fields will be identical, and it would be poor practice to duplicate the form and use separate forms for both add and editing a blog entry.

Like any other piece of reusable code, the form can be included in a php script. The main difference with a reusable piece of HTML code is that any PHP variables within the code must be available to it. Also, the form action will be directed to two different scripts, so this too must be a variable set in the parent script, that is, the script that includes the form.

The includes/blog_form.php file, which contains the form, will look like this


<h3><?php echo $blog_heading?></h3>

<form action="<?php echo $blog_form_action?>" method="post">
<input type="hidden" name="blog_content_id" value="<?php echo isset($blog_content_id) ? $blog_content_id ''?>" />
<dl>
<dt>Headline</dt>
<dd><input type="text" name="blog_content_headline" value="<?php echo isset($blog_content_headline) ? $blog_content_headline ''?>"/></dd>

<dt>Category</dt>
<dd>
<select name="blog_category_id">
<?php
        
foreach($categories as $id=>$cat)
        {
                echo 
"<option value=\"$id\"";
                
/*** mark as selected ***/
                
echo (isset($selected) && $id==$selected) ? ' selected' '';
                echo 
">$cat</option>\n";
        }
?>
</select>
<dd>

<dt>Blog</dt>
<dd>
<textarea name="blog_content_text" rows="5" cols="45"><?php echo isset($blog_content_text) ? $blog_content_text ''?></textarea>
</dd>

<dd><input type="submit" value="<?php echo $blog_form_submit_value?>" /></dd>
</dl>
</form>

The form is much the same as the add_category form from used earlier, with the exception that this form has more fields. The category drop-down menu is identical to that of the add category form and a text input field is supplied for the heading along with a textarea for the blog_content_text. Once again the field names are identical to those in the database table that the form is designed for.

The form also contains several pieces of PHP code in the inputs to echo the value of form field if they are set. The form action is set with a variable along with the value of the submit button. These variables make the form re-usable later when updating blog data.

The add_blog.php file that will include the form performs several functions. Like the add category form a session is started at the top of the script and then the session token is set. The header.php file is included as is the conn.php file to connect to the database. Once again, a check is made that a valid connection has been made to the database, and if this is ok, a further check is made to be sure a category is available. If all is well, the categories are put into an array named $categories with the category_id as the index. I is this array that is passed to the form to produce the category drop-down menu.

Following from there, the variables that are in the form are set. If one of them is omitted, an error will be produced, so it is important to make sure all variables have a value, even if the value is an empty string.

The add_blog.php file will look like this.


<?php

    
/*** begin output buffering ***/
    
ob_start();

    
/*** include the header file ***/
    
include 'includes/header.php';

    
/*** check access level ***/
    
if(!isset($_SESSION['access_level']) || $_SESSION['access_level'] < 1)
    {
        
/*** if not logged in, forward to login page ***/
        
header("Location: login.php");
        exit;
    }
    else
    {

        
/*** set the token to prevent multiple posts ***/
        
$form_token uniqid();
        
$_SESSION['form_token'] = $form_token;

        
/*** get the categories from the database ***/
        
include 'includes/conn.php';

        
/*** check for database connection ***/
        
if($db)
        {
            
$sql "SELECT
                blog_category_id,
                blog_category_name
                FROM
                blog_categories"
;
            
$result mysql_query($sql);
            if(!
is_resource($result))
            {
                echo 
'Unable to find any categories';
            }
            else
            {
                
/*** check for a result ***/
                
if(mysql_num_rows($result) != 0)
                {
                    
/*** put the categories in an array ***/
                    
$categories = array();
                    while(
$row mysql_fetch_array($result))
                    {    
                        
$categories[$row['blog_category_id']] = $row['blog_category_name'];
                    }

                    
/*** set the form values ***/
                    
$blog_form_action 'add_blog_submit.php';
                    
$blog_heading "Add A Blog Entry";
                    
$blog_content_headline '';
                    
$blog_content_text '';
                    
$blog_form_submit_value 'Add Blog';

                    
/*** include the blog form ***/
                    
include 'includes/blog_form.php';
                }
                else
                {
                    echo 
'No categories found';
                }
            }
        }
        else
        {
            
/*** if we are here the database connection has failed ***/
            
echo 'Unable to complete request';
        }

        
/*** include the footer ***/
        
include 'includes/footer.php';
    }
?>

The final part of adding a blog entry is to create the add_blog_submit.php file. Like the add_category_submit.php file, the task of INSERTing the data into the database is quite simple. The same process is repeated with a new SQL query for the blog table. The session token is destroyed in the same way to prevent multiple posting by hitting the refresh button.

The add_blog_submit.php file will look like this.


<?php

    
/*** begin output buffering ***/
    
ob_start();

    
/*** include the header file ***/
    
include 'includes/header.php';

    
/*** check access level ***/
    
if(!isset($_SESSION['access_level']) || $_SESSION['access_level'] < 1)
    {
        
/*** if not logged in, forward to login page ***/
        
header("Location: login.php");
        exit;
    }
    else
    {
        
/*** check the form has been posted and the session variable is set ***/
        
if(isset($_SESSION['form_token'], $_POST['blog_category_id'], $_POST['blog_content_headline'], $_POST['blog_content_text']))
        {
            
/*** first check all POST variables for type and length ***/
            
if(!is_numeric($_POST['blog_category_id']) || $_POST['blog_category_id']==0)
            {
                echo 
'Blog Category Name is Invalid';
            }
            elseif(!
is_string($_POST['blog_content_headline']) || strlen($_POST['blog_content_headline'])<|| strlen($_POST['blog_content_headline'])>50)
            {
                echo 
'Blog Headline is invalid';
            }
            elseif(!
is_string($_POST['blog_content_text']) || strlen($_POST['blog_content_text'])<|| strlen($_POST['blog_content_text'])>4096)
            {
                echo 
'Blog Text is Invalid';
            }
            else
            {    
                
/*** if we are here, include the db connection ***/
                
include 'includes/conn.php';

                
/*** test for db connection ***/
                
if($db)
                {
                    
/*** escape the strings ***/
                    
$blog_user_id $_SESSION['blog_user_id'];
                    
$blog_category_id mysql_real_escape_string($_POST['blog_category_id']);
                    
$blog_content_headline mysql_real_escape_string($_POST['blog_content_headline']);
                    
$blog_content_text mysql_real_escape_string($_POST['blog_content_text']);

                    
/*** the sql query ***/
                    
$sql "INSERT
                        INTO
                        blog_content(
                        blog_user_id,
                        blog_category_id,
                        blog_content_headline,
                        blog_content_text)
                        VALUES (
                        '
{$blog_user_id}',
                        '
{$blog_category_id}',
                        '
{$blog_content_headline}',
                        '
{$blog_content_text}')";

                        
/*** run the query ***/
                    
if(mysql_query($sql))
                    {
                        
/*** unset the session token ***/
                        
unset($_SESSION['form_token']);

                        echo 
'Blog Entry Added';
                    }
                    else
                    {
                        echo 
'Blog Entry Not Added' .mysql_error();
                    }
                }
                else
                {
                    echo 
'Unable to process form';
                }
            }
        }
        else
        {
            echo 
'Invalid Submission';
        }
    }
?>

Retrieve Blog Entries

Now that blog entries can be created, they need to be retrieved in order to be displayed. This is achieved with the use of a single SELECT statement. The page to view the last 5 blog entries will be the main index.php page.

This file is far less complex than the previous examples as it requires only a single query to extract the data and then display it. The conn.php file is included and a check is made for a valid database connection. Further checks are then made to ensure a valid database resource is available and if all is well, the blog data is SELECTed from the database.

The data is put into an array of blog data for use further down the script in the display where a simple foreach loop echoes each record in a little box that can later be styled with CSS.

The index.php file will look like this.


<?php

    
/*** include the header ***/
    
include 'includes/header.php';

    
/*** include the database connection ***/
    
include 'includes/conn.php';

    
/*** check for valid database connection ***/
    
if($db)
    {
        
/*** the SQL query to select last 5 blogs ***/
        
$sql "SELECT
            blog_content_headline,
            blog_content_text,
            DATE_FORMAT(blog_content_date, '%b %d %Y') AS blog_content_date,
            blog_category_name,
            blog_user_name
            FROM
            blog_content
            JOIN
            blog_users
            USING(blog_user_id)
            JOIN
            blog_categories
            USING(blog_category_id)
            ORDER BY blog_content_id DESC
            LIMIT 5"
;

        
/*** run the query ***/
        
$result mysql_query($sql) or die(mysql_error());

        
/*** create the blog array ***/
        
$blog_array = array();

        
/*** check for a valid resource ***/
        
if(is_resource($result))
        {
            
/*** check there are results ***/
            
if(mysql_num_rows($result) != 0)
            {
                
/*** stuff the blog entries into the blog array ***/
                
while($row mysql_fetch_array($resultMYSQL_ASSOC))
                {
                    
$blog_array[] = $row;
                }
            }
        }
        else
        {
            echo 
'Blog Unavailable';
        }
    }
    else
    {
        echo 
'No Blog Entries Available';
    }
?>

<h1>QRdvark.com Blog</h1>
<p>
Welcome to the QRdvark.com Blog...
</p>


<?php

    
if(sizeof($blog_array) > 0)
    {
        
/*** loop over the blog array and display blogs ***/
        
foreach($blog_array as $blog)
        {
            echo 
'<div class="blog_entry">';
            echo 
'<p><span class="category">'.$blog['blog_category_name'].': </span>
            <span class="blog_date">Added by '
.$blog['blog_user_name'].' on '.$blog['blog_content_date'].'</p>';
            echo 
'<h2>'.$blog['blog_content_headline'].'</h2>';
            echo 
'<p>'.$blog['blog_content_text'].'</p>';
            echo 
'</div>';
        }
    }
    else
    {
        echo 
'No Blogs Here';
    }

    
/*** include the footer file ***/
    
include 'includes/footer.php';
?>

Update A Blog Entry

Once a blog entry has been added, it will become the first item displayed on the index page. It is about this time that a spelling error is noticed, so a method to update or edit the entry is required. The form to do this has already been created, and is the same for that was used to add a blog entry, that is, the blog_form.php file in the includes directory.

Like the add_blog.php file, the edit_blog.php will set a number of variables for the blog form, and pre-populate the input fields with the data from the database. This will allow the content to be edited directly. Before this can happen, a method of choosing the blog entry to edit or update is required.

By creating a page with a table to select and list all the blog entries, a simple hypertext link can be used to forward to the edit blog page. The ID of the blog entry is sent along with the URL and is retrieved using GET.

Each blog entry should only be able to be editted by the user who posted it, or the admin user, so it is important to check both the user id of the user, and the access level. Remember, and access level of 5 is the administrator.

The list_blogs.php file will look like this.


<?php

    
/*** begin output buffering ***/
    
ob_start();

    
/*** include the header file ***/
    
include 'includes/header.php';

    
/*** check access level ***/
    
if(!isset($_SESSION['access_level']))
    {
        
header("Location: login.php");
        exit;
    }
    else
    {
        
/*** include the database connection ***/
        
include 'includes/conn.php';

        
/*** check for valid database connection ***/
        
if($db)
        {
            
/*** the SQL query to select last 5 blogs ***/
            
$sql "SELECT
                blog_content_id,
                blog_content_headline
                FROM
                blog_content
                "
;

            
/*** if user is not admin ***/
            
if($_SESSION['access_level'] == 1)
            {
                
$blog_user_id mysql_real_escape_string($_SESSION['blog_user_id']);
                
$sql .= " WHERE blog_user_id = $blog_user_id";
            }
            
/*** run the query ***/
            
$result mysql_query($sql) or die(mysql_error());

            
/*** check for a valid resource ***/
            
if(is_resource($result))
            {
                
/*** check there are results ***/
                
if(mysql_num_rows($result) != 0)
                {
                    
/*** stuff the blog entries into a blog array ***/
                    
$blog_array = array();
                    while(
$row mysql_fetch_array($resultMYSQL_ASSOC))
                    {
                        
$blog_array[] = $row;
                    }
                }
                else
                {
                    echo 
'No Blog Entries Found';
                }
            }
            else
            {
                echo 
'Blog Unavailable';
            }
        }
        else
        {
            echo 
'No Blog Entries Available';
        }
    }
?>

<h3>My Blog</h3>
<h4>Click a headline to edit blog entry</h4>
<table>
<thead><tr><td>ID</td><td>Headline</td><td>Edit</td><td>Delete</td></tr></thead>
<tfoot></tfoot>
<tbody>
<?php

    
/*** loop over the blog array and display blogs ***/
    
foreach($blog_array as $blog)
    {
        echo 
'<tr>
        <td>'
.$blog['blog_content_id'].'</td>
        <td>'
.$blog['blog_content_headline'].'</td>
        <td><a href="edit_blog.php?bid='
.$blog['blog_content_id'].'">Edit</a></td>
        <td><a href="delete_blog.php?bid='
.$blog['blog_content_id'].'" onclick="return confirm(\'Are you sure?\')">Delete</a></td>
        </tr>'
;
    }
?>
</tbody>
</table>

<?php
    
/*** include the footer ***/
    
include 'includes/footer.php';
?>

By now, the format of the list_blog.php file should be quite apparent. The header file is included along with the database connection file, conn.php. A simple SQL SELECT pulls the blog id and headline from the and an array is created which is used in the creation of a HTML table to display each headline. The hypertext link contains the blog_content_id which will be used by the edit_blog.php file to SELECT the data for that ID.

Note that if the user access level is equal to 1, the WHERE clause is added to the SQL query to ensure only the results for that users are displayed.

Like all data supplied from userland, the blog id, called "bid" for short, must be checked and escaped with the mysql_real_escape_string() function to protect against SQL injection. An abbreviated version of the variable named in this case as it will be used in the URL and URL's should be kept as short as possible.

The edit_blog.php file will look like this.


<?php
    
/*** begin output buffering ***/
    
ob_start();

    
/*** include the header file ***/
    
include 'includes/header.php';

    
/*** check access level ***/
    
if(!isset($_SESSION['access_level'], $_SESSION['blog_user_id']))
    {
        
header("Location: login.php");
        exit;
    }
    else
    {
        
/*** set the token to prevent multiple posts ***/
        
$form_token uniqid();
        
$_SESSION['form_token'] = $form_token;

        
/*** check the blog category id is set and is a number ***/
        
if(isset($_GET['bid']) && is_numeric($_GET['bid']))
        {
            
/*** get the categories from the database ***/
            
include 'includes/conn.php';

            
/*** check for database connection ***/
            
if($db)
            {
                
/*** get the categories for the dropdown menu ***/
                
$categories = array();
                
$sql "SELECT blog_category_id, blog_category_name FROM blog_categories";
                
$result mysql_query($sql);
                while(
$row mysql_fetch_array($result))
                {
                    
$categories[$row['blog_category_id']] = $row['blog_category_name'];
                }

                
/*** escape the blog category id and assign to a variable ***/
                
$blog_content_id mysql_real_escape_string($_GET['bid']);
                
$sql "SELECT
                    blog_content_id,
                    blog_content_headline,
                    blog_content_text,
                    blog_category_id,
                    blog_category_name
                    FROM
                    blog_content
                    JOIN
                    blog_categories
                    USING(blog_category_id)
                    WHERE
                    blog_content_id = 
$blog_content_id";

                
/*** check if user is admin ***/
                
if($_SESSION['access_level'] == 1)
                {
                    
/*** allow only blogs for this user ***/
                    
$blog_user_id mysql_real_escape_string($_SESSION['blog_user_id']);
                    
$sql .= " AND blog_user_id=$blog_user_id";
                }

                
/*** run the query ***/
                
$result mysql_query($sql) or die(mysql_error());

                
/*** check for a valid resource ***/
                
if(!is_resource($result))
                {
                    echo 
'Unable to fetch blog record';
                }
                else
                {
                    
/*** check there is a blog entry ***/
                    
if(mysql_num_rows($result) != 0)
                    {
                        while(
$row mysql_fetch_array($result))
                        {
                            
$heading 'Edit Blog';    
                            
$blog_form_action 'edit_blog_submit.php';
                            
$selected $row['blog_category_id'];
                            
$blog_content_id $row['blog_content_id'];
                            
$blog_content_headline $row['blog_content_headline'];
                            
$blog_content_text $row['blog_content_text'];
                            
$blog_form_submit_value 'Edit Blog';
                        }
                        
/*** include the blog form ***/
                        
include 'includes/blog_form.php';
                    }
                    else
                    {
                        echo 
'No blog found';
                    }
                }
            }
        }
        else
        {
            
/*** if we are here the database connection has failed ***/
            
echo 'Unable to complete request';
        }
    
        
/*** include the footer ***/
        
include 'includes/footer.php';
    }
?>

With the edit_blog.php file in place, and all the variables set, it is now simply a matter of creating the file to submit to that will perform the updates made in the form. Note that the correct category for the blog entry is selected in the drop down menu and and can be changed.

Create a file in the includes directory called edit_blog_submit.php. This file will, as with previous scripts, collect all the information coming in, validate it to be sure it is what is expected, escape it to prevent SQL injection, and finally UPDATE the database record, saving the changes. If the process of continually checking and re-checking data seems laborious, think of the task of having the site and/or database compromised and having to rebuild it.

The edit_blog_submit.php file will look like this.


<?php

    
/*** begin output buffering ***/
    
ob_start();

    
/*** include the header file ***/
    
include 'includes/header.php';

    
/*** check access level ***/
    
if(!isset($_SESSION['access_level'], $_SESSION['blog_user_id']))
    {
        
header("Location: login.php");
        exit;
    }
    else
    {
        
/*** check the form has been posted and the session variable is set ***/
        
if(isset($_SESSION['form_token'], $_POST['blog_category_id'], $_POST['blog_content_id'], $_POST['blog_content_headline'], $_POST['blog_content_text']))
        {
            
/*** first check all POST variables for type and length ***/
            
if(!is_numeric($_POST['blog_category_id']) || $_POST['blog_category_id']==0)
            {
                echo 
'Blog Category Name is Invalid';
            }
            if(!
is_numeric($_POST['blog_content_id']) || $_POST['blog_content_id']==0)
            {
                echo 
'Invalid ID';
            }
            elseif(!
is_string($_POST['blog_content_headline']) || strlen($_POST['blog_content_headline'])<|| strlen($_POST['blog_content_headline'])>50)
            {
                echo 
'Blog Headline is invalid';
            }
            elseif(!
is_string($_POST['blog_content_text']) || strlen($_POST['blog_content_text'])<|| strlen($_POST['blog_content_text'])>4096)
            {
                echo 
'Blog Text is Invalid';
            }
            else
            {    
                
/*** if we are here, include the db connection ***/
                
include 'includes/conn.php';

                
/*** test for db connection ***/
                
if($db)
                {
                    
/*** escape the strings ***/
                    
$blog_content_id mysql_real_escape_string($_POST['blog_content_id']);
                    
$blog_category_id mysql_real_escape_string($_POST['blog_category_id']);
                    
$blog_content_headline mysql_real_escape_string($_POST['blog_content_headline']);
                    
$blog_content_text mysql_real_escape_string($_POST['blog_content_text']);
    
                    
/*** the sql query ***/
                    
$sql "UPDATE
                        blog_content
                        SET
                        blog_category_id = 
{$blog_category_id},
                        blog_content_headline = '
{$blog_content_headline}',
                        blog_content_text = '
{$blog_content_text}'
                        WHERE
                        blog_content_id = 
$blog_content_id";

                    
/*** run the query ***/
                    
if(mysql_query($sql))
                    {
                        
/*** unset the session token ***/
                        
unset($_SESSION['form_token']);

                        echo 
'Blog Updated Successfully';
                    }
                    else
                    {
                        echo 
'Unable To Update Blog';
                    }
                }
                else
                {
                    echo 
'Unable to process form';
                }
            }
        }
        else
        {
            echo 
'Invalid Submission';
        }
    }
?>

Delete A Blog Entry

The final part of the CRUD is the Delete. This process is much the same as seen previously in this tutorial when deleting a category. Like the edit blog process, a method is needed to list all the blog entries so a selection can be made. Creating a new list identical to the one just created would be further, and unnecessary duplication. With a small change to the list_blogs.php file, a delete option can be provided next to the edit option.

The delete link in the list_blogs.php file is already in place, saving the extra time of creating a second list.

Note also in the Delete link a small javascript onclick confirm has been provided so when the delete link is clicked, the user will be prompted with a confirmation box with a message. If javascript is disabled, the script will simply delete the relevant blog entry without warning.

The del_blog_submit.php file is very much like the delete category page and performs the same function, it checks that a valid number has been submitted to the page, and if all is will, the link is deleted, and a message appears to say the task is done.

The delete_blog.php file will look like this.


<?php

    
/*** begin output buffering ***/
    
ob_start();

    
/*** include the header file ***/
    
include 'includes/header.php';

    
/*** check access level ***/
    
if(!isset($_SESSION['access_level'], $_SESSION['blog_user_id']))
    {
        
header("Location: login.php");
        exit;
    }
    else
    {
        
/*** check the form has been posted and the session variable is set ***/
        
if(isset($_GET['bid']) && is_numeric($_GET['bid']))
        {
            
/*** if we are here, include the db connection ***/
            
include 'includes/conn.php';

            
/*** test for db connection ***/
            
if($db)
            {
                
/*** excape the string ***/
                
$blog_content_id mysql_real_escape_string($_GET['bid']);

                
/*** the sql query ***/
                
$sql "DELETE
                    FROM
                    blog_content
                    WHERE
                    blog_content_id = 
$blog_content_id";

                
/*** check access level ***/
                
if($_SESSION['access_level'] == 1)
                {
                    
$blog_user_id mysql_real_escape_string($_SESSION['blog_user_id']);
                    
$sql .= " AND blog_user_id = $blog_user_id";
                }

                
/*** run the query ***/
                
if(mysql_query($sql))
                {
                    
/*** affected rows ***/
                    
$affected mysql_affected_rows($link);

                    
header("Location: list_blogs.php");
                }
                else
                {
                    echo 
'Blog Entry Not Deleted';
                }
            }
            else
            {
                echo 
'Unable to process form';
            }
        }
        else
        {
            echo 
'Invalid Submission';
        }
    }
?>

With this file in place, the blog is complete and ready for action.

Sod This, Just Give Me The Blog

The entire source code for this blog application is available for download and users are free to use it, or build on it however they wish.

Credits

Thanks to Captain Cook for discovering Australia, and to the British, for sending us here.