php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #62405 Null values for insert/update statements
Submitted: 2012-06-24 22:34 UTC Modified: 2012-06-26 16:00 UTC
From: floriparob at gmail dot com Assigned:
Status: Not a bug Package: PostgreSQL related
PHP Version: 5.4.0.3 OS: Debian 64bit 3.2.0-2
Private report: No CVE-ID: None
 [2012-06-24 22:34 UTC] floriparob at gmail dot com
Description:
------------
Actually using PHP 5.4.0.3 but this was not in the drop down list above.

Null is a perfectly valid value for a column. However, PHP fails to submit the correct values when it builds the SQL query and submits it to the postmaster.
I don't have this problem with Java and the Postgres jdbc driver.
Consider the following DDL:-

create table table_a (
tablea_id serial not null primary key,
col_a varchar(20),
col_b integer,
col_c boolean default false);

In my class for table_a I have:-

update table_a set col_a = 'this->colA', col_b = this->colB,
col_c = this->colC where tablea_id = $something;

In my action I might set a value -- setcolA('some value');
If this->colB IS NULL, then PHP doesn't detect this fact, generates the following SQL which fails because of syntax errors.

update table_a set col_a = 'some value', col_b = , col_c = where tablea_id = 3;

It should generate:-

update table_a set col_a = 'some value', col_b = null, col_c = null where tablea_id = 3;

The failure to detect boolean's and treat them appropriately is another pain in the neck. You are using pg_query, you KNOW it's a Postgres database, you should KNOW that Postgres creates boolean columns as char(1), and that it uses a 't' to represent "true" and a 'f' to represent "false".
To overcome this failure I had to do the following in my class:-

$my_bool = this->colC? 't' : 'f';

and then use '$my_bool' instead of this->colC in the insert and update statements.

Imagine if you had dozens of boolean type columns in a table. A nightmare. It is quite common in applications where users belong to groups and a group has a set of privileges, and those privileges are usually held as boolean value's.



---
From manual page: http://www.php.net/function.pg-query#refsect1-function.pg-query-description
---


Actual result:
--------------
[Thu Jun 21 02:50:31 2012] [error] [client 127.0.0.1] PHP Warning:  pg_query(): Query failed: ERROR:  syntax error at or near ","\nLINE 5:            '', 'Kemly plus vistors to look at house', , 0,\n                                                              ^ in /var/www/timemanager.info/public_html/classes/AppointMents.php on line 133, referer: http://dev.timemanager.info/addchgappnts.html


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-06-25 15:30 UTC] rasmus@php.net
-Status: Open +Status: Feedback
 [2012-06-25 15:30 UTC] rasmus@php.net
What is generating your query? PHP's pg_query() call just takes a query string. 
It doesn't take an object and translate object properties like you seem to imply, 
so it sounds like you have a middle layer that is doing this and it sounds like 
the bug is in that middle layer, not in PHP.
 [2012-06-26 14:59 UTC] floriparob at gmail dot com
Hi Rasmus, not using Hibernate or anything similar.
The class has getter's and setter's, constructor, etc. and methods for fetching,
inserting and updating rows.

$query = "UPDATE table_a SET . . . etc.";
$result = pg_query($dbconn, $query);
if ( !$result ) {  return FALSE; } else { return TRUE; }

Works fine when all variables contain not null values.
How do you set a column to null if there is no bug in pg_query?
I can rewrite and used prepared statements but if the array of data for the 
placeholders contains a null value for one of the columns, is it going to be set 
to null?
 [2012-06-26 14:59 UTC] floriparob at gmail dot com
-Status: Feedback +Status: Open -PHP Version: 5.4.4 +PHP Version: 5.4.0.3
 [2012-06-26 15:59 UTC] rasmus@php.net
Sorry, this isn't a support forum. You represent NULLs in pg_query() the same 
way you do from the command line psql tool. So your question is really about 
your PHP userspace code and how you detect a NULL property. If they are real 
properties, you can use property_exists() or, since you mention you are using 
getters and setters, then you can easily handle the distinction between null and 
unset properties yourself.

For there to be a bug in pg_query() you need to show me a valid pg query that 
works from the command line but doesn't work in the pg_query() call.
 [2012-06-26 16:00 UTC] rasmus@php.net
.
 [2012-06-26 16:00 UTC] rasmus@php.net
-Status: Open +Status: Not a bug
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun May 19 14:01:32 2024 UTC