. . . and find myself looking back at previous projects or other websites just to remember how to select rows from a database . . .

FYI, we all do that.

You had a problem with the PDO API and now you have two problems. My best and strongest suggestion is this: If you want a simpler/different database API, do not roll your own. Search http://packagist.org for an ORM or a DBAL that looks good and use it instead of PDO.

Other people have already done this work for you. Use their work and focus instead on whatever awesome thing is unique to your app. Work smart, not hard and all that.

Writting a wrapper, should start form connecting the DB, and all the possible method could be wrapped. Passing connection to the query method, doesn't look good.

A very rough example would be the code bellow, I strongly do not suggest this mixture, but it will give you the direction.

You connection should be made either from the constructor, or from another method called in the constructor, You can use something like this:

public function __construct($driver = NULL, $dbname = NULL, $host = NULL, $user = NULL, $pass = NULL, $port = NULL) {
        $driver     = $driver   ?: $this->_driver;
        $dbname     = $dbname   ?: $this->_dbname;
        $host       = $host     ?: $this->_host;
        $user       = $user     ?: $this->_user;
        $pass       = $pass     ?: $this->_password;
        $port       = $port     ?: $this->_port;
        try {
            $this->_dbh  = new PDO("$driver:host=$host;port=$port;dbname=$dbname", $user, $pass);
            $this->_dbh->exec("set names utf8");
        } catch(PDOException $e) {
            echo    $e->getMessage();  
        }
    }

So you can either pass connection credentials when you instantiate your wrapper or use default ones.

Now, you can make a method that just recieves the query. It's more OK to write the whole query, than just pass tables and columns. It will not make a whole ORM, but will just make the code harder to read.

In my first times dealing with PDO, I wanted everything to be dynamically, so what I achieved, later I realized is immature style of coding, but let's show it

public function query($sql, $unset = null) {
    $sth = $this->_dbh->prepare($sql);

    if($unset != null) {
        if(is_array($unset)) {
            foreach ($unset as $val) {
                unset($_REQUEST[$val]);
            }
        }
        unset($_REQUEST[$unset]);
    }

    foreach ($_REQUEST as $key => $value) { 
         if(is_int($value)) {
             $param = PDO::PARAM_INT;
         } elseif(is_bool($value)) {
             $param = PDO::PARAM_BOOL;
         } elseif(is_null($value)) {
             $param = PDO::PARAM_NULL;
         } elseif(is_string($value)) {
             $param = PDO::PARAM_STR;
         } else {
             $param = FALSE;   
         }
         $sth->bindValue(":$key", $value, $param);
    }

    $sth->execute();
    $result = $sth->fetchAll();

    return $result;

}

So what all of these spaghetti does?

First I though I would want all of my post values to be send as params, so if I have

input name='user'
input name='password'

I can do $res = $db->query("SELECT id FROM users WHERE username = :user AND password = :password");

And tada! I have fetched result of this query, $res is now an array containing the result.

Later I found, that if I have

input name='user'
input name='password'
input name='age'

In the same form, but the query remains with :user and :password and I submit the form, the called query will give mismatch in bound params, because the foreach against the $_REQUEST array will bind 3 params, but in the query I use 2.

So, I set the code in the beginning of the method, where I can provide what to exclude. Calling the method like $res = $db->query("SELECT id FROM users WHERE username = :user AND password = :password", 'age'); gave me the possibility to do it.

It works, but still is no good.

Better have a query() method that recieves 2 things:

• The SQL string with the param names
• The params as array.

So you can use the foreach() logic with bindValue, but not on the superglobal array, but on the passed on.

Then, you can wrap the fetch methods

public function fetch($res, $mode = null)

You should not directly return the fetch from the query, as it might be UPDATE, INSERT or DELETE.

Just pass the $res variable to the fetch() method, and a mode like PDO::FETCH_ASSOC. You can use default value where it would be fetch assoc, and if you pass something else, to use it.

Don't try to be so abstract, as I started to be. It will make you fill cracks lately.

Hum... IMHO I don't think you should try to wrap PDO in functions, because they're already "wrapped" in methods. In fact, going from OOP to procedural seems a step back (or at least a step in the wrong direction). PDO is a good library and has a lot of methods and features that you will surely lose if you wrap them in simple reusable functions.

One of those features is the BeginTransaction/Rollback (see more here)

Regardless, In a OOP point of view you can decorate the PDO object itself, adding some simple methods.

Here's an example based on your function

Note: THIS CODE IS UNTESTED!!!!

class MyPdo
{
    public function __construct($conn)
    {
        $this->conn = $conn;
    }
    

    public function pdo()
    {
        return $this->conn;
    }

    public function selectAllById($table, $id = null)
    {
        $query = 'SELECT * FROM :table';
        $params = array('table'=>$table);
        if (!is_null($id)) {
            $query .= ' WHERE id = :id';
            $params['id'] = $id;
        }
        $r = $this->conn->prepare($query)
                  ->execute($params)
                  ->fetchAll();
        //More stuff here to manipulate $r (results)
        return $r;
    }

    public function __call($name, $params)
    {
        call_user_func_array(array($this->conn, $name), $params);
    }
}

Note: THIS CODE IS UNTESTED!!!!

ORM

Another option is using an ORM, which would let you interact with your models/entities directly without bothering with creating/destroying connections, inserting/deleting, etc... Doctrine2 or Propel are good bets for PHP.

Howeveran ORM is a lot more complex than using PDO directly.