[SOLVED] Empty integer field in a form

[clay1]

I have a form I am using to add a record to a postgresql database

 

I am getting an error when I try to insert an empty integer field.

 

I don't want to put 0 in the empty fields

 

How do I handle this? Do I need to test each integer field with an if statement?

 

Thanks

 

The insert statement:

 

INSERT into "markets" VALUES (DEFAULT, 'Test Market', 'email1', '', '', 'email2', 20, 10, 16, , , 'string', 35, 100, 25, 29, 30, 34, , , , )

 

"INSERT into \"markets\" VALUES (DEFAULT, '{$market}', '{$leadsemail}', '{$leadsemailcc}', '{$invoiceemail}', '{$invoiceemailcc}', {$fullprice}, {$halfprice}, {$pricelevel1}, {$pricelevel2}, {$pricelevel3}, '{$owner}', {$fullpriceagefrom}, {$fullpriceageto}, {$halfpriceagefrom}, {$halfpriceageto}, {$pricelevel1agefrom}, {$pricelevel1ageto}, {$pricelevel2agefrom}, {$pricelevel2ageto}, {$pricelevel3agefrom}, {$pricelevel3ageto})";

[Handy PHP]

Well, I don't use postgresql but in MySQL, you can set a field to accept NULL (blank) values.

 

However, if you can't do that, just replace zeros with NULLS for any output and replace NULLS with zeros to add to the database:

if($from_database == 0){
     $from_database = '';
}
if($to_database == '' || $to_database == NULL || !$to_database){
     $to_database = 0;
}

 

Handy PHP

[.josh]

You should be validating form info before putting it in the db in the first place, so putting in something if nothing exists shouldn't be a problem.. nonetheless, the default field value in your db as Handy mentioned would be better.

[clay1]

The fields are set to accept nulls but apparently I need to explicitly pass a NULL value to the database and not just an empty integer like: ,,

 

I can't replace zeros because there are cases where I want the value to be set as zero

 

So I am still rather stuck here

 

Pricelevel2 and 3 for example won't be used very often so I don't want to have to fill every single field in the form out every time

 

Well, I don't use postgresql but in MySQL, you can set a field to accept NULL (blank) values.

 

However, if you can't do that, just replace zeros with NULLS for any output and replace NULLS with zeros to add to the database:

if($from_database == 0){
     $from_database = '';
}
if($to_database == '' || $to_database == NULL || !$to_database){
     $to_database = 0;
}

 

Handy PHP

[clay1]

I guess I am mostly wonder how to validate this stuff

 

I have about a 2 dozen fields on this form do I need to write an if(empty($ check for each field or is there a way to do this in less code?

[.josh]

The fields are set to accept nulls but apparently I need to explicitly pass a NULL value to the database and not just an empty integer like: ,,

 

But is it set up to make it null by default?

 

 

Anyways, dunno how you have your form setup but in general you could just for instance loop through the $_POST array.

 

foreach($_POST as $key => $value) {
  if (trim($value) == '') {
    $_POST[$key] = 'null';
  }
}

[clay1]

That worked thank you