Today I will show you how to make your application run on both SQL databases and flatfile databases. This way we can make applications that take full use of the power of SQL databases but are also usable without them.
Introduction
In this tutorial we will be making a somewhat functional interface to a movie database.
Please note, however, that the main focus will be in the making of the database solution.
I won't waste your time with fancy CSS & JS tricks this time, but try to give you a deep view into Object Oriented Programming (OOP), some design patterns and the MVC -model (Model, View, Controller).
After this tutorial you should be able to use textfiles as primitive databases and design your applications so that changing the database won't affect it's functionality.
Here's a picture of what we will be creating.
Step 1 - Understanding MVC & Design Patterns
The idea behind MVC - and the reason why you should use it - is to separate the different parts of an application.
- The Model
The Model should be the only interface through which your application accesses the database or object properties.
- The View
The View should be the only place where you define how things are to be draw on the screen.
- The Controller
And the Controller is the brains behind it all.
The Controller gets input from the user, makes decisions on how to act, requests the required data from the Model and tells the View to display it to the user, without having any idea where the Model is going to take the data from or how the View is going to display it.
In a basic sense a modern website is sort of MVC.
The HTML or XML file holds the information, the CSS takes care of styling and the JS event kind of run it all.
We, however, will be using the MVC all inside the PHP application.
I've divided the PHP application into a Framework class, that will act as the controller for this project, a View class that will load up predefined templates and inject the requested content in them and a Model class that will decide whether to use SQL database or a flatfile database and depending on that will spawn the right kind of Model subclass.
The Model will implement a design pattern called the Factory pattern.
What that means is that it will create us the kind of model class that we need.
The Framework will be implementing an another design pattern called the Singleton pattern.
In short the singleton is a class that can be instantiated exactly one time.
The class can not be created from outside itself, instead the very class will be holding the information whether or not it "exists" and creates itself when needed.
But more on that later.
Step 2 - Prepare the SQL database
This will be a very simple movie database application, so well manage with just one table. Create it as follows.
CREATE DATABASE `movies` ;
CREATE TABLE `movies`.`movie` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 100 ) NOT NULL ,
`rating` INT NOT NULL ,
`freetext` VARCHAR( 250 ) NOT NULL
) ENGINE = MYISAM ;
This will give us a database table something like in the following picture.
For the flatfile database we need no preparing as it will be created the first time it's used.
Step 3 - Begin Object Oriented PHP
Create a new php file and name it index.php. Or whatever you like, we won't be referencing that name anywhere in the code. But mine is index.php.
That file is going to be very small - only a fhew lines. First of all we need to require the Framework (that we have not yet made).
<?php
require_once 'Framework.php';
The next part is where the design patterns come to play.
$app = Framework::singleton();
$app->main();
We ask the framework to give us an instance of itself.
If there's no instances yet, it will be created by the class, but if the class is already instantiated, it will return us the existing instance.
The constructor of the Framework prepares the other classes and the next thing to do is to just call the Framework's main method.
exit();
?>
Then we can stop the execution of the code and our first file is complete. Just four lines of code.
Unfortunately the other files won't be as easy to write, but just take your time reading and take breaks as needed and I trust you will do just fine.
Step 4 - The Framework
Like said, this is where the brains of the application are. We'll start by adding the rest of our soon to be written files to this one.
<?php
require_once 'settings.php';
require_once 'view.php';
require_once 'model.php';
In the beginnig of the class we'll define some variables.
The instance is going to hold this class.
That may be a little tricky to understand at first, but it will get clearer as me move along.
class Framework
{
private static $instance;
private static $view;
private static $model;
The __constructor is a method that gets called automatically by PHP when an object gets instantiated.
Notice that it is set to private, so it can't be called from outside this object.
We then set the view variable to point at our View class and set the model variable to point at what ever our Model Factory returns us.
private function __construct()
{
self::$view = new View();
self::$model = Model::GetModel();
Ok, now that we have a pointer to a model of some sort, let's make sure it's working.
We don't know what the connect method is doing.
It might be trying to open a mySql database connection or creating a file handle for reading text files, but we know that it's supposed to return 1 if everything went OK.
if ( self::$model->connect() != 1 )
{
echo "Fatal error trying to connect to database.";
exit(0);
}
}
Then the singleton method.
First we check if we have already done this.
If we already have an instace of the class we can just return it, but if it is not yet set we declare it as a new instance of our class.
The __CLASS__ returns whatever class it's in, I might have just as well written self::$instance = new Framework(); It's just the same.
public static function singleton()
{
if( !isset( self::$instance ) )
{
$obj = __CLASS__;
self::$instance = new $obj;
}
return self::$instance;
}
This next part is not at all necessary, but it is important to add if we want to make sure our singleton remains singe.
If the user tries to clone the object, and error will get triggered.
Again the __clone method is something PHP calls automatically when needed.
public function __clone()
{
trigger_error( 'Cloning the framework is not permitted', E_USER_ERROR );
}
Let's leave our main method a bit empty for now.
All we do is tell the View to present to the user what the Model has stored.
We'll come back to this later on to add the functionalities.
public function main()
{
self::$view->showFrontpage( self::$model->GetAllMovies() );
}
}
?>
Step 5 - Setting the settings
Create a new PHP file and call it settings.php.
Here we'll be holding stuff like the database credentials and instructions for the formatting of the flatfile.
The DBTYPE variable will hold the type of database we want to use.
For this example we'll make both mysql and flatfile dbtypes, but if we would happen to need an XML database, all we'd need to do is write it's Model and change the DBTYPE to xmlfile.
<?php
class Settings
{
static $DBTYPE = "mysql";
static $dbhost = "localhost";
static $dbuname = "username";
static $dbpass = "password";
static $dbase = "movies";
static $FILENAME = "db.txt";
static $DELIMITER = '|';
static $ENDROW = '$';
}
?>
I've also specified here the name of the text file that will be used if the DBTYPE is set to flatfile.
The DELIMITER and ENDROW chars are also defined here, so that if you would happen to have a movie with a name like I am Le|gen$d, you can change these to something else.
What they do is tell the model where to snip out cells and rows from a stream of characters.
Step 6 - The Model Factory
Again let's make a new PHP file and call this one the model.php.
Here's a bit longer chunk of code, but it should be pretty clear.
<?php
require_once 'Model_SQL.php';
require_once 'Model_FILE.php';
class Model
{
private static $model;
public static function GetModel()
{
switch (Settings::$DBTYPE)
{
case "mysql":
self::$model = new Model_SQL();
break;
case "flatfile":
default:
self::$model = new Model_FILE();
break;
}
return self::$model;
}
}
?>
We include the files needed and depending on what's written in the settings.php's DBTYPE variable we instanciate the appropriate class and return a pointer to it back to the Framework.
Step 7 - The SQL Model
Quess what? Let's add a new PHP file to our project.
Call this one the Model_SQL.php.
We wont need anything special in the constructor of this class as we have already called for the connect function.
Declare the private static $conn variable to hold the handle of the database connection for future use.
<?php
class Model_SQL
{
private static $conn;
public function __construct()
{
}
public function connect()
{
We'll attempt the mysql_connect and return something other than 1 if the method fails.
Otherwise we'll then select the database to use and inform the Framework that everything wen't fine.
self::$conn = mysql_connect( Settings::$dbhost, Settings::$dbuname, Settings::$dbpass );
if ( !self::$conn )
{
return 0;
}
mysql_select_db( Settings::$dbase, self::$conn );
return 1;
}
There's not much to say about the next two methods.
Mysql_close and mysql_query do exactly what one might think.
We'll be using the Query method whenever we want something from the database.
I've made it private to ensure that only this object can call it.
public function disconnet()
{
mysql_close( self::$conn );
}
private function Query( $sql )
{
return mysql_query( $sql );
}
Then a simple GetAll query.
As we read only one row at a time, we need to stack the rows into an array to return the whole thing as one.
The row's are also arrays and with the flag MYSQL_ASSOC we get the names of the database table columns as the keys of our array.
public function GetAllMovies()
{
$rows = Array();
$result = self::Query( "SELECT * FROM movie ORDER BY title;" );
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
array_push($rows, $row);
}
return $rows;
}
}?>
Let's leave it at that for now as we have a lot more ground to cover.
Step 8 - The Text Model
The text model will (and must) have all the same methods as the SQL model.
In other programming lanquages this could be ensured by making an interface class but now I'm just trusting myself to remember to give both model the same methods.
Make a new PHP file and name it Model_FILE.php.
The beginning of the file is almost identical to that of the other model file, but here we have also an array where we can store the data we read from the file.
<?php
class Model_FILE
{
private static $conn;
private static $movies = Array();
public function __construct()
{
}
public function connect()
{
In the connect method we'll actually be reading the entire database into memory.
In small files this is no problem, but you can't really do a Facebook on flatfile db.
We'll attempt to open the file, place the pointer at the beginnig ('a+') and start reading lines.
Trim each line to get rid of any special characters like newlines etc. and split the line into variables at the delimiter.
Stack the lines in the array and remember to close the file handle.
self::$conn = fopen(Settings::$FILENAME, 'a+') or die();
if ( filesize( Settings::$FILENAME ) > 0 )
{
while ( !feof(self::$conn) )
{
$line = fgets(self::$conn, 1024);
$line = trim($line);
list ($id, $title, $rating, $freetext) = split (Settings::$DELIMITER, $line);
$tmp = Array("id" => $id, "title" => $title, "rating" => $rating, "freetext" => $freetext);
array_push(self::$movies, $tmp);
self::$conn++;
}
fclose(self::$conn);
}
return 1;
}
The GetAll methos is pretty simple as we already have the data in an array.
public function GetAllMovies()
{
return self::$movies;
}
}?>
Step 9 - The View
Now we get the data we need but we still need some way to present it to the user. So create a new PHP file and name it view.php.
This tutorial's view class is nearly empty, but in some other projects there's going to be stuff going on here as well.
The view opens up predefined template files according to the Framework's will.
<?php
class View
{
private static $TEMPLATESRC = "templates/";
public function showFrontpage( $movies )
{
include self::$TEMPLATESRC . "template.php";
}
}
?>
Step 10 - The Templates
Create a new folder called templates and in that folder a new file called template.php. This will be our default template.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Movie Site</title>
<link rel="stylesheet" href="style.css" type="text/css" media="screen" />
</head>
<body>
<div id="container">
<h1>Movie site</h1>
<fieldset>
<label><a href="" onclick="document.getElementById('form').style.display = 'block'; return false;">Add a new movie</a></label>
<div id="form">
<form method="post" action="?add=1">
<p><label for="title">Title</label><br />
<input type="text" name="title" /></p>
<p><label for="rating">Rating</label><br />
<select name="rating">
<option value="1">1 Star</option>
<option value="2">2 Stars</option>
<option value="3">3 Stars</option>
<option value="4">4 Stars</option>
<option value="5">5 Stars</option>
</select></p>
<p><label for="freetext">Review</label><br />
<textarea name="freetext" rows="4" cols="60"></textarea></p>
<input type="submit" name="submit" value="Add movie" />
</form>
</div>
</fieldset>
<!-- INSERT PHP HERE -->
</div>
</body>
</html>
It's just a simple form to add more movies, but I've styled it to hidden by default.
On clicking the Add new movie link the form is revealed.
The link has javascript return false to prevent it from taking the user into the href="" page.
An other way of doing this would be to set the href="javascript: void('null');".
<?php
if( count( $movies ) == 0 )
{
echo "<p><em>No movies</em></p>";
}
else
{
foreach ( $movies AS $i => $key )
{
echo "
<div class=\"movie\">
<p class=\"controls\"><a href=\"?edit=" . $movies[$i]['id'] . "\">EDIT</a> |
<a href=\"?\" onclick=\"if(confirm('Really delete?')){document.location = '?del=" . $movies[$i]['id'] . "'; return false;}\">DELETE</a></p>
<h2>" . $movies[$i]['title'] . "</h2>
<p>";
for ( $j = 0; $j < $movies[$i]['rating']; $j++ ) echo "*";
echo "</p>
<p>" . $movies[$i]['freetext'] . "</p>
</div>
";
}
}
?>
In the PHP part we just loop through the array and echo out the contents.
Step 11 - The CSS
Just a quick and dirty piece of css.
BODY
{
background-image: url('images/bg.jpg');
background-repeat: repeat-x;
background-position: top;
font-family: 'Trebuchet MS';
font-size: 11px;
}
div#container
{
width: 600px;
margin: 90px auto;
}
h1
{
font-family: 'Trebuchet MS';
}
div.movie
{
border: 1px solid #ccc;
background-color: #efefef;
padding: 10px;
margin-bottom: 2px;
}
p.controls
{
float: right;
}
div#form
{
display: none;
}
fieldset
{
margin-bottom: 2px;
border: 1px solid #ccc;
}
Now we have a simple movie database that can request data from two different sources.
Not all that impressive, but wait, there's more!
Step 12 - Adding Functionality (PHP)
Open up the Frameword.php and locate the main method. Add the following lines to the beginnig of the method.
if(isset($_GET['add']))
{
$newmovie = Array("title" => $_POST['title'], "rating" => $_POST['rating'], "freetext" => $_POST['freetext']);
self::$model->AddMovie( $newmovie );
}
else if (isset($_GET['edit']))
{
$row = self::$model->GetMovie( $_GET['edit'] );
self::$view->showEditpage( $row );
return;
}
else if (isset($_GET['done_editing']))
{
$newmovie = Array("id" => $_GET['done_editing'], "title" => $_POST['title'], "rating" => $_POST['rating'], "freetext" => $_POST['freetext']);
self::$model->UpdateMovie( $_GET['done_editing'], $newmovie );
}
else if (isset($_GET['del']))
{
self::$model->DeleteMovie( $_GET['del'] );
}
We take the arguments passed by the user through $_GET or $_POST and call the Model to store, edit or remove content.
Also if the user has requested an entry for editing, we call the View to show a different template and stop the rest of the code from executing.
You can now close the Framework.php as we'll move onto the other parts. Open the view.php and add the new method there.
public function showEditPage( $movie )
{
include self::$TEMPLATESRC . "edit_template.php";
}
If we're editing content we want to see the editing page instead of the frontpage. Create a new PHP file in the templates folder, name it edit_template.php and paste the following in it.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Movie Site</title>
<link rel="stylesheet" href="style.css" type="text/css" media="screen" />
</head>
<body>
<div id="container">
<h1>Movie site</h1>
<fieldset>
<label>Edit movie</label>
<form method="post" action="?done_editing=1">
<p><label for="title">Title</label><br />
<input type="text" name="title" value="<?php echo $movie['title']; ?>" /></p>
<p><label for="rating">Rating</label><br />
<select name="rating">
<option value="1"<?php if($movie['rating'] == 1) echo " selected" ;?>>1 Star</option>
<option value="2"<?php if($movie['rating'] == 2) echo " selected" ;?>>2 Stars</option>
<option value="3"<?php if($movie['rating'] == 3) echo " selected" ;?>>3 Stars</option>
<option value="4"<?php if($movie['rating'] == 4) echo " selected" ;?>>4 Stars</option>
<option value="5"<?php if($movie['rating'] == 5) echo " selected" ;?>>5 Stars</option>
</select></p>
<p><label for="freetext">Review</label><br />
<textarea name="freetext" rows="4" cols="60"><?php echo $movie['freetext']; ?></textarea></p>
<input type="submit" name="submit" value="Done editing" />
</div>
</fieldset>
</div>
</body>
</html>
It's just a form with the values echoed by PHP.
You should now have the following files and folders.
Step 13 - Adding Functionality (SQL)
Here's the rest of the Model_SQL file. Just running some queries and returning what the database server returns.
public function GetMovie( $id )
{
$result = self::Query( "SELECT * FROM movie WHERE id = $id;" );
return mysql_fetch_array($result, MYSQL_ASSOC);
}
public function AddMovie( $newmovie )
{
$result = self::Query( "INSERT INTO movie VALUES (default, '" . $newmovie['title'] . "', '" . $newmovie['rating'] . "', '" . $newmovie['freetext'] . "');" );
}
public function UpdateMovie( $id, $newmovie )
{
$result = self::Query( "UPDATE movie SET title = '" . $newmovie['title'] . "', rating = '" . $newmovie['rating'] . "', freetext = '" . $newmovie['freetext'] . "' WHERE id = " . $id . ";" );
}
public function DeleteMovie( $id )
{
$result = self::Query( "DELETE FROM movie WHERE id = $id;" );
}
Step 14 - Adding Functionality (flatfile)
Here comes the final tricky part of this tutorial.
SQL databases are sophisticated tools with great functionality build in them.
Textfiles are not. Everythig must be done by PHP and most thing are just too comlicated to be done efficiently enough.
But for a small application like this even a text file is plenty of database.
public function SaveToFile()
{
$lines = Array();
$counter = 1;
foreach (self::$movies AS $i => $key)
{
$tmp = implode(Settings::$DELIMITER, self::$movies[$i]);
$tmp = substr($tmp, strpos($tmp, Settings::$DELIMITER) + 1);
$tmp = $counter . Settings::$DELIMITER . $tmp;
array_push($lines, $tmp);
$counter++;
}
$string = implode("\n", $lines);
$fh = fopen(Settings::$FILENAME, 'w');
fwrite($fh, $string);
fclose($fh);
}
Here we have the heart and soul of our class.
It goes throught the array, implodes each row into a string palcing the delimeter between values, strips the id from each row and replaces it with a serialized number.
This is becose while SQL gives us an auto_increment primary key, the textfile has no way of identifying the lines for one another.
Then we implode each line into a string and save it in the beginnig of the file truncating the previouse content.
Each time we change something in the movies array we must call this method to ensure the changes are saved.
public function AddMovie( $newmovie )
{
array_unshift($newmovie, 0);
array_push(self::$movies, $newmovie);
$this->SaveToFile();
}
Ok, this one is simple.
We get the array holding the user submitted information and simply push it to the end of our movies array and save it all to the file.
Since we dont have the primary key, I'm unshifting a number to the newmovie array, so the columns would match.
public function DeleteMovie( $id )
{
for ($i = 0; $i <>SaveToFile();
}
Again wery simple. Go through all the movies and unset the one with the given id. Then save.
public function UpdateMovie( $id, $newmovie )
{
for ($i = 0; $i <>SaveToFile();
}
The updating goes just like the deleting except that instead of unset, we simply overwrite. And don't forget to save.
Step 15 - Take a deep breath
That's it. It has been a long road and the subject has been tough, but we made it!
Instead of showing you neat tricks or beautiful desings I've written about programming, code design and patterns.
I hope you have found this tutorial helpful and maybe next time I'll write something a little less dull ;)
Thanks for reading!
