The Magic of PHP + MySQL

It's safe to say that nearly every website that's up-to-date these days is using some form of content management system (CMS). While there are a ton of great free options that provide us with a CMS to power a website (WordPress, Drupal, etc.), it doesn't hurt to peek under the hood and get a feel for how these systems work.

To get our feet wet as back-end developers, we'll be creating a simple PHP class that will:

  • Create a database
  • Connect to a database
  • Display a form with two fields
  • Save the form data in the database
  • Display the saved data from the database

Download Files

This class is intended to give you a feel for how PHP and MySQL interact together, and to show the basics of a CMS. I'll be skipping explanations of some of the very basic programming stuff, so if at any point you feel lost, checkout the course Diving into PHP and give yourself a crash-course in PHP. I'll try not to lose anyone, though, I promise.

Building the Class

Our first step is to simply lay out the class in a file named 'simpleCMS.php' so we have a road map to work with.


class simpleCMS {
  var $host;
  var $username;
  var $password;
  var $table;

  public function display_public() {

  public function display_admin() {

  public function write() {

  public function connect() {

  private function buildDB() {


As you can see, we're creating one class with four variables and five methods. I've opted to use PHP's object-oriented approach because it makes for cleaner code in large projects, and, in my opinion, it's just good practice.

The Variables

In this case, all four variables are for connecting to the database: $host, $username, $password, and $table provide a path and access to our database on the server. For now, we'll leave those empty and move on to our database, which is constructed by the method buildDB().

Build the Database

private function buildDB() {
    $sql = <<<MySQL_QUERY
            title	VARCHAR(150),
            bodytext	TEXT,
            created	VARCHAR(100)

    return mysql_query($sql);

This function runs a MySQL command that checks the database to see if testDB exists. If so, it simply passes along a notification of success; if not, it creates our table and assigns three columns to hold data.

Connect to the Database

Now that we have a function to build our table, let's create the function that will connect to our database.

public function connect() {
    mysql_connect($this->host,$this->username,$this->password) or die("Could not connect. " . mysql_error());
    mysql_select_db($this->table) or die("Could not select database. " . mysql_error());

    return $this->buildDB();

We call mysql_connect() to hook into our database, and then mysql_select_db() to make sure we save our data in the right place. Both of these functions are accompanied by the die() command, which essentially says, "in the event that this function fails, stop execution of this script and display a message."

Our connect() function connects to the database and gets us pointed in the right direction, then runs our buildDB() function. Remember the grammatically awkward "IF NOT EXISTS" part of our MySQL command? Because we're going to run this function every time the page is loaded, we have to make sure we're not overwriting our database with every function call, and that's exactly what that phrase requires.

Build the Form

So, we've got a database. Now we just need to put stuff in it!

public function display_admin() {
    return <<<ADMIN_FORM

    <form action="{$_SERVER['PHP_SELF']}" method="post">
      <label for="title">Title:</label>
      <input name="title" id="title" type="text" maxlength="150" />
      <label for="bodytext">Body Text:</label>
      <textarea name="bodytext" id="bodytext"></textarea>
      <input type="submit" value="Create This Entry!" />


Again, this is a very simple function. When called, it simply returns the HTML markup to create our form. You'll notice, however, in the action attribute of the form element, that I've used the variable $_SERVER['PHP_SELF']. This is, essentially, a shortcut that references the file you're currently using (in our case, it's display.php). This is useful if you'll be reusing your code across a site and don't necessarily want to rewrite this function for each page.

I'm also going to take a second right now to talk about the method I'm using to return the HTML. It's a format used in PHP called HEREDOC syntax, and I love it.

The primary advantage of HEREDOC is that it allows you to include formatting in your output. This is extraordinarily useful for folks like me who take issue with cluttered source code. You can read more about HEREDOC syntax and its ilk in the PHP manual.

Saving the Data to the Database

Our form will allow us to input information, so how do we save it? That's where our write() method comes in.

public function write($p) {
    if ( $p['title'] )
      $title = mysql_real_escape_string($p['title']);
    if ( $p['bodytext'])
      $bodytext = mysql_real_escape_string($p['bodytext']);
    if ( $title && $bodytext ) {
      $created = time();
      $sql = "INSERT INTO testDB VALUES('$title','$bodytext','$created')";
      return mysql_query($sql);
    } else {
      return false;

Let's start with the function call itself — we're passing a variable to this one, which we haven't done so far. Our variable $p is going to hold the information sent from our form via the post method.

Once inside the function, we start with a conditional statement that's checking to see if the the title value was set in the form before it was submitted, and if so, we're setting our $title variable to the $_POST['title'] value (NOTE: we're using the function mysql_real_escape_string() as a precaution against potentially dangerous input, which is important to keep in mind when you're building anything that will allow users to input information). If $_POST['title'] wasn't set, we skip this line, leaving the $title variable unset.

This process is repeated for our second input, and then both variables are checked to make sure nothing is blank before saving to the database. If both variables are set, we then set the $created variable with the current Unix timestamp, which we'll use to sort our entries chronologically when we view them in the future.

We now have three variables, and because we've run checks, we know that all three variables are not empty. Now we can write our MySQL query that will save the entry in the database!

Displaying the Information from the Database

Now that we have the means to put information into our database, we need to create a way to get that information back out. This is where display_public() comes in. This is by far the most complex of our methods, so let's really take our time and figure out what's going on inside.

public function display_public() {
    $q = "SELECT * FROM testDB ORDER BY created DESC LIMIT 3";
    $r = mysql_query($q);

    if ( $r !== false && mysql_num_rows($r) > 0 ) {
      while ( $a = mysql_fetch_assoc($r) ) {
        $title = stripslashes($a['title']);
        $bodytext = stripslashes($a['bodytext']);

        $entry_display .= <<<ENTRY_DISPLAY


    } else {
      $entry_display = <<<ENTRY_DISPLAY

    <h2>This Page Is Under Construction</h2>
      No entries have been made on this page. 
      Please check back soon, or click the
      link below to add an entry!

    $entry_display .= <<<ADMIN_OPTION

    <p class="admin_link">
      <a href="{$_SERVER['PHP_SELF']}?admin=1">Add a New Entry</a>


    return $entry_display;

The first thing to note when reading from a database is the way PHP and MySQL interact with each other. First, we ask the database a question (query), to which it replies with a result (resource). However, this result isn't really useful until we've decoded it using one of several methods that "fetch," or organize, the information that's contained inside into a usable form (array).

Our very first action in the above function is to set up our query in the variable $q. The asterisk (*) operator in MySQL means "everything," so our query is asking the database to select everything from entries in the table testDB in reverse chronological order, limited to the first three entries returned.

Now that the query is defined, we send it to the database using the function mysql_query(). The resulting resource is stored in the variable $r. This is where it gets a bit tricky.

We now run a conditional statement that says, "IF mysql_query() didn't fail, AND IF the number of entries returned was greater than zero, process the result, OR ELSE display a default message."

If $r contains entries from the database, we now have to "fetch" that data. Information from the database is returned as an array, which is organized similarly to the database table itself. The function mysql_fetch_assoc() will take the resource and break each entry into an associative array (this means that when we save the result of mysql_fetch_assoc() into the variable $a, the data from the entry will be accessible by the column names in the database, i.e. $a['title']).

However, mysql_fetch_assoc() only gives us one entry at a time. To get all of the returned entries, we have to use a while loop. Essentially, we're saying, "WHILE $r has values we haven't used yet, get the next entry in line and do the following actions with it."

In this case, we're going to check the entry to make sure that data was returned, then remove the slashes that were added when we saved the information to the database using stripslashes(). After that, we simply wrap the variables in some HTML and, voila! we've got screen-ready content!

As a final step, the code adds a link to the bottom that allows users to add an entry. It's worth noting the use of the ".=" operator used in the while loop and when creating the "Add a New Entry" link; a function can only return one variable, so we need to append the new information to the existing variable. If we just used the equals sign ("="), we would overwrite existing data and end up with just a link to the form and no content.

So, you've now written your first CMS class! You can easily write and retrieve data to and from a database. All that's left to do is to try it out!

Using the Class

To use our class, we need to create a separate file. I'm going to call it "display.php", which I'll save in the main web folder, with our class saved as "simpleCMS.php" in a folder called "_class" within the main folder. To start, we just set up a document with plain ol' HTML.

<!DOCTYPE html>
<html lang="en">





To use our class, we just have to insert a little PHP between the body tags:


  $obj = new simpleCMS();
  $obj->host = '';
  $obj->username = 'DB1234567';
  $obj->password = 'DBpassword';
  $obj->table = 'DB1234567';

  if ( $_POST )

  echo ( $_GET['admin'] == 1 ) ? $obj->display_admin() : $obj->display_public();


First and foremost, we have to include the class using the include_once() function. Then, we have to instantiate our object so that our code knows what's going on. Third, we set all of those variables we talked about toward the beginning of this tutorial. You'll have to replace all of those values with the information you get from your own server or hosting company. And fourth, we connect to our database using the connect() method.

After we've connected to the database, we check to see if any $_POST information exists. This is because we're using the same file for input, processing, and display of information. If anything was passed via $_POST, we run the write() function to validate it and save it to the database. Then, we use some shorthand trickery to run a conditional statement. In essence, we're saying, "IF $_GET['admin'] is set to 1, then show the form using display_admin(), OR ELSE show me the stored entries using display_public()."

And that's it! Once you get a feel for it, this sort of basic programming will allow you to start exercising total control over websites you build, whether you decide to really dig in and build your own CMS framework or just improve an existing CMS by, say, writing a WordPress plugin.

Really, when it comes to modern web design, you should have at least some understanding of how things are working behind the curtain—understanding how a site works will better enable you to design sites that have a more fluid integration of form and function. And besides, adding PHP and MySQL to your curriculum vitae definitely won't hurt your credibility...

Download Files

Jason Lengstorf runs Ennui Design, a freelance design and development effort. He has a fetish for building custom applications from scratch, including his own content management system. When he's not glued to his keyboard, he's likely to be wearing cowboy shirts, deadlifting, or pretending to know stuff about wine.

Important Note

This code is written for demonstration purposes only. Several security holes have been pointed out in the comments, which I have addressed in Part Two of this tutorial series Editor's note: There is no part two of this series anymore. Jason recommends his book PHP for Absolute Beginners as a resource for best practices. Still, I would strongly advise not using it for production websites without further testing.

I have covered some of the bases with security, but other issues may exist. Further reading on security risks and safe PHP code can be found here. Please read through this before implementing this code on your server to avoid potential security holes.