Tag: database

A lot of PHP frameworks like to boast “create a blog in 20 minutes”. Well, I’m going to show you how to create a bare bones blog in about 30, but without the use of a framework. Our blog will include posts, and comments. I won’t bother with HTML headers and footers – you can add those in yourself. I recommend using my XHTML template; most of the pages below can easily be inserted into the body. I also won’t be covering user authentication in this tutorial, I’ll cover that in a later tutorial. We also won’t be doing much error checking, or prevention against users inserting malicious code. We will, however, prevent SQL injection with a very simple function.

What you will need:

• A web server with PHP and MySQL support
• Very basic programming knowledge

Topics covered:

• Using MySQL to insert, delete, and update posts and comments
• Iterating over MySQL results
• Using cookies to save commenter information
• Protection against SQL injection
• Examples of php’s date() function
• Using anchors to scroll the page
• Redirection
• Heredocs

Not covered:

• User authentication (coming later)
• Categories and tags (coming later)
• Prevention against other (JavaScript) injection attacks
• Error checking (empty fields, invalid emails, etc.)

The first thing we need to do is create some MySQL tables so that we have some place to store our data. We will be using two tables for our blog: posts, and comments. I will show you how to add categories and users in later tutorials.

If you haven’t already done so, you will need to create a MySQL database for your blog. I cover this in step 4 of my WordPress tutorial. Make note of your database name, username and password; you will need these in a few minutes.

Now create the following tables. You can do so by running the following code in the “SQL” tab of phpMyAdmin if you have it installed.

You may have noticed “date” is an int rather than datetime. I prefer to use a unix timestamp instead because they are easier to work with in PHP, but you can use whatever you choose — just keep in mind you will have to modify some of the code below if you choose to use datetime instead.

Now open your favorite text editor, and get ready to start coding! I use EditPlus, but there are plenty of free alternatives. We will be creating the following pages:

• index.php
• post_add.php
• post_delete.php
• post_edit.php
• post_view.php
• comment_add.php
• comment_delete.php
• mysql.php

You can create these files now if you like, and we’ll slowly fill them in. Don’t worry, most of them are just a few lines of code!

Let’s start with the most important file: mysql.php. This file simply connects to our database so that we can run queries on it. I’ve also included a few simple helper functions. I don’t know how people get by without these!

If you hadn’t noticed, you will need to fill in your username, password and database name. mysql_safe_query is basically a combination of mysql_query, sprintf, and some sanitization. It converts empty strings to NULL, leaves numbers as they are, and quotes and properly escapes strings as necessary so that you don’t have to worry about SQL injection attacks.

Now let’s add the ability to make some blog posts!

At the bottom is a simple form with title and body fields, and a submit button. Pretty self explanatory. If you omit the “action” attribute in the form element it will simply post back to the current page; this is what we want.

The PHP at the top simply checks if anything has been posted, and if it has it inserts it into the “posts” table in our database. It doesn’t check that both “title” and “body” have been properly filled out, nor does it prevent users from entering malicious JavaScript code. If you want to check against these things, do it before you insert the data into the database (line 5). However, in a proper implementation this page would be protected with some sort of user authentication, so you may want to allow it if it’s from a trusted user.

The “if” statement checks if there were any (usually syntax) errors in the query. If there weren’t any, and the post was successfully inserted into the database, it notifies the user and adds a link to view the newly created post. Otherwise, it spits out the error for debugging.

Since we now have a link to post_view.php, we might as well create that page next.

This one’s a little more complicated. It displays the blog entry, all comments on the entry, and adds a form to post new comments.

Line 4 gets the post given by the “id” variable in the URL. We use “LIMIT 1” to let MySQL know we only want 1 result back, and it can stop searching after it has found it. Lines 6-9 check that we actually got a result back. Lines 11-15 will display the post. Notice how in the date script I used <sup> to display the suffix on the date – we need to escape the “s” and the “u” so that the date function doesn’t convert them, but the “p” isn’t an accepted letter so it doesn’t need to be escaped. On line 14 we convert new lines into <br/>s so that you don’t need to type them in your post. Line 15 prints out some options we’ll talk about later.

Lines 17-28 will display all the comments in an ordered list. The while loop on line 20 is used to iterate over all the results/comments. We give each comment an “id” so that we can use anchor links, as you will see later.

Lines 30-55 print uses heredoc syntax to print out the form. Heredocs behave the same as double-quoted strings, meaning you can use variables in them. Just remember to use squiggly brackets {} around array variables. We include $_COOKIE variables to automatically fill out the user’s name, email and website so that he doesn’t have to retype them every time he leaves a comment. I will show you how to set these next. Note that if the variable does not exist/isn’t set, the value will just be left blank, like we want. We also include the post ID in the action URL on line 31. We could have just as easily included this in a hidden form field, as many people like to do, but I like to put it in the URL for a little more consistency. If we later decide we want to put the comment form on the actual comment_add page, then we can link directly to it with the id in the URL, whereas otherwise we would have to somehow post the id over.

Let’s add the comment_add.php page next.

First we save the user’s input to some cookie variables. We set to expiry date to one month from now. This will be refreshed every time the user comments on something. Lines 9 and 10 simply insert the comment into the database. Line 11 increments the comment counter on the post. We could have just retrieved the number of comments with MySQL’s COUNT(*) command, but this puts extra strain on the database if it’s used a lot. It probably won’t make a difference until you’re getting thousands of visitors per day, but it’s always a good idea to do it right the first time around. Line 12 redirects back the post we were just on, and scrolls the page down to the comment we just inserted – people like to see that their comment was actually posted.

Deleting comments is pretty easy, so we’ll do that next.

Not much needs explaining here. Same deal as before. Just remember the decrement the comment counter or it will be off.

Let’s delete some posts too while we’re at it.

You may have noticed that I haven’t been using closing PHP tags – you don’t need them, and it’s actually probably a good idea not to include them so that you don’t accidentally output some whitespace. Notice we also delete all the comments associated with the post. It won’t hurt much to leave them there, but we might as well save some disk space and give MySQL less to dig through.

On to post_edit.php. I always find the edit pages to be the biggest pain to write.

This one’s not so bad. We simply retrieve the post, and then fill the form with it. We use MySQL’s UPDATE command to update the post, then redirect back to the post so the user can see his changes. You might notice that this page is quite similar to the post_add.php page. You can merge these two pages if you like, so that if you add any more fields to the post, you don’t have to edit two pages, but then you end up with a lot of conditionals just to change a bit of wording and it can get quite messy.

Now, back to the beginning, let’s write the index page. The first page your users will see when the view your blog. It should probably display only the last few entries, but for simplicity, we’re going to make it display all entries on one page.

The loop here is quite similar to the comment loop you saw earlier, so there isn’t much to explain. I’ve shortened the posts to 300 characters so that you don’t end up with too much text on one page. Note that you need a line break after the heredoc if it’s at the end of the page, otherwise PHP will complain.

You should now have a fully functional blog! We will add more features to it in the future, so stay tuned. Please leave some comments below and let me know if this tutorial was too difficult to follow, or too wordy 🙂

It’s better if you work through the tutorial on your own, but for convenience, I’ve included a zip file with all the above files. I wouldn’t use this on a live site! It’s meant for learning purposes only.