Page 1 of 1

Record date to MySQL using PHP

Posted: Thu Apr 16, 2009 10:58 am
by danieltmi
Greetings;
I am able to write various data to a MySQL dbase using a PHP driven form however the date/time is blank.

This is the sql code that works fine (without the time stamp code):

$sql = "update contacts set " . "address='$address', " . "address2= '$address2'," . "city= '$city'," . "state= '$state'," . "zip= '$zip'," . "country= '$country'," . "officephone= '$officephone'," . "homephone= '$homephone'," . "fax = '$fax'" . "WHERE id LIKE '$recordid' ";

This is the sql code that bugs:

$sql = "update contacts set " . "address='$address', " . "address2= '$address2'," . "city= '$city'," . "state= '$state'," . "zip= '$zip'," . "country= '$country'," . "officephone= '$officephone'," . "homephone= '$homephone'," . "fax = '$fax'," . "whensubmitted = current_date" . "WHERE id LIKE '$recordid' ";

The name of my date/time field is whensubmitted, a No Null datetime type. I tried using NOW() and current_date to no avail, possibly my syntax was not proper?

Re: Record date to MySQL using PHP

Posted: Thu Apr 16, 2009 12:47 pm
by silverspy18
I think you have to use date("d-M-Y",time()) instead of current_time, I don't know if PHP uses it as it didn't work for me when I tried it. Regardless, you have to concatenate the date properly and put quotes around it:

Code: Select all

 
$sql = "update contacts set " . "address='$address', " . "address2= '$address2'," . "city= '$city'," . "state= '$state'," . "zip= '$zip'," . "country= '$country'," . "officephone= '$officephone'," . "homephone= '$homephone'," . "fax = '$fax'," . "whensubmitted = '".date("d-M-Y",time())."' WHERE id LIKE '$recordid' ";
 
I hope that works...I haven't tested it.

EDIT: I think that if you have the datatype of of 'whensubmitted' as a date, then an incorrectly formatted date won't be stored in the field, and the result will be blank. If I am wrong, forgive me I am fairly new to PHP.

Re: Record date to MySQL using PHP

Posted: Thu Apr 16, 2009 3:55 pm
by danieltmi
Spy, I checked the data/time data and it is in fact Y-M-D, I used the following cleaned up string:

$sql = "UPDATE contacts SET address = '$address', address2 = '$address2', city = '$city', state = '$state', zip = '$zip', country= '$country', officephone = '$officephone', homephone = '$homephone', fax = '$fax', whensubmitted = NOW() WHERE id LIKE '$recordid'";

and it worked like a charm!

Thanks for your help! God bless!