Change the return format on PHP's PDO Select

Question

I am writing my own PDO wrapper to make my life easier and a fair amount safer.

A standard query looks like:

$user = $db->select('users')
           ->eq('twitter_id', $twitter_id)
           ->limit(1)
           ->prepare()
        ->exec();

Generates this query:

SELECT * FROM users WHERE twitter_id = :twitter_id LIMIT 1

This works perfectly fine as I, currently, want it. Where I am running into a problem is when I have a query to return multiple rows.

My apps stores some dynamic settings that I want to grab and use in one pass and I can do that by running a query like:

$share_datas = $db->select('settings', 'setting, value')
                  ->prepare()
               ->exec();

Which generates:

SELECT setting, value FROM settings

Which returns:

Array
(
    [0] => Array
        (
            [setting] => since_id
            [value] => 17124357332
        )

    [1] => Array
        (
            [setting] => last_dm
            [value] => 1271237111
        )
)

The function prepare() puts the pieces together for the query and the function exec() binds the params and returns the array.

    function exec()
    {
//      echo 'vars: <pre>'.print_r($this->sql_vars, true).'</pre>';
        $stmt = $this->dbh->prepare($this->sql_last_query); 
        foreach($this->sql_vars as $key => $val)
        {
            if('date_time' === $key) continue;
            $bind = $stmt->bindValue($key, $val);
        }
        $stmt->execute();
        $this->sql_vars = array();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

Now to the question: Is there a way that I can change exec() or the query itself so that it can return an array that returns like below and avoids any additional loops?

Array
(
     [since_id] => 17124357332
     [last_dm]  => 1271237111
)

Answer 1

No problem with some simple array functions.

$in = $db->exec();
$out = array();
foreach( $in as $row )
    $out[ $row['setting'] ] = $row['value'];

If you need a more general function, you'll have to describe the transformation clearer.

Answer 2

The answer is likely going to be either:

1. Creating multiple versions of your exec method with different return behavior, or
2. Having exec simply perform the execution and store the statement handle, then have fetching the data be a separate method.

I've found the following convenience methods handy, in addition to your current array of hashes:

• Query "one": The first column in the first row as a scalar (for things like SELECT COUNT(*))
• Query "list": The first column of all rows as an indexed array (for things like SELECT id FROM ...))
• Query "pairs": The first two columns of all rows as a hash (for your current problem)
• Query "insert id": The last generated row id as a scalar (autoincrement in MySQL, sequence in Postgres, etc)

These are all occasionally convenient things that PDO (and most other database adapters) simply don't have built-in flags to handle.