note 115062 added to function.pg-query-params

pg_query_params() *does* accept NULLs. They will automatically be transformed, correctly, into SQL NULL. Thus, for example: <?php $sql = "UPDATE tbl_example SET column_a = $1, column_b=$2"; $params = array(NULL, 42); $result = pg_params ($sql, $params); //is equivalent to: $result = pg_query ("UPDATE tbl_example SET column_a = NULL column_b = '42')"; //and not, as one might fear, either of these (incorrect) things: // ... column_a = '' ... // ... column_a = 'NULL' ... ?> Note that you can use NULLs this way in an UPDATE or INSERT statement, but NOT in a WHERE clause. This isn't a restriction of pg_query_params(), but rather it is a consquence of the SQL language. So, if you want a query of the type: <?php //depending on data, the where-test parameter may or may not be NULL //the following is WRONG for $1. $sql = "SELECT * from tbl_example WHERE column_a = $1 and column_b = $2"; $params = array(NULL, 42); $result = pg_params ($sql, $params); ?> This will fail as invalid SQL: because you should use "= 42" but "IS NULL". The solution is to use the SQL construct "IS [NOT] DISTINCT FROM". <?php $sql = "SELECT ... WHERE column IS NOT DISTINCT FROM $1" $params = array (42); //this works, the same as "where column = 42" $params = array (NULL); //this works, the same as "where column is null" ?> (Aside: though this is annoying, the behaviour is correct. There is a postgresql compatibility option "transform_null_equals", but it won't help you here, even though you might expect it to.) ---- Server IP: 72.52.91.14 Probable Submitter: 82.10.237.122 ---- Manual Page -- http://php.net/manual/en/function.pg-query-params.php Edit -- https://master.php.net/note/edit/115062 Del: integrated -- https://master.php.net/note/delete/115062/integrated Del: useless -- https://master.php.net/note/delete/115062/useless Del: bad code -- https://master.php.net/note/delete/115062/bad+code Del: spam -- https://master.php.net/note/delete/115062/spam Del: non-english -- https://master.php.net/note/delete/115062/non-english Del: in docs -- https://master.php.net/note/delete/115062/in+docs Del: other reasons-- https://master.php.net/note/delete/115062 Reject -- https://master.php.net/note/reject/115062 Search -- https://master.php.net/manage/user-notes.php