My name? Base… Data Base.
I was approached a few weeks ago to be part of an exciting little project that unfortunately I’m not at liberty to reveal (for now, let’s refer to it as “project-n“) and therefore must digress on the details of what the app will do. However, the technical aspect of the project has been particularly interesting and lends itself to a post such as this.
The public-facing part of the site is/will-be a flash application displaying content in a “turn-page” fashion, which I will probably talk more on at a later time. This system will grab its’ data as XML output from a MySQL database.
On the back-end, we basically needed a CRUD (Create, Read, Update, Delete) style system, with some custom file management integrated into it. I had two major language choices: Ruby (with the Rails framework of course) or PHP. The programmer in me desperately wanted to take this opportunity to go nuts with Rails and create some great new web-two-point-o masterpiece, but the time limit we’re working with meant that I really needed to stick to PHP (which I am far more familiar with).
So, with language choice out of the way I figured I had better pick a nice little framework and get the job done simply and quickly. After a couple of hours spent looking at the various option available, I soon realised that using a framework is just like learning a new language as you have to go back and learn all the conventions etc… Not really ideal, as RoR would probably be quicker than learning a new PHP framework. SO, staring down the barrel of a VERY clean slate I looked at what I wanted to achieve and slowly pieced together an execution strategy.
Now, as a side-note it’s worth keeping in mind that last week I had a discussion with my boss regarding our ever increasing workload and we are definitely taking on an employee in the near future. However, considering our last struggle to find a PHP Developer who is familiar with the Joomla! Framework, it is just so much easier for us to find another designer while I slide into a development role. To be honest I have been getting a bit tired of design lately and have been doing a lot of development work anyway. So, it all just fits together like a glove and means that building my own framework for “project-n” is the perfect refresher course in PHP.
Phew… Now, with the intro out of the way, I wanted to cruise into some code. Obviously the first thing to do is put together a database API so that we can grab and manipulate data. A long time ago I put together a super basic connection management class and posted it on my old site, this was a good start. I have modified it slightly to be more extensible, but this makes up the basic part of our class:
class DB {
var $host;
var $user;
var $pass;
var $db;
var $query;
var $result;
var $lastresult;
var $rowsaffected;
var $insertid;
var $link;
var $errors;
function DB($host, $db, $user, $pass) {
$this->host = $host;
$this->db = $db;
$this->user = $user;
$this->pass = $pass;
$this->DBconnect();
}
function DBconnect() {
$this->link = @mysql_connect($this->host, $this->user, $this->pass);
if(!$this->link) {
// Error here
}
$this->select($this->db);
register_shutdown_function(array(&$this, 'close'));
}
function select($db) {
if (!@mysql_select_db($db, $this->link)) {
// error here
}
}
function close() {
// Close our database connection
// This is ultra important - don't wanna overload our server.
@mysql_close($this->link);
}
}Very basically, when the class is initalised we make a connection to MySQL and tell it which database we want to look at. You’ll notice we’ve also registered a function to close the connection automatically for us when we’re done. We really need so much more functionality than this, so let’s get a mechanism for processing queries (the interesting part):
function doquery() {
$this->result = @mysql_query($this->query, $this->link);
if(mysql_error()) {
// error here
}
// We need to handle insert, delete, update
// and replace statements differently to select statements:
if(preg_match("/^\\s*(insert|delete|update|replace) /i",$this->query)) {
$this->rowsaffected = mysql_affected_rows();
// Just in case, let's grab the insertid - could be userful:
if(preg_match("/^\\s*(insert|replace) /i",$this->query)) {
$this->insertid = mysql_insert_id($this->link);
}
// How many did we successfully change?
$return = $this->rowsaffected;
} else {
// How we handle select statements:
$i = 0;
while($row = @mysql_fetch_object($this->result)) {
$this->lastresult[$i] = $row;
$i++;
}
@mysql_free_result($this->result);
// Store number of rows returned
$this->numrows = $i;
// Return number of rows selected
$return = $this->numrows;
}
return $return;
}Okay, let’s give ourselves some ability to interface with this puppy hey?
function setquery($query) {
$this->query = $query;
}
function getresults() {
// Run query and grab the return:
$this->doquery();
if($this->lastresult) {
return $this->lastresult;
}
else {
return false;
}
}
function getaffectedrows($suppress=false) {
if(!$suppress) {
// Run the query by default
// Otherwise JUST return the results:
$this->doquery();
}
if($this->rowsaffected) {
return $this->rowsaffected;
} else {
return false;
}
}
function geterrormsg() {
// Simple error return:
return '[' . mysql_errno() . '] ' . mysql_error();
}The complete code can be grabbed here: database.class.phps
This code will no doubt be added to over the coming week or two, but in it’s current state we can simply use it like so:
$db = new DB( "localhost", "mytestdbname", "root", "root" );
$db->setquery("SELECT title FROM mytesttablename WHERE 1 = 1");
$resultsarray = $db->getresults();Easy right? Obviously you just do a loop to output the array of objects like this:
foreach($resultsarray as $result) {
echo $result->title . "<br />";
}And that’s the end of the game ladies and gentlemen! Enjoy.
