How can I include a colon in a prepared statement?

I've got the following query which works great when pasted into开发者_StackOverflow an SQL client but doesn't work when I'm executing it through PDO.

INSERT INTO mdt_order (`id`,`reference`,`customer_id`,`order_datetime`)
VALUES (NULL,'786d98e7','1960','2011-03-25 08:59:34')

I'm pretty sure it's the :'s in the date that are messing it up. The order_datetime field actually gets set to 2011-03-25 08??

How can I include colons in my PDO queries?

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.

I've never seen this behaviour. PDO ignores ':xyz' syntax enclosed in SQL string quotes. You might have a buggy version though.

But anyway, if you are using prepared statements, then you really ought to be using bound parameters anyway:

$pdo->prepare("INSERT INTO mdt_order (`id`,`reference`,`customer_id`,`order_datetime`) VALUES (?,?,?,?)")
    ->execute(array(NULL, '786d98e7', '1960', '2011-03-25 08:59:34'));

If you want to keep using raw strings, then use ->query() instead. This will never even attempt to look for :params, so avoid such problems.

$query = "
    INSERT INTO
        mdt_order (`id`, `reference`, `customer_id`, `order_datetime`)
    VALUES
        (NULL, :reference, :customer_id, :order_datetime)";

$db = new PDO("mssql:host=sqlserver;dbname=database", "username", "password");

$statement = $db->prepare($query);

$parameters = array(
    ":reference" => $reference,
    ":customer_id" => $customer_id,
    ":order_datetime" => $order_datetime
);

$statement->execute($parameters);

:xyz is named parameter in PDO.

That means if you use pdo->prepare("SELECT :fields FROM :table :where");

You can statement->execute(array(":fields"=>$fields,":table"=>$table,":where"=>$where));