Abstracting MySql results in PHP

If there is one thing a web server does everyday it iss connecting to the database. I have been using PHP for many years now, but if you ask me to write a script to fetch data from the database I couldn't do it without going back to the Ultimate PHP manual to find a few examples first.

I can never remember which is the correct function to use; mysqli_fetch_array, mysqli_fetch_row, or mysqli_fetch_assoc. A little trip to Google always help but I think it is not something we should be worrying about every time we want to write a query.

Here is an example of writing it all from scratch:

$con = new mysqli('localhost','username','password','database');

// stop if there is an error; 
if ($con->connect_error){ // improve your error handler for extra points
    die("$con->connect_errno: $con->connect_error"); 
}

// a simple query
$sql = "SELECT * FROM blog_post WHERE date_added > '2013-12-31' ";

// get the data from the database
$result = $con->query($sql);

// Now lets display it on the page.
while (($row = $result->fetch_array()) !== false){
    echo $row["id"]. " : ". $row["title"]. " @ ". $row['date_added']."\n";
}

This works, but I have no intention of rewriting this every time I need to connect to run a query. Actually, I think it is because of this style of writing that we still see people using the deprecated mysql_* functions. We need to move this piece of code out of sight and when we need to get data we don't care how to connect to database, just return the data. So let's create a class that does just that for us. We pass it the query and it returns the data, period!

class DataManager {

    private static $host = "localhost";
    private static $user = "user";
    private static $pass = "pass";
    private static $dbname = "testdb";
    private static $con;

    private static  function connect(){
        if (self::$con){
            return self::$con;
        }
        self::$con = new mysqli(self::$host,self::$user,self::$pass,self::$dbname);

        if (!self::$con){
            // throw error here using your own error handler
            // or you can use die.
            // But no respectable application would use die to handle errors
        }

        return self::$con;
    }


    public static function query($sql){
        $con = self::connect();

        $result = $con->query($sql);

        if (!$result){
            // Then there is probably an error in the query
            // Add a nice error handler here
            echo "He's dead jim... he is dead\nIn this query:\"<i>$sql</i>\"\n$con->error";
            exit;
        }
        if (isset($result->field_count) && $result->field_count == 0){
            return false;
        }

        $group = array();
        while (($r = $result->fetch_object()) != false){
            $group[] = $r;
        }
        return $group;
    }

    public static function escape($string){
        $con = self::connect();
        return $con->real_escape_string($string);
    }
}

So this is the gist of it. When you have to run a query now, all you care about is the query. You don't think about the process:

$sql = "SELECT * 
        FROM posts 
        WHERE date_published > '2013-12-31' 
        LIMIT 10";
$newPosts = DataManager::query($sql);

All the fields returned by the query will be available to you in the $newPosts array objects.

<?php foreach ($newPosts as $post):?>
    <h1><?php echo $post->title?></h1>
    <span><?php echo $post->date_published?></span>
    <p><?php echo $post->description?></p>
<?php endforeach; ?>

Note: In a real world example, you never print the data directly on the page. You make sure it is sanitized to prevent cross site scripting.

---