Differences between using ? and :param in prepare statement

Question

Let's say I want to select records where Id = 30. Prepared statements allow two ways of binding parameters:

question marks

$id = 30;
$q = $conn->prepare("SELECT * FROM pdo_db WHERE id > ?");
$q->execute(array($id));  // Here above ID will be passed 

named parameters

$sth = $conn->prepare("SELECT `id`, `title` FROM `pdo_db` WHERE `id` > :id");
$sth->execute(array(
  ':id' => 30
));

Both are working fine and give accurate results but I am not able to get the exact differences between these two nor when I should use one or another?

Answer 1

Question mark parameters are called positional parameters.

Parameters defined with : and a name are called named parameters.

The rule is that you can't mix the two in your prepared statement.

Positional parameters work in a simple way - if you have two positional parameters, you can specify an array with two elements. Array values will be bound in order as they appear in the array.

Named parameters are a bit trickier, they don't have to be bound in order they appear. You can also repeat one named parameter multiple times in the statement, but you can bind it only once to pass the value - that last part works when PDO is set to emulation via $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);.

Answer 2

This seesm to be more of a preference based question as there isn't a strict right or wrong answer.

Personally I avoid ? as it's tightly tied to the order of parameters. Although theoretically you shouldn't be able to mess this up by adding or removing binding a single binding, it just seems like it's asking for trouble. It is (slightly) less work though.

:name is more concise and is tightly bound to the identification of bindings, not arbitrarily to the order. It involves a (tiny) bit more work but it is much easier to debug, and less prone to mistakes.

I prefer being (slightly) concise over writing (slightly) less code.

Answer 3

These are different placeholders

   ? -- > question mark placeholders
   :name --> named Placeholders

The difference between named and question mark placeholders is that with question mark placeholders you'll have to take care about the order in which they will be bound to the query.