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);.