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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: floriparob at gmail dot com
New email:
PHP Version: OS:

 

 [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

Pull Requests

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: Mon Dec 02 16:01:33 2024 UTC