First steps with PDO

22nd May 2004 @ 20:51 EDT

Developing PDO and releasing an alpha has sparked a lot of interest already (probably helped along by George ;-)) and we got our first "how does it work" e-mail today. As it happens, I've already written a intro to PDO for the OTN but George informs me that they can take a while to publish.

Meanwhile, to avoid being swamped by mail as the word gets out, here are a couple of sample PDO scripts to get you started. Please keep in mind that it is alpha software (although still works pretty well) and requires PHP 5 from CVS (RC3 will work too, but that isn't released until next week).

The API is "grown-up" by default; you get so called "unbuffered" result sets as standard and the prepare/bind/execute API is preferred, although there are some short-cuts already (and some more planned). Note that you don't need to do any quoting manually using bound parameters; it is handled for you. You do need to be careful with magic_quotes though (as always).

<?php
$dbh = new PDO('mysql:dbname=test;host=localhost', $username, $password);
// let's have exceptions instead of silence.
// other modes: PDO_ERRMODE_SILENT (default - check $stmt->errorCode() and $stmt->errorInfo())
//              PDO_ERRMODE_WARNING (php warnings)
$dbh->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION);
// one-shot query
$dbh->exec("create table test(name varchar(255) not null primary key, value varchar(255));");
?>

<?php
// insert some data using a prepared statement
$stmt = $dbh->prepare("insert into test (name, value) values (:name, :value)");
// bind php variables to the named placeholders in the query
// they are both strings that will not be more than 64 chars long
$stmt->bindParam(':name', $name, PDO_PARAM_STR, 64);
$stmt->bindParam(':value', $value, PDO_PARAM_STR, 64);
// insert a record
$name = 'Foo';
$value = 'Bar';
$stmt->execute();
// and another
$name = 'Fu';
$value = 'Ba';
$stmt->execute();
// more if you like, but we're done
$stmt = null;
?>

<?php
// get some data out based on user input
$what = $_GET['what'];
$stmt = $dbh->prepare('select name, value from test where name=:what');
$stmt->bindParam('what', $what);
$stmt->execute();
// get the row using PDO_FETCH_BOTH (default if not specified as parameter)
// other modes: PDO_FETCH_NUM, PDO_FETCH_ASSOC, PDO_FETCH_OBJ, PDO_FETCH_LAZY, PDO_FETCH_BOUND
$row = $stmt->fetch();
print_r($row);
$stmt = null;
?>

<?php
// get all data row by row
$stmt = $dbh->prepare('select name, value from test');
$stmt->execute();
while ($row = $stmt->fetch(PDO_FETCH_ASSOC)) {
    print_r($row);
}
$stmt = null;
?>

<?php
// get data row by row using bound ouput columns
$stmt = $dbh->prepare('select name, value from test');
$stmt->execute();
$stmt->bindColumn('name', $name);
$stmt->bindColumn('value', $value)
while ($stmt->fetch(PDO_FETCH_BOUND)) {
    echo "name=$name, value=$value\n";
}
?>

Oh, how do you get and install it?

Grab a PHP 5 snapshot from http://snaps.php.net (or HEAD from CVS).

   ./configure --prefix=/usr/local/php5 --with-zlib ....
   make
   make install
   export PATH="/usr/local/php5/bin:$PATH"
   /usr/local/php5/bin/pear install -f PDO
   [ now add extension=pdo.so to php.ini ]
   /usr/local/php5/bin/pear install -f PDO_MYSQL
   [ now add extension=pdo_mysql.so to php.ini ]
   /usr/local/php5/bin/php -m

There are other drivers; Search PECL for more. If you're running windows, just grab the win32 snap and the PDO dlls from PECL binaries for PHP 5.

Credits: thanks to Marcus, George, Ilia and Edin.

Please try to avoid asking too many questions about it; documentation will follow as soon as it is ready.

Wez Furlong in PHP.

9 Comments | Permalink

Complexity

23rd May 2004 @ 12:15 EDT

Although I like this API, I think the prepare()/bindParam()/execute() is overly complex for most cases. This solution is really good when it comes to batch inserts, for example, but usually, you'll only need to execute it once. Now imagine you're inserting into a table with 10 columns - that makes up ten lines of code consisting of

$stmt->bindParam(':name', $name, PDO_PARAM_STR, 64);

...plus two for prepare() and execute(). Seems like quite an amount of boilerplate.

How about a sprintf()-style shortcut something like this:

$dbh->prepare('insert into test (name, value) values (%64s, %64s)')->execute($name, $value);

The semantics used would be slightly different than sprintf():

%s prints and quotes a string, if NULL is passed, NULL appears
%64s defines the max. length (sprintf just overflows)
%d prints a numeric (or NULL)
%f prints a float (or NULL)

(could be continued)

by thekid@php.net in Comment.

or you could just use this...

23rd May 2004 @ 13:04 EDT

which should work with the current released PDO packages

<?php
$dbh->prepare('insert into test (name, value) values (:name, :value)')->execute(array(':name'=>$name, ':value'=>$value));
?>

Wez Furlong in Comment.

Thank you

25th May 2004 @ 23:47 EDT

One word, to both you, George (I just bought your book, btw!), and the rest of the people behind the scenes working to make this happen: Thanks.

by Roman in Comment.

thanks

26th May 2004 @ 11:34 EDT

this db-handler/abstraction/wrapper is really nice. seems to "adopt" some mysqlI-features/naming/methods

thanks

by Kai in Comment.

thank you

29th May 2004 @ 05:25 EDT

I am feeling very well

by bombshell in Comment.

My comments about the API

8th June 2004 @ 16:25 EDT

My humble opinions after some years fighting with DB:

exec() should be query(), widly adopted and won't be confused with execute()

I personally find this:

   // insert a record
   $name = 'Foo';
   $value = 'Bar';
   $stmt->execute();

a font of rare and hard to find bugs

it's nice to have datatype abstraction, but i don't quiet agree on having validation of them. First because it at least will be very weak and won't frees you the need of proper validation (for ex at forms). Second because it'd force you to return an error or even worse, to cut a string without any warning.

I find more clever to set the fetchmod e in query(). Doing it in $stmt->fetch(ASSOC) just ends calculating the mode each call, when there is no need. I don't know any case when switching the fetchmode on the fly could be useful.

imho, PDO_FETCH_BOTH, shouldn't be the default mode, it's just a waste of memory. I'd go with ORDERED as default. A setFetchMode() method is a need btw :-)

Common params needed by a query() function are: SQL, the args for preparing, the args for binding, the first row to fetch, the number of rows to fetch and the fetch mode. This api so won't let you any hole.

why did you changed the format of the DSN? it took ages for people to learn it! ;)

Thanks for your work in this stuff, finally one took it! Hope my comments helps on something.

Tomas V.V.Cox

by cox@idecnet.com in Comment.

firebird error connect

13th January 2006 @ 19:00 EDT

i use firebird 1.5 and easyphp1.6 for intranet and i try to migrate to wampserver5 1.6 (wampserver.com) and i can't connect to the firebird,

i try to conect to my table clientes where the client is 'codcli=24'

who to do the script

help me i need a script for list, insert, update and delete, if i can't migrate i'm ben force to continue to use easyphp1.6.

by cfaria@desenvolve.net in Comment.

sys admin

16th June 2006 @ 17:36 EDT

A few typos.. In the top example above where you have... $dbh->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION);

It should actually be... $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Same thing in your 4th example, where you have... while ($row = $stmt->fetch(PDO_FETCH_ASSOC)) {

It should be... while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

At least with PHP 5.1.4

Ray M.

by rxmccaf@youbetnet.com/Ray McCaffity in Comment.

yes, constants changed names

16th June 2006 @ 18:20 EDT

Those aren't typos, they were 100% correct at the time of writing.

Wez Furlong in Comment.