20
Jan
2011
Drew

PHP MySQL Database Object

Below you will find another generic PHP object developed by Arizona LAMP developer Drew Ewing. This object can be used as a basis of communication with a MySQL database server.
<?php
class Database
{
    function __construct ($host, $user, $pass, $database)
    {
        if (($this->link = mysql_connect($host, $user, $pass)) !== false) {
            $db = mysql_select_db($database, $this->link);
            if(!$db) {
                throw new Exception("Unable to select database: '" . mysql_error($this->link) . "'");
            }
        } else {
            throw new Exception("Unable to connect to database: '" . mysql_error($this->link) . "'");
        }
    }
    function query ($query)
    {
        if ($this->link) {
            $res = mysql_query($query, $this->link);
            if (! $res) {
                throw new Exception("An unexpected error has occurred: '" . mysql_error($this->link) . "'");
            } else {
                return $res;
            }
        }
    }
    function fetchColumn ($query)
    {
        if ($this->link) {
            $res = $this->query($query);
            return mysql_result($res, 0);
        }
    }
    function fetchAssoc ($query)
    {
        if ($this->link) {
            $res = $this->query($query);
            $return = array();
            if ($res) {
                while (($row = mysql_fetch_assoc($res)) !== false) {
                    $return[] = $row;
                }
            }
            return $return;
        }
    }
    function fetchRowAssoc($query) {
        if($this->link) {
            $res = $this->fetchAssoc($query);
            if($res &amp;&amp; !empty($res)) {
                return $res[0];
            }
        }
    }
    function fetchRowArray($query) {
        if($this->link) {
            $res = $this->fetchArray($query);
            if($res &amp;&amp; !empty($res)) {
                return $res[0];
            }
        }
    }
    function fetchArray ($query)
    {
        $res = $this->query($query);
        $return = array();
        if ($res) {
            while (($row = mysql_fetch_array($res)) !== false) {
                $return[] = $row;
            }
        }
        return $return;
    }
}
Usage of the object would look like the following:
<?php
    $db = new Database('localhost', 'root', '', 'test_db');
    // The following query will allow you to select an associative array of the 15 newest entries, and guarantee they are from the last 24 hours.
    $rows = $db->fetchAssoc('SELECT id, title, content FROM test_entries WHERE entry_date > '. (time() - 86400) . ' ORDER BY entry_date DESC LIMIT 0,15');
    foreach($rows as $row) {
         // Use the selected array data as such: $row['title'], etc...
    }

    // The following would select a single column, in this example the number of entries in the table
    $entries = $db->fetchColumn('SELECT COUNT(1) FROM test_entries WHERE 1');
    echo 'There are '. $entries . ' total entries in the database';
?>
Of course, usage could vary. This is a basic implementation. From here you could add query building utilities, alias functions to automatically update or insert given an array and an identifier. I have developed such an object as an extension of this one, and will post it soon.
Category: 
PHP
SQL
Download: 
The database object in easy to download text format