Send NULL variable to MySQL

[XeroSiS]

Basically I am having a lot of trouble figuring out how I can assign NULL to a variable and then send NULL to mysql.

The case is I want to edit some users, and the filed date of birth is a mandatory field, so if they update it then OK, but if they do not it stays NULL.

Now in my edit function I have a varibale that say:

$born_on = $_POST['born_on'], which is basically an empty textbox.

when I update mysql it saves that is 00-00-0000 00:00:00

Now even when I put an if condition making $born_on = NULL it still saves the 00-00-0000 00:00:00, how can I change that back to a NULL whenever I make the textbox empty without having to write an extra query just for that field in an if condition.

[tirna]

maybe try something like this:

 
if(empty($_POST['born_on'])) $born_on = null;

and/or set the value of the DOB column in your sql update query to null.

[dk_zero-cool]

#2

Now even when I put an if condition making $born_on = NULL it still saves the 00-00-0000 00:00:00

#1 The problem is that you haven't assigned an allow null rule to your column. You need to mark the NULL field for your column to allow a null value.

[NogDog]

Don't confuse the PHP null with the SQL NULL. When you use the PHP null in a string (such as your SQL string) it will be cast to a zero or empty string (I don't remember which off the top of my head). But to set a column's value to null in SQL, you need to use the NULL keyword in the query. So very simplistically (with no error-checking or SQL injection escaping), you might do something like:

// note use of string listeral "NULL" instead of PHP null...
// ...plus adding single quotes around the actual value if supplied
$born_on = (empty($_POST['born_on'])) ? "NULL" : "'".$_POST['born_on']."'";
$query = "INSERT INTO `table_x` (`born_on`) VALUES ($born_on)"; 

[tirna]

The case is I want to edit some users, and the filed date of birth is a mandatory field, so if they update it then OK, but if they do not it stays NULL.......

When the html form loads, I would display the current DOB in the database as the default value in the form's textbox. This default value is then sent to the server if the user has not changed it.

Then as part of your server side form data validation, why not redirect the user back to the html form if they have entered an invalid DOB in the textbox?

[XeroSiS]

Date of birth is not a mandatory field, but I don't want to make different SQL scripts considering cases where date of birth is assigned / date of birth not assigned. Because this is one of many non-mandatory fields basically.

The issue I am facing is that basically I need to somehow make a php variable equal exactly to the MYSQL null basically.

I have yet to try nogdog's approach.

[tirna]

Date of birth is not a mandatory field.....

You have now changed what you said in your first post.

I assume you have set up the column in the db table to allow null values.

[XeroSiS]

OH sorry, I meant to say it is not a mandatory field, my apologies for this. Any reply now that you know the case?

[sonicpaint]

I have a similar problem and can't seem to get rid of the 0000-00-00 in the database column even event when I've set it to Null.
example: `second_date` date DEFAULT NULL

When displaying the data, it'll show 2 results..one is Wed Dec 31, 1969 and the other is
Tue Nov 30, 1999.

Can some explain what is going on here?

I've search this in Google and tried a few things with no resolve. If someone could help, I greatly appreciate it.