Using pg_execute, I can't use now() or null values

I'm using pg_prepare to prepare my SQL statements in PHP. When I try to pg_execute with a value that needs to be NULL, the function fails. Simi开发者_JAVA技巧larly, it will fail when using the SQL keyword now().

Code:

pg_prepare($connection, "insert_autosave", 'INSERT INTO autosave (category, editor, session_id, content, timeout, as_hook) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id');
$result = pg_execute($connection, "insert_autosave", array($category, $editor, $sid, $content, ($sto == "timeout" ? 'TRUE' : 'FALSE'), (is_numeric($asid) ? $asid : 'NULL')));

Error:

[pg_execute]: Query failed: ERROR: invalid input syntax for integer: "NULL" in actions.php on line 49

Doing a normal pg_query works. Any ideas on how to get around this?

---

I recall you can use PHP's null constant for that:

$result = pg_execute($connection, "insert_autosave", array(
   $category, 
   $editor, 
   $sid, 
   $content, 
   ($sto == "timeout" ? 'TRUE' : 'FALSE'), 
   (is_numeric($asid) ? $asid : null)
));

This will definitely not work with SQL functions though, you will have to embed these in the statement beforehand:

pg_prepare(
   $connection, 
   "insert_autosave", 
   'INSERT INTO autosave (some_date) VALUES (NOW())'
);

---

netcoder already explained how to take care of the NULL case. It's also possible to pass now() via a placeholder, using the special date/time input values "now", "today", etc. (See documentation for more info)

pg_prepare(
   $connection, 
   "insert_autosave", 
   'INSERT INTO autosave (some_date) VALUES ($1)'
);
pg_execute($connection, "insert_autosave", array('now'));
pg_execute($connection, "insert_autosave", array('2010-01-14 20:24:43'));

You can use $1::timestamp in cases where there's any ambiguity about the type.

NB! Never use this feature in directly SQL queries like 'now'::timestamp, because in this case the value will be parsed and remembered at prepare-time, and every execution will get the same time even if it's in a different transaction. NOW() is preferred in this case.