Pdo Prepared Statement - What Are Colons in Parameter Names Used For

PDO prepared statement - what are colons in parameter names used for?

Colons are required in the SQL statement, to indicate which identifiers are placeholders.

Colons in the execute() or bindParam() calls are optional. The documentation specifies them, but the implementation is clever enough to figure out what you mean if you leave them out (what else could you mean?).

Is the leading colon for parameter names passed to PDOStatement::bindParam() optional?

No, since the documentation doesn't mention this I think it's safe to assume that this behaviour isn't officially supported and shouldn't be relied upon.

However, it does actually happen to work (in PHP 5.3.24 at least) - internally a colon will be added to the parameter if it's missing (see ext/pdo/pdo_stmt.c:363 in the PHP 5.3.24 source code).

What does the colon mean in :name when using php bindParam

That maps to the named placeholder in the query. It is not required for the binding, the driver will auto-added it if not present.

In your code you have

$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
                     ^^^^^^^^^              ^^^^^^^

The driver reads anything with the : and trailing text as a placeholder. It then swaps that content with the value being bound, escapes all special characters, and quotes the string.

So then your bindparam has

:calories and :colour which match up to each of those. Let's say $calories had o'brien. When the query went to the DB it would be:

SELECT name, colour, calories
FROM fruit
WHERE calories < 'o\'brien'

PDO also supports unnamed placeholders which are just question marks ?. You bind these by position.

$sth = $dbh->prepare('SELECT name, colour, calories
        FROM fruit
        WHERE calories < ? AND colour = ?');

and then use a 1 because it is the first placeholder.

$sth->bindParam(1, $calories, PDO::PARAM_INT);

Additionally you can just pass all values to the execute function as an array and it will do the binding as well.

Regardless of bindparam or execute binding you have to address the binding by how you use it in the query. Unnamed is positional, named is by name.

What does a colon before a literal in an SQL statement mean?

The colon is a common character that indicates a placeholder for a variable value in a SQL statement. In this case, the those placeholders are getting replaced by the value of userId and project_id at runtime. This is great for avoiding SQL injection vulnerabilities.

How can I include a colon in a prepared statement?

Too bad I can't delete this accepted answer. Anyway, here is an edited answer:

I'm pretty sure it's the :'s in the date that are messing it up.

I am pretty sure it is not.

However, to be indeed sure, one have to have an error message from PDO.

PHP - PDO Prepared statment, Warning: PDOStatement::bindParam() expects at least 2 parameters

PDOStatement::bindParam ( mixed $parameter , mixed &$variable )

$parameter: Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.

$variable: Name of the PHP variable to bind to the SQL statement parameter.

You'll want to use:

$this->query->bindParam(':full_name', $this->order);

For more information read PDOStatement::bindParam.

PDO prepared statements binding

edit: Execute does work with named bindings so you could just edit your $data array like this:

$data = [':ver_weather' => $post["weathercondition"],
         ':ver_flash'   => $post["flashintense"],
         ':ver_earth'   => $post["earthrumble"]]

Note the : at the beginning of each key

Original answer below...

I think the issue is that you're trying to bind by name and I don't think PDOStatement supports named bindings. I'd recommend trying the following:

$data = [$post["weathercondition"], $post["flashintense"], $post["earthrumble"]];
$sql = "INSERT INTO `database`.`table` (`weather`, `flash`, `earth`)
        VALUES (?, ?, ?)";

$pdo->prepare($sql)->execute($data);

PHP: Array declaration and named variable

':hi' is a named variable and also an index/key in your array this can pass values to your database (Used in PDO).

'hi' is just an index/key in your array

---

Related Topics