Adventures in PHP: Databases

If you know me, you know that I’m not a big fan of PHP for various reasons. Recently though I’ve found the need to become more familiar with development using PHP. Many of the applications that I develop require database connectivity. Instead of using a framework, I decided to roll out my own database connection layer to familiarize myself with PHP database methods and possible creating some reusable objects to plug into other projects.

The first step was to setup a MySql database, configure the user, and make sure that a had some data to run tests against. In a PHP class I am taking, one of the assignments was to create a guestbook application that was backed by a database. Using this as my frame of reference, I mapped out a simple table to store guestbook entries.

CREATE TABLE `guestbook` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(100) DEFAULT NULL,
  `Author` varchar(45) DEFAULT NULL,
  `Email` varchar(65) DEFAULT NULL,
  `Message` varchar(2500) DEFAULT NULL,
  `DateOfEntry` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1

After the database table was established, I manually inserted a couple rows of data to query against and test the result sets as they appear in PHP. Now that my test data was available, I was ready to start building my database layer.

The first step in creating my database connector class was defining what I would be doing. In this case, all I really need to do is query and insert. After some quick searches I found how to establish a database connect, query, and do inserts using PHP. I compiled it all into a simple PHP class using NetBeans and ran some quick tests to verify connectivity, query results, and begin doing some test inserts. The final DbConnector.php class looks like this:

<?php

/**
 * This class is intended to be a database connection tool
 *
 * @author Russell Shingleton
 */
class DbConnector {

    private $server = "127.0.0.1";
    private $db_name = "guestbook";
    private $username = "gbadmin";
    private $password = "gb$admin1";
    private $con = null;

    public function openConnection() {
        $this->con = mysql_connect($this->server, $this->username, $this->password);
        if (!$this->con) {
            die('Could not connect to database: ' . mysql_error());
        }
    }

    public function closeConnection() {
        mysql_close($this->con);
    }

    public function query($query) {
        mysql_select_db($this->db_name, $this->con);
        return mysql_query($query);
    }

    public function insert($query) {
        mysql_select_db($this->db_name, $this->con);
        return mysql_query($query) or die(mysql_error());
    }
}

?>

This is a very simple example of a PHP database connector object that can be extended as need arises.

Next I will attempt to begin integrating this into my PHP guestbook example.