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?

Assign the result to an array! `mysql_fetch_array($result)` – David Yell Jul 16 '10 at 08:11 — David Yell, Jul 16 '10 at 08:11

score 0 · Answer 1 · answered Dec 24 '12 at 23:10

I use RedBean in all my projects and would recommend it without hesitation. The main reasons being:

Minimum configuration required. I don't have to map the database schema into a YAML or JSON file, simply put in the connection parameters and go.
Elegant and easy to understand usage syntax.
Lots of features such as caching and tree relationships.
Pretty good performance.

And here's an example of using it:

$book = R::dispense('book');
$book->title = 'Gifted Programmers';
$book->author = 'Charles Xavier';
$id = R::store($book);

deceze · Answer 2 · 2010-07-16T06:01:18.280

0

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.

edited Jul 16 '10 at 06:01

answered Jul 16 '10 at 05:51

deceze

510,633
85
743
889

score 0 · Answer 3 · edited May 23 '17 at 12:19

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.

score 0 · Answer 4 · answered Jul 16 '10 at 08:01

One can suppose I'm a fan of Kohana, but I really love the thing. Get the Kohana 3 and put there the Sprig ORM (it's a fork from original Sprig ORM, but with additional ‘sugar’ :) instead of native Kohana's one. You'll understand how pretty they are together. You'll can access to your tables like this code shows:

//just the basics, updating existing record
$screenshot = Sprig::factory('Screenshot', $id)->load();
$screenshot->x_res = 240;
$screenshot->y_res = 260;
$screenshot->update();

//creating new one
$screenshot = Sprig::factory('Screenshot');
$screenshot->x_res = 300;
$screenshot->y_res = 250;
$screenshot->create();

Additional link to the discussion of the Sprig fork: http://forum.kohanaframework.org/comments.php?DiscussionID=4368

Hope, it'll help you.

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

4 Answers4