Bug #62405 [Opn->Fbk]: Null values for insert/update statements
Edit report at https://bugs.php.net/bug.php?id=62405&edit=1
ID: 62405
Updated by: rasmus@php.net
Reported by: floriparob at gmail dot com
Summary: Null values for insert/update statements
-Status: Open
+Status: Feedback
Type: Bug
Package: PostgreSQL related
Operating System: Debian 64bit 3.2.0-2
PHP Version: 5.4.4
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2012-06-24 22:34:39] 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
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=62405&edit=1
Thread (5 messages)