I prefer:

    $stmt = $somePDOObject->prepare("INSERT INTO folks " .
    "(name, addr, city) values (:name, :addr, :city);");
    $stmt->execute(
      array(
        ":name" => $name,
        ":addr" => $addr,
        ":city" => $city));

since it treats the statement as a function call, rather than some chunk of state that you manipulate.

Totally forgot you could do that. Way cool.

Perhaps; to avoid any verbosity for any real code I wrap PDO in something like IdiORM and Paris (http://j4mie.github.com/idiormandparis/) or my fork called Dakota (https://github.com/powerpak/dakota). My point was that you can write a PHP + MySQL toy example with PDO that is just as simple as the mysql_query() garbage but is a million times safer in the long run.

Oh, totally, don't get me wrong. For a one-off query I might write what you did and think nothing of it. It's vastly better than the alternative.

If you go the resilient route with everything you end up with code that looks like Java code. It's not usually worth it, as when the query changes, odds are you'll be looking at the parameters, and will notice changes. Not true for large queries, of course, but for beginners? They're writing things like the above, where the entire process fits in a single eyefull. Making it more verbose from the get-go means they're more likely to just concatenate strings.

> If you go the resilient route with everything you end up with code that looks like Java code.

Meh. Prepared statements are useful when you'll use the it multiple times through rebinding, and for one-shots it's easy enough to have a function taking a parameterized query and an array and doing the annoying crap internally.

Then you just write:

    $result = executeSQL($somePDOObject,
        "INSERT INTO folks (name, addr, city) values (:name, :addr, :city);",
        array("name" => $name, "addr" => $addr, "city" => $city);

This is actually fairly close to what I do in my own code (I actually extend the PDO object, but same idea). The example I gave was just using the base PDO library instead.

What would be the difference, apart of the named parameters? I mean, does this add any security?

> I mean, does this add any security?

No, or he would have mentioned it.

It's clearer (especially as the number of parameters increase) and easier to change (in order to e.g. add a new parameter)

It's not more clear when you're trying to examine the codebase as a whole.

Of course it is. Names are clarity. Position-based binding sucks. I wrote a quick extension to java.sql.PreparedStatement at work one evening this week to enable named parameters specifically because of this.

Named parameters also make it easier to re-use queries (have a queries file or something) because you're much less apt to immediately break something by changing a query if everything is explicitly bound via names. And you can make the statements as a whole more terse if you'd like; I intentionally wrote it verbosely.

In the first example above, you have one place where you have position based binding that you could break while reusing or refactoring.

In your example, you have one place where you have position based binding that you could break while reusing or refactoring.

In my experience, people are vastly less likely to rename a variable than move it to a different place in a query.

There's also the proclivity for people to want to reuse the same value in multiple places in a query. Name-based binding makes that a lot more readable and straightforward.

resilient to what?

The ? ? ? example is readable when you're inserting three values; it becomes really really easy to leave off, or add on an extra, argument when you're inserting fifteen or fifty values.

Think fast: is everything in the code below correct? I've already forgotten whether I've added an extra ? or left one off, and I just typed this 30 seconds ago.

    $sth = $dbh->("INSERT INTO a_table (lorem, ipsum, dolor, sit, amet, consectetur, adipiscing, elit, donec, malesuada, purus, et, tellus, dignissim, tristique, in, ipsum, neque, ultricies, quis, hendrerit, ac, pulvinar, eget, enim)
    ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
    $sth->execute(array($lorem, $ipsum, $dolor, $sit, $amet, $consectetur, $adipiscing, $elit, $donec, $malesuada, $purus, $et, $tellus, $dignissim, $tristique, $in, $ipsum, $neque, $ultricies, $quis, $hendrerit, $ac, $pulvinar, $enim, $eget));

Trick question; you've specified the `ipsum` column twice anyway. ;)

I think it's a good idea to be abstracting this away anyway. you should have some light ORM that hides actual sql statements away, that way you don't run into this problem.

also, the named version above can have query length issues I think.