Insert into Date field

[Anonymous]

Hello,

I am building a work order module for my companies intranet. One of the fields is when is the work to be done by? If they choose "before" or "after" then the specific_date field is required, and used for the insert. If they choose "Anytime" that means the specific_date field becomes a moot point, so I don't pass any data to my mysql insert query. If I do not pass mysql a valid date, I get an error message:

Incorrect date value: '' for column 'specific_date' at row 1

How can I pass an exceptable "null" value to MySql, I tried setting the field to not null but that didn't make any differance. According to the MySql man pages, if I pass NULL (no quotes or tics) it should work but I couldn't get that to work either.

Thanks

 

[Anonymous]

Hi,

Well, if someone chooses 'Anytime' you can modify the query so that it does not use that field.
For example:

$sql = "UPDATE table SET value = '$val1'... WHERE condition1 AND condition2..."
if($var != 'anytime'){
$sql .= "AND condition3";
}

If there are no previous conditions, you can use WHERE 1 so that you can later fill it with the rest.

Regards.

 

[Anonymous]

Thanks for the suggestion, the concept makes sense I will try to impliment it when I get back to work tomorrow.

 

[Anonymous]

That works great...

here is my working code (I am gonna say finished code cause it is still pretty messy).

<?php
  	mysql_connect(localhost, root, apgar) or die(mysql_error());
   	mysql_select_db(apgar) or die(mysql_error());
	mysql_query($sql);
	if(trim($_POST['required_date']) == 'Anytime')
		{	
			mysql_query
				("INSERT INTO workorders 
					(performed_by, requested_by, required_date, notify_client, client_contact_name, client_contact_telephone, work_location, location_name, location_street, location_city,location_zip, invoice_number, po_number, purchase_status, demo_pickup_date, product_description, comments)
				values
					('{$_POST['performed_by']}','{$_POST['requested_by']}','{$_POST['required_date']}','{$_POST['notify_client']}','{$_POST['client_contact_name']}','{$_POST['client_contact_telephone']}','{$_POST['work_location']}','{$_POST['location_name']}','{$_POST['location_street']}','{$_POST['location_city']}','{$_POST['location_zip']}','{$_POST['invoice_number']}','{$_POST['po_number']}','{$_POST['purchase_status']}','{$_POST['demo_pickup_date']}','{$_POST['product_description']}','{$_POST['comments']}')")
		     or die(mysql_error());		
	 
	 	}
	else
		{
			mysql_query
				("INSERT INTO workorders 
					(performed_by, requested_by, required_date, specific_date,specific_time,specific_time2, notify_client, client_contact_name, client_contact_telephone, work_location, location_name, location_street, location_city,location_zip, invoice_number, po_number, purchase_status, demo_pickup_date, product_description, comments)
				values
					('{$_POST['performed_by']}','{$_POST['requested_by']}','{$_POST['required_date']}','{$_POST[specific_date]}','{$_POST['specific_time']}','{$_POST['specific_time2']}','{$_POST['notify_client']}','{$_POST['client_contact_name']}','{$_POST['client_contact_telephone']}','{$_POST['work_location']}','{$_POST['location_name']}','{$_POST['location_street']}','{$_POST['location_city']}','{$_POST['location_zip']}','{$_POST['invoice_number']}','{$_POST['po_number']}','{$_POST['purchase_status']}','{$_POST['demo_pickup_date']}','{$_POST['product_description']}','{$_POST['comments']}')")
     or die(mysql_error());		
		}
?>

Insert Was Successful, click <a href="index.php">here</a> if you are not redirected in 3 seconds.

<script language=javascript>
setTimeout("location.href='index.php'", 3000);
</script>