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
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, head on over to
w3schools.com 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.
<?php
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
CREATE TABLE IF NOT EXISTS testDB (
title VARCHAR(150),
bodytext TEXT,
created VARCHAR(100)
)
MySQL_QUERY;
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!" />
</form>
ADMIN_FORM;
}
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
<h2>$title</h2>
<p>
$bodytext
</p>
ENTRY_DISPLAY;
}
} else {
$entry_display = <<<ENTRY_DISPLAY
<h2>This Page Is Under Construction</h2>
<p>
No entries have been made on this page.
Please check back soon, or click the
link below to add an entry!
</p>
ENTRY_DISPLAY;
}
$entry_display .= <<<ADMIN_OPTION
<p class="admin_link">
<a href="{$_SERVER['PHP_SELF']}?admin=1">Add a New Entry</a>
</p>
ADMIN_OPTION;
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.
<html>
<head>
<title>SimpleCMS</title>
</head>
<body>
</body>
</html>
To use our class, we just have to insert a little PHP between the body tags:
<?php
include_once('_class/simpleCMS.php');
$obj = new simpleCMS();
$obj->host = 'database.host.net';
$obj->username = 'DB1234567';
$obj->password = 'DBpassword';
$obj->table = 'DB1234567';
$obj->connect();
if ( $_POST )
$obj->write($_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...
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.
The demo has been removed.
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.
