I have a sql insert query as below:

$sql = ("INSERT INTO ticket(ticketid, type, subject, issue, phone, email, clientcode, submittedby,submitteddate,status,addedby,priority)
values(nextval('ticketidseq'), '$problemtype', '$subject', '$issue', '$phone', '$email', '$company', '$name', now(),'$status', '$p[staffid]','$priority')");

The problem is here that phone field which is integer. When there is no value typed in by the user it prints error message : pg_atoi: zero-length string.

I have tried to make the phone field to allow null value with this statement :

ALTER TABLE ticket DROP COLUMN phone;
             ALTER TABLE ticket ADD COLUMN phone integer NULL;

but no success.

Please help to solve this problem. I'm using postgresql.

Regards,
ganesh

why don't you rewrite to something like this:

<?

if(strlen($phone) == 0 || empty($phone)) {
$phone = 0; //if the field is a INTEGER
//$phone = "no phone"; if the field is a VARCHAR
}

$sql = ("INSERT INTO ticket(ticketid, type, subject, issue, phone, email, clientcode, submittedby,submitteddate,status,addedby,priority)
values(nextval('ticketidseq'), '$problemtype', '$subject', '$issue', $phone, '$email', '$company', '$name', now(),'$status', '$p[staffid]','$priority')");

?>

try changing phone field type to VARCHAR (for example notation "203-349-4343" would require VARCHAR field).
also if i am not mistaken, if you want to insert a interger variable to integer field in a database, you don't have to use single or double quotes...

:D:D:D:D


the genuine genius

Thanks, I will give it a try.

another problem is that you're surrounding your integer value in quotes. When inserting into a DB, it's not necessary to quote your integer values, nor is it allowed to insert 'NULL' when you mean NULL. The first, 'NULL', is the text-string "NULL", which won't be allowed into an integer column.

If you're sticking with an integer approach, I suggest you remove the single quotes from the query around $phone. At the top of the script, do something like:

$phone = isset($_POST['phone'])? $_POST['phone'] : 'NULL';

That way, your query will either insert the phone number or NULL.


You can try the same approach with text-based columns, too. Just make sure to add the single-quotes at the testing part of the script:

$phone = isset($_POST['phone'])? "'{$_POST['phone']}'" : 'NULL';



Take care,

Nik
http://www.bigaction.org/