Tutorials

Part 1: Create your database

This is the first of several tutorials that will ultimately teach you to create your own blog using PHP and MySQL. I’m going to try to write it to the newbie coder, and hopefully you’ll gain a better understanding of PHP along the way. I’ll try to write one tutorial per week.

Here are the topics I plan to cover:

  1. Creating the table in your database.
  2. Using an HTML form and PHP processing to post to your blog.
  3. Calling your blog entries for the main page and individual entry pages.
  4. Passwording individual entries.
  5. Listing your archives.
  6. Allowing comments on your blog.
  7. Pinging Ping-O-Matic
  8. Sending trackbacks.
  9. Recieving trackbacks

This first entry is going to teach you to write a file that will set up your database for your blog.

First we need to understand how we want our database to look. We’re going to make it very simple and use 4 columns: id, timestamp, title, and entry. Once you grasp the concept you can have as many or as few columns as you like. And we’re going to call our table ‘php_blog’.

First we have to open php:

<?php

Now we need to log in to our database. You need to know the name of your db (db_name), your MySQL username (db_username) and your password (db_password). Replace the variables on the following code with your information:

mysql_connect ('localhost', 'db_username', 'db_password') ;
mysql_select_db ('db_name');

The next part of our code is the set of instructions that our script will send to our database. All of this is included in the variable $sql. First we’ll tell it to create the table called ‘php_blog’:

$sql = "CREATE TABLE php_blog

Now, that’s not the end of the query. We still need to tell it which columns to create. Our first column, id, is going to be an integer with a binary value of no more than 20 (which means you won’t be able to have more than 2,359,296 entries). We’re also going to make id not null, and auto-increment so that every new entry will get the next available id. So let’s tack that on:

$sql = "CREATE TABLE php_blog (
  id int(20) NOT NULL auto_increment,

Next we want our timestamp. A timestamp is more useful than just writing in the date and time because you can format it any way you want, or just extract the year (for archives by year) or just get the time, etc. Let’s make sure our timestamp is not null, too:

$sql = "CREATE TABLE php_blog (
  id int(20) NOT NULL auto_increment,
  timestamp int(20) NOT NULL,

Of course our entries need titles. Let’s make our title column a varchar (variable characters) and allow it a binary value of 255 and make it not null. This should give us a good range for the title:

$sql = "CREATE TABLE php_blog (
  id int(20) NOT NULL auto_increment,
  timestamp int(20) NOT NULL,
  title varchar(255) NOT NULL,

Our final column is going to be the actual blog entry. We’re going to make this longtext and not null:

$sql = "CREATE TABLE php_blog (
  id int(20) NOT NULL auto_increment,
  timestamp int(20) NOT NULL,
  title varchar(255) NOT NULL,
  entry longtext NOT NULL,

And the last thing we need to do is make our id the primary key and the unique key. The id is what everything hinges on. It’s how we’ll identify entries and call them in our blog. Each entry must have a unique id.

$sql = "CREATE TABLE php_blog (
  id int(20) NOT NULL auto_increment,
  timestamp int(20) NOT NULL,
  title varchar(255) NOT NULL,
  entry longtext NOT NULL,
  PRIMARY KEY  (id)
)";

Now we’re going to tell MySQL that it should either create our table or return an error:

$result = mysql_query($sql) or
print ("Can't create the table 'php_blog' in the database.<br />" . $sql . "<br />" . mysql_error());

Finally we need to close MySQL:

mysql_close();

and PHP:

?>

So let’s look at our whole script start to finish:

<?php

mysql_connect ('localhost', 'db_username', 'db_password') ;
mysql_select_db ('db_name');

$sql = "CREATE TABLE php_blog (
  id int(20) NOT NULL auto_increment,
  timestamp int(20) NOT NULL,
  title varchar(255) NOT NULL,
  entry longtext NOT NULL,
  PRIMARY KEY  (id)
)";

$result = mysql_query($sql) or print ("Can't create the table 'php_blog' in the database.<br />" . $sql . "<br />" . mysql_error());

mysql_close();
?>

Now save the file as anything you like, as long as it has a .php extension. Then, upload the file and browse to it (in your browser). If it doesn’t return any errors, check your db to be sure it worked and delete the file from your server immediately.

UPDATE: If you want to print a message letting you know that the table creation worked, add this code at the end, before you close PHP:

if ($result != false) {
    echo "Table 'php_blog' was successfully created.";
}

Comments

Error Comments are closed for this entry.