BobNZ2
asked on
php/postgresql inserting null values for timestamp and date datatypes
Hi, I'm trying to insert data from website to a postgresql database and am having problems with date and timestamp datatypes.I have a date field which can be null.
when a value is passed from a form the variable is checked to see if it is empty if it is
then null is assigned if (empty($RCCode)) { $RCCode='null';}
this works well with integers but not for timestamp or date .
if null is assigned to a date or timestamp field if (empty($DatPurch)) { $DatPurch='null';} then on the insert query the variable has to have no single quotes ie $DatPurch, or null value will cause an error.
However if there is a value in the variable $DatPurch='01/01/0001';
then it needs to be enclosed with single quotes '$DatPurch' or there is an error.
so how can i insert both, null and a valid value?
hope this makes sense.
D
when a value is passed from a form the variable is checked to see if it is empty if it is
then null is assigned if (empty($RCCode)) { $RCCode='null';}
this works well with integers but not for timestamp or date .
if null is assigned to a date or timestamp field if (empty($DatPurch)) { $DatPurch='null';} then on the insert query the variable has to have no single quotes ie $DatPurch, or null value will cause an error.
However if there is a value in the variable $DatPurch='01/01/0001';
then it needs to be enclosed with single quotes '$DatPurch' or there is an error.
so how can i insert both, null and a valid value?
hope this makes sense.
D
if empty($DatPurch)
{
$DatPurch = '';
}
else
{
$DatPurch = '31/05/2004';
}
and then
"INSERT INTO ...." . "$DatPurch" . "....
{
$DatPurch = '';
}
else
{
$DatPurch = '31/05/2004';
}
and then
"INSERT INTO ...." . "$DatPurch" . "....
ASKER
That would be alright for one instance but I have 8 date/timestamp fields in 1 table alone.
but thanks for the reply.
but thanks for the reply.
Ivanov_G,
Just out of curiosity,
Will that insert NULL value when the $DatPurch is empty....i think it will insert only '0000-00-00'.
Just out of curiosity,
Will that insert NULL value when the $DatPurch is empty....i think it will insert only '0000-00-00'.
ASKER
Ivanov: I have done something like that , I have:
if (empty($DatPurch)) { $DatPurch='01/01/0001';}
else{$DatPurch= $DatPurch;}
I just Think that is really messy and that there should be a better way.
if (empty($DatPurch)) { $DatPurch='01/01/0001';}
else{$DatPurch= $DatPurch;}
I just Think that is really messy and that there should be a better way.
ASKER
No it won't insert null value just '0001/01/01
you cant insert a null string in postgres for integer, timestamp, date, boolean
you cant insert a null string in postgres for integer, timestamp, date, boolean
ASKER
Ivanov:
if empty($DatPurch)
{
$DatPurch = '';
this would throw up an error:
Warning: pg_query() query failed: ERROR: Bad timestamp external representation '' in
if empty($DatPurch)
{
$DatPurch = '';
this would throw up an error:
Warning: pg_query() query failed: ERROR: Bad timestamp external representation '' in
Well man, I can not give you any suggestion about postgre. I didn't worked with it.
The universal solution I can offer is to create a trigger BEFORE INSERT and check what you have. If the date is 01/01/0001 the insert NULL, else the value you have. and your php code will look something like this:
if empty($DatPurch) {
$DatPurch = '01/01/0001';
}
else {
$DatPurch = '31/05/2004';
}
The universal solution I can offer is to create a trigger BEFORE INSERT and check what you have. If the date is 01/01/0001 the insert NULL, else the value you have. and your php code will look something like this:
if empty($DatPurch) {
$DatPurch = '01/01/0001';
}
else {
$DatPurch = '31/05/2004';
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hmmm not quite sure what your saying.
test the variable and apply value in the query ?
still leaves the problem of single quotes for value and no single quotes for null value
test the variable and apply value in the query ?
still leaves the problem of single quotes for value and no single quotes for null value
Hi
No it does not, try the code!
K_J
No it does not, try the code!
K_J
Cool K_J,
I just can't stop appreciating for your method.
:-)
I just can't stop appreciating for your method.
:-)
Hi ldbkutty
your welcome....
K_J
your welcome....
K_J
ASKER
Ok, I,m new to php so you may need to explain a bit more.
" . ( empty ( $DatPurch ) ? "null" : "'" . $DatPurch . "'" ) . "
why are you concatenating? why not just ($var1, $var2, '$var3'...
do you mean
" . ( empty ( $DatPurch )if (empty($DatPurch)) { $DatPurch='null';} else{$DatPurch= $DatPurch;}
and what does the : mean?
" . ( empty ( $DatPurch ) ? "null" : "'" . $DatPurch . "'" ) . "
why are you concatenating? why not just ($var1, $var2, '$var3'...
do you mean
" . ( empty ( $DatPurch )if (empty($DatPurch)) { $DatPurch='null';} else{$DatPurch= $DatPurch;}
and what does the : mean?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks hallvors, I still don't understand how junkies query is going to deal with the single quotes problem.
hopefully he will explain further.
hopefully he will explain further.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if($DatPurch == 'null')
$query = your query without single-quotes
else
$query = your query with single-quotes.
But may be there's a better way.