PHP Lessons - Lesson 3.1 - Working with MySQL DB. Creating tables.

In the previous lesson, we created a connection to the database. In this lesson, we’ll create tables for our future website. First, let’s add the name of the required database in the simpleCMS class:

class simpleCMS {  // management class

  public $host = 'localhost'; // variables for DB connection
  public $username = 'root';
  public $password = '';
  public $db = 'testDB';

Now we need to create this database on our MySQL server. If you’re using Denwer, phpMyAdmin is included. Go to phpMyAdmin using this link and create a new database:

Now that we’ve set the config and created the database, let’s update the PHP database connection method. Modify the DB connection method as follows:

public function connectDB() {
  $link = mysql_connect($this->host, $this->username, $this->password); // connect to MySQL server
  if (!$link) {
    die('Connection error: ' . mysql_error());
  }
  mysql_select_db($this->db) or die("Can't find DB. " . mysql_error()); // select the database
  return $link;
}

If everything is correct, there should be no error messages. Now let’s create the table. To do that, add a buildDB() method and call it directly from connectDB():

public function connectDB() {
  $link = mysql_connect($this->host, $this->username, $this->password); // connect to MySQL server
  if (!$link) {
    die('Connection error: ' . mysql_error());
  }
  mysql_select_db($this->db) or die("Can't find DB. " . mysql_error()); // select the database
  $this->buildDB(); // call internal method from within the same class
  return $link;
}

public function buildDB() {

}

Now, each time we connect to the database, buildDB() can check if our tables exist. Let’s send a query to the DB:

public function buildDB() {
  $sql = 'CREATE TABLE IF NOT EXISTS messages (
            title     VARCHAR(150),
            bodytext  TEXT,
            created   VARCHAR(100)
          )';
  return mysql_query($sql);    
}

Let’s break down how this works.

First, we assign the SQL query to a string variable, and then pass that variable into mysql_query, which sends the query to the database.

Our SQL query begins with the CREATE TABLE operator. Then we use IF NOT EXISTS to check if the table named messages already exists.

Inside the parentheses after the table name, we list the table’s columns that will be created. In our case, those are: title, bodytext, and created. After each column name, we specify its data type: VARCHAR(150) is a string with a max length of 150 characters, and TEXT is a text field suitable for full articles.

Now, if you save the simpleCMS.php file and run index.php, the messages table will be created.

We’ll start using this table in the next lesson. If you encounter any questions or errors, leave them in the comments.