Support for named placeholders and explicit data types (PDO::PARAM_*) #EnhancementRequest

[klor]

PDO allow programmers to work with named placeholders and explicit data types (PDO::PARAM_*) using bindValue() and bindParam(). However, the PDO syntax is quite verbose as shown in the examples below.

Example - Named parameters in an UPDATE:

// http://stackoverflow.com/questions/5012406/escaping-strings
$val = "Some string with an a'postrophe in it";
$stmt = $pdo->prepare('UPDATE table SET col = :val');
$stmt->bindParam('val', $val);
$stmt->execute();

Example - Named parameters in an INSERT:

// http://stackoverflow.com/questions/11142465/pdo-and-alphnumeric-strings
$stmt = $dbh->prepare("
    INSERT INTO `some_table` SET
        `order_number` = :order_number,
        `order_po` = :order_po
");
$stmt->bindParam(':order_number', $orderNum, PDO::PARAM_STR);
$stmt->bindParam(':order_po', $orderPO, PDO::PARAM_STR);

The enhancement request is to add support for binding named placeholders and explicit data types. Programmers will then be able to make a statement similar to this:

$sql  = "SELECT * FROM sometable WHERE name=:name AND foo>:baz AND bar<:baz";
$songs = DB::prepare($sql)
            ->bind('name', $_POST['name'], PDO::PARAM_STR)
            ->bind('baz', $_POST['baz'], PDO::PARAM_INT)
            ->execute()
            ->fetchAll();
echo $songs['name'];

Or, perhaps this instead:

$sql  = "SELECT * FROM songs WHERE artist=:artist AND foo>:baz AND bar>:baz";
$songs = DB::prepare($sql)->execute([ 'artist'=>$_POST['artist'], 'baz'=>$_POST['baz'] ])->fetchAll();
echo $songs['artist'];

Or, as Doll that comes with an interesting implementation of "inline type hinting":

$sth = $db->prepare("SELECT s:foo, i:bar, l:baz");
$sth->execute(['foo' => 'foo', 'bar' => 1, 'baz' => $fp]);

The Doll implementation is quite similar to SafeMySQL where each placeholder is marked with data type is in $sql = "SELECT * FROM table WHERE ?n LIKE ?s";

For inspiration on bind() functions, see

• https://github.com/gajus/doll
• http://stackoverflow.com/questions/6740153/simple-pdo-wrapper/6743773#6743773
• http://culttt.com/2012/10/01/roll-your-own-pdo-php-class/

[colshrapnel]

Thank you for the suggestions.

The idea behind this wrapper was to retain as much native PDO as possible. Thus, all the features supported by PDO are supported by this toy wrapper as well.

• For the named placeholders you can tell that they are fully supported already.

• Explicit binding is also available, by means of working through PDOStatement:

  $stmt = DB:prepare($sql);
  $stmt->bindValue('name', $_POST['name'], PDO::PARAM_STR)
  $stmt->bindValue('baz', $_POST['baz'], PDO::PARAM_INT);
  $stmt->execute();
  

  It is not that elegant as in your example, yet you don't need explicit binding very often - So I don't think it's a big deal.

Type-hinted placeholders as in Doll require sophisticated query parsing, as no placeholder-like data should be parsed out of string literals. I am working on it but it is not done yes.

And regarding that wrapper from cultt, it was one of inspirations for my recent Your first database wrapper's childhood diseases - it got almost every one.

[joshcangit]

Actually, it does support named parameters.

$stmt = DB::prepare("SELECT * FROM sometable WHERE name=:name AND foo>:baz AND bar<:baz")->execute([ 'name' => $_POST['name'], 'baz' => $_POST['baz'] ])->fetch();
print_r($stmt);

I've used this sort of coding in my project and it works just fine.

The problem seems to be with the bindParam() or bindValue() functions.
I've tested this with both named and positional parameters.

You will get this error if you bind 1 parameter.

Fatal error: Uncaught Error: Call to a member function execute() on bool . . .

Or either of these errors if you bind more than 1 parameter.

Fatal error: Uncaught Error: Call to a member function bindParam() on bool . . .
Fatal error: Uncaught Error: Call to a member function bindValue() on bool . . .

Or even this error if you don't use method chaining.

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound