querySingle function for PDO

Question

I have been refactoring a lot of my old code lately, and with that I have been switching from sqlite3 to mysql using PDO - sqlite3 has something called "querysingle" that allows you to return single responses (as a row or a single value) from a database table.

Anyways, I really liked the simplicity of this and that it all fits in 1 line of code and (IMO) doesn't harm the readability of the code.

I wrote a function in PHP that does a similar thing, but I wanted to make sure that I wasn't missing any edge cases, or see if I could improve the function in any way.

Here is my function:

function pdoQuerySingle($con, $query, $values = array(), $single = false) {
    if($values) {
        $stmt = $con->prepare($query);
        $stmt->execute($values);
    } else {
        $stmt = $con->query($query);
    }
    if($single) return $stmt->fetch()[$single];
    return $stmt->fetch();
}

You use it as follows:

$variable = pdoQuerySingle($pdo, "SELECT * FROM table WHERE id=?", [$id]);

As you can see this query works with prepared statements for security against SQL injection, though you can leave the 3rd param empty if you aren't passing any values to the query. The 4th parameter can be used to return only a specific entry from the query return as a string so if you wanted $variable to be set to a string from a column in the database, let's say, "name" you would do

$variable = pdoQuerySingle($pdo, "SELECT name FROM table WHERE id=?", [$id], "name");

Answer 1

There is a better way.

That's a very good function, but you can make it 100 times more useful by returning the statement.

function pdoQuery($con, $query, $values = array()) {
    if($values) {
        $stmt = $con->prepare($query);
        $stmt->execute($values);
    } else {
        $stmt = $con->query($query);
    }
    return $stmt;
}

Given there are two excellent things,

• "method chaining" (if some statement returns an object, you can call this object's method directly by adding -> )
• different fetch methods supported by PDOStatement

So in the end your current code will be changed a little, but you will get a lot of other neat ways for getting your data.

To get a row

$row = pdoQuery($pdo, "SELECT name FROM table WHERE id=?", [$id])->fetch();

to get a name

$name = pdoQuery($pdo, "SELECT name FROM table WHERE id=?", [$id])->fetchColumn();

But beside these two use cases there are many others!

• you can run a DML query as well:

  pdoQuery($pdo, "DELETE FROM table WHERE id=?", [$id]);
  

• fetch a list of records

  $data = pdoQuery($pdo, "SELECT * FROM table")->fetchAll();
  

• use all the formats supported by fetchAll()

• even get a row count

  $deleted = pdoQuery($pdo, "DELETE FROM table WHERE id=?", [$id])->rowCount();
  

As you can see, this new function is much more flexible and can be used to ease a lot more tasks