[SOLVED] dates and MySql

[simon551]

I think I'm kind of confused on how to deal with nulls in dates.

 

if ( !($_POST['beginningDate']) == '') 
{
	$date= ($_POST['beginningDate']);
	$timestamp = strtotime($date);
	$bdate = date("Y-m-d", $timestamp);
}
else 	{
		$bdate= 'NULL';
	}


$insertSQL="INSERT INTO table (beginningDate) VALUES ($bdate)";

 

This works to take care of the null, but if the date actually has a post value, then the query

rejects the date because it is not surrounded by quotes. What I'm struggling with is that MySql

requires either a NULL or a 'Value' but I don't really know how to accomodate the possibility of

either. Some have suggested that I leave the date out of the query if it is null. Would that mean

I would re-write the query in the if statement? Does anyone have a better way of dealing with this

problem?

 

[chronister]

if ( !($_POST['beginningDate']) == '') 
{
	$date= ($_POST['beginningDate']);
	$timestamp = strtotime($date);
	$bdate = date("Y-m-d", $timestamp);
}
else 	{
		$bdate= 'NULL';
	}


$insertSQL="INSERT INTO table (beginningDate) VALUES ('$bdate')";

 

 

That will work, just surround it with the quotes. Maybe I am missing something here, but I have no problem with single quotes in a query surrounding a variable.

 

Nate

 

[simon551]

I didn't either until 5.041. see <a href="http://www.phpfreaks.com/forums/index.php/topic,149956.msg646152.html#msg646152">this post</a>.

 

if I surround the '$bdate' in the query mySql tries to insert 'NULL' in the query which is rejected.

 

 

[chronister]

what is the error that you get when you try to insert 'NULL' ?

 

I guess the other way would be to write 2 queries and run the proper one based on an if statement.

 

Nate

[simon551]

I'm at home and the new server is at work, but if I recall correctly the error was 'Incorrect date value: 'NULL''

[chronister]

the best solution I can see is to write 2 queries and run the proper one based on an if statement. Is you data type in mysql set to date?

 

The other option would be to set the default value in MySql to NULL.

 

Hope this helps a little.

 

Nate