[SOLVED] Inserting a null value into mysql database

[fatrandy13]

i have a form that submits data to a php result page.  i then insert that data into a mysql database.  Null values ARE allowed and the problem is when i try to insert a null number value, it errors.

 

$ticket_number = $_POST['ticket_number'];
$another_number = $_POST['another_number'];

$query = "INSERT INTO ticket (ticket_number, another_number) values ($ticket_number, $another_number);
mysql_query($query) or die(mysql_error());

 

ticket number is required, but another_number is not.

error: youg have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near ')' at line 1

 

the ')' refers to the end of the close parenthasis in the sql statement.  the thing is, in mysql i could have just done:

 

insert into ticket (ticket_number, another_number) values (1, NULL);

 

how do i replicate that in a php statement.  the same thing happens with a null date type too.  There has to be a way!

 

thanks in advance.

-fatrandy13

[willpower]

you need to use single quotes around your $variables matey.

 

$query = "INSERT INTO ticket (ticket_number, another_number) values 'ticket_number', '$another_number');

mysql_query($query) or die(mysql_error());

[BlueSkyIS]

If it's not that, it may be that you didn't terminate $query with "

[fatrandy13]

if its a number thats being inserted, single quotes are not nessescary, i  did however put quotes and received this error:

 

Out of range value adjusted from column 'another_number' at row 1.

 

this error is because php is sending NULL and since i am now putting quotes around it, mysql is getting 'null' as a string.  i tested this by inserting it into a varchar() datatype, and i in fact got the string 'null' inserted into the database.  i need to actuall send a null value, not the string null and not a ' '.  Anyone else?

[pocobueno1388]

Did you try this?

 

<?php

$query = "INSERT INTO ticket (ticket_number, another_number) values ('$ticket_number', null)";
mysql_query($query) or die(mysql_error());

?>

[fatrandy13]

yes, it does accept a null value that way, but in some cases i do want to insert the value if its available. so i need the variable name present

[BlueSkyIS]

so.... in cases where you want to insert the value, use the value. in cases where you don't want to use the value, use null.

[pocobueno1388]

See if this gives you what you want.

 

<?php

$ticket_number = $_POST['ticket_number'];

if (!empty($_POST['another_number']))
   $another_number = $_POST['another_number'];
else
   $another_number = NULL;

$query = "INSERT INTO ticket (ticket_number, another_number) values ('$ticket_number', '$another_number')";
mysql_query($query) or die(mysql_error());

?>

[fatrandy13]

i run across the same issue as before, since php is returning NULL, and i am wrapping single quotes around it in the query, mysql actually get the string 'null'.  i tested this with both a integer datatype, in which case i get a type mismatch, and also with a varchar() datatype, in which the string 'null' gets inserted, in neither case an actual NULL value.

[fatrandy13]

just got it, thanks everyone for your help... i've been playing with this for a couple days now, this worked though:

 

if (!empty($_POST['another_number']))
   $another_number = $_POST['another_number'];
else
   $another_number = mysql_escape_string("NULL") ;

$query = "INSERT INTO ticket (ticket_number, another_number) values ('$ticket_number', $another_number)";

 

thanks everyone, this inserted NULL into my integer datatype in mysql, exactly what i wanted... again thanks

no quotes around $another_number though ...