Help with inserting NULL values into mySQL using PHP

[tyooseph]

I'm currently developing a php form to interface with mySQL database.  Some form fields are not required and I would like to store NULL values in the database when no data is entered.  So, in the database, I set the fields' default values to NULL and it works when I insert a record using phpMyAdmin.  However, when I use my php script to insert the record using the INSERT statement, the field value enters as a blank instead of NULL.  Therefore, my searches for IS NULL produces no results.

I'm using a variable array to hold the form value - $defaults['name']=$_POST['name'] - then use the variable array in the VALUE portion of the INSERT statement.  I have found a difference in how php handles the INSERT statement.  (Assume no entry from user) if I use the variable in the VALUE portion of the INSERT statement the record is inserted with blanks, not NULL; However, if I omit the variable array from the VALUE portion altogether, the insert statement inserts the record and the default value, NULL, is entered into the field.

I don't want to create a complex script to determine if the fields are blank in order to omit those variables from the insert statement.  Is there a way to get the database to store NULL when no data is entered in the form field?

PS - I use echo within php to display the form and have tried single quotes, double quotes, and no quotes and nothing works.  I really need help with this.  Thanks.

[paul2463]

you dont need to create a complaex script you just need a simple function you call on the relevant field such as

[code]
<?php

function addNull($value)
{
if ($value != "")
{
$returnvar = $value;
}
else
{
$returnvar = NULL;
}
return $returnvar;
}


?>
[/code]

[tyooseph]

I've tried that - even when I set the value to NULL, it stores a blank in the database.

[paul2463]

try this one then
[code]<?php

function addNull($value)
{
if ($value != "")
{
return $value;
}
else
{
return NULL;
}
}


?>[/code]

[ShogunWarrior]

What you want to do is the following:
Set the value as such:
$variable = is_null($variable) ? "NULL" : $variable;

And insert as normal

[tyooseph]

Ok. I've tried to simplify my code by hardcoding the variable values.

  $serial=NULL;
  $id=112;
 
  $qry = "INSERT INTO vehicle (`agencyID`, `serial`) VALUES ($id, $serial)";
  $result = mysql_query ($qry) or trigger_error("Query: $qry\n<br />MySQL Error: " . mysql_error());

This results in no data entry at all.

If I change the value for $serial to a number, say $serial=59, then it inserts the record and places 59 in the field for serial and NULL for all my other fields that default to NULL.

What am I doing wrong??

[effigy]

MySQL needs to see the literal word "NULL", therefore PHP should set your variable to "NULL" or 'NULL' but not simply NULL.

Observe:

[code=php:0]
<?php
$null = NULL;
echo "null is $null";

echo '<hr>';

$null = 'NULL';
echo "null is $null";
?>
[/code]

[tyooseph]

That's great! When I hard code the NULL value using single quotes it transfers to the database as it should.

Now how do I incorporate this into my actual script.  I'm sure the problem is with the single and double quotes and the quote within quotes, but I haven't been able to find a good 'how to' on this subject.  When I try to remove the single quotes from the VALUES portion, the script encounters an error.

Any ideas? 

if (empty($_POST['serial'])){$defaults['serial']='NULL';}else{$defaults['serial']=$_POST['serial'];}

$qry = "INSERT INTO vehicle (`agencyID`, `serial`) VALUES (' " . $_SESSION['id'] . " ', ' " . $defaults['serial'] . " ') " ;

[effigy]

Each field needs to be checked for nulls, escaped, and quoted appropriately. Below is an except from the code [url=http://www.phpfreaks.com/forums/index.php/topic,121042.0.html]here[/url]:

[code]
### Escape each field that will be put into SQL.
foreach ($values as &$value) {
$value = $this->real_escape_string($value);
### NULL and digits should not be quoted.
if ($value != 'NULL' && ! preg_match('/^\d+$/', $value)) {
$value = '"' . $value . '"';
}
}
[/code]

[tyooseph]

When I use $value = ' " ' . $value . ' " '; from your example, the database stored a set of double quotes ("") and not a NULL value.  When I remove the quotes from around the variable in the $qry statement, the script generates an error (blank screen). 

I modified my code based on your example.  Did I miss something?

$defaults['serial']=mysql_escape_string($_POST['serial']);
if ($defaults['serial'] != 'NULL'){$defaults['serial']= ' " ' . $defaults['serial'] . ' " ' ;}

$qry = "INSERT INTO vehicle (`agencyID`, `serial`) VALUES (' " . $_SESSION['id'] . " ', ' " . $defaults['serial'] . " ') " ;

[Vampen]

Why dont make the default value in the database fields to NULL. So if the field is not filled it get the value NULL

CREATE TABLE TEST (
 test_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 post1 VARCHAR(200) NULL DEFAULT NULL,
 post2 VARCHAR(200) NULL DEFAULT NULL,
 PRIMARY KEY(test_id)
);

[effigy]

$qry is still enclosing every value in single quotes. You have to let the values do the work, not the query. If serial's value is being predetermined, $qry should be [tt]$qry = "INSERT INTO vehicle (`agencyID`, `serial`) VALUES (' " . $_SESSION['id'] . " '," . $defaults['serial'] . ")" ;[/tt]

[b]Vampen[/b]'s solution is also a good idea.

[tyooseph]

Removing the single quotes from serial in $qry results in a blank field in the database.  I'm sorry for harping on this, but I've been struggling with this for weeks; really need help effigy.  Please advise.

[tyooseph]

Also, Vampen, the database is set to default to NULL value, but it doesn't work when data goes into database using php INSERT statement.

[effigy]

Echo your final SQL statement for debugging purposes--what are you getting?

[Vampen]

It works fine with me. My code goes like this:

[code]$sql = "INSERT INTO annonse (

overskrift,adresse,epost,tidspunkt,postnummer,bildenavn,ant_rom,moblert,ledigfradato,pris,depositum,diverse
) VALUES ( "
. "'" . $_POST['overskrift'] . "', "
. "'" . $_POST['adresse'] . "', "
. "'" . $_POST['epost'] . "', "
. "'" . $klokka . "', "
. "'" . $_POST['postnummer'] . "', "
. "'" . $filnavn . "',"
. "'" . $_POST['ant_rom'] . "', "
. "'" . $_POST['moblert'] . "', "
. "'" . $_POST['ledigfradato'] . "', "
. "'" . $_POST['pris'] . "', "
. "'" . $_POST['depositum'] . "', "
. "'" . $_POST['diverse'] . "' "
. ")";[code][/code][/code]