What is a good wrapper/framework/libraries for dealing with interface PHP applications to an SQL database?

Question

I have a table

$query=
"CREATE TABLE screenshot ".
"(screenshot_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ".
"source_video_id INT UNSIGNED NOT NULL, ".
"screenshot_file_name VARCHAR(128), ".
"x_res INT, ".
"y_res INT, ".
"time INT UNSIGNED);";
mysql_query($query);

Then I insert things into that table.

I often want to do an SQL query and iterate over the result of the query, but end up doing this.

//select all screenshots from video, by video id
    $q0=
    "SELECT * FROM screenshot ".
    "WHERE source_video_id = '$source_video_id' ".
    "AND x_res = 120 ".
    "AND y_res = 90 ".
    "ORDER BY time ASC;";
    $r0 = mysql_query($q0);
    $n0_num = mysql_numrows($r0);

//for each result
for($n0=0;$n0<$n0_num;$n0++) {
    $source_video_id = mysql_result($r0,$n0,'source_video_id');
    $time = mysql_result($r0,$n0,'time');
    $screenshot_file_name = mysql_result($r0,$n0,'screenshot_file_name');

    //do stuff for each returned result!
}

This is just ugly. To get the SQL query results, I have to write this for every column! $source_video_id = mysql_result($r0,$n0,'source_video_id');

I have to write an ugly loop, get the results for each row returned and do something for each result. Basically I want something like;

foreach($SQL_command) {
//Do for each result
}

I want the column variables for each row to be already set, so that I do not have to do

$source_video_id = mysql_result($r0,$n0,'source_video_id');

For each and every column I want to access!

I am sick of writing boiler plate code to do this for every single table in my data. Are there any frameworks or libraries that would make this less painful?

Answer 1

These are the very basics of a database abstraction layer. It's not hard to program your own, or you can use a generic library like Doctrine or Propel. Every notable PHP framework includes some form of database abstraction as well, you really just need to start using one.

Answer 2

If you have the PDO drivers enabled (as you should) you can use the single DB() method as a function from the phunction PHP framework. It was inspired by the DiBi database abstraction layer. The documentation is still underway, but I've posted a short summary in this answer.

function DB($query)
{
    static $db = null;
    static $result = array();

    if (is_null($db) === true)
    {
        if (preg_match('~^(?:mysql|pgsql):~', $query) > 0)
        {
            $db = new PDO(preg_replace('~^(mysql|pgsql):(?:/{2})?([-.\w]+)(?::(\d+))?/(\w+)/?$~', '$1:host=$2;port=$3;dbname=$4', $query), func_get_arg(1), func_get_arg(2));

            if (preg_match('~^mysql:~', $query) > 0)
            {
                self::DB('SET time_zone = ?;', 'GMT');
                self::DB('SET NAMES ? COLLATE ?;', 'utf8', 'utf8_unicode_ci');
            }
        }

        else if (preg_match('~^(?:sqlite|firebird):~', $query) > 0)
        {
            $db = new PDO(preg_replace('~^(sqlite|firebird):(?:/{2})?(.+)$~', '$1:$2', $query));
        }
    }

    else if (is_a($db, 'PDO') === true)
    {
        if (isset($query) === true)
        {
            $hash = md5($query);

            if (empty($result[$hash]) === true)
            {
                $result[$hash] = $db->prepare($query);
            }

            if (is_a($result[$hash], 'PDOStatement') === true)
            {
                if ($result[$hash]->execute(array_slice(func_get_args(), 1)) === true)
                {
                    if (preg_match('~^(?:INSERT|REPLACE)~i', $query) > 0)
                    {
                        return $db->lastInsertId();
                    }

                    else if (preg_match('~^(?:UPDATE|DELETE)~i', $query) > 0)
                    {
                        return $result[$hash]->rowCount();
                    }

                    else if (preg_match('~^(?:SELECT|EXPLAIN)~i', $query) > 0)
                    {
                        return $result[$hash]->fetchAll(PDO::FETCH_ASSOC);
                    }

                    return true;
                }
            }

            return false;
        }
    }

    return $db;
}

Your example query could be written as:

// connect to the MySQL server, do this on your config file or something
DB('mysql://host:port/database_name/', 'username', 'password');

// run the query!
$results = DB('SELECT * FROM screenshot WHERE source_video_id = ? AND x_res = ? AND y_res = ? ORDER BY time ASC;', $source_video_id, 120, 90);

foreach ($results as $result)
{
    print_r($result);
}

The above code uses prepared queries which means that you'll also be safe from SQL injection attacks.

PS: I'm biased here, since I'm the developer of the framework. If you run into any problems let me know.