Help needed with saving today's date back to database

PHP
1

My echo $update_sql statement shows this

UPDATE projects SET dex=‘{FD35CF46-D1F4-A05F-D4C6-16CF86A2E05F}’, Name=‘h’, Designation=‘f’, Phone=59, PMC=1, Quality=1, Progress=1, Issues=1, Responsiveness =1, Commitments=1, Safety=1, SLevel=1, Total=8, Improvement=‘No comment for now’, Future=‘N’, Date=2012/06/21 WHERE dex=‘{135C2B55-B95D-786D-9CBD-9563B3AF04B3}’

The date shown here is the current date. However, the date stored in my database is 0000-00-00. what could be wrong? I am sanitizing the date as date type.

sanitize($date, "date");

I am using the $date=date(Y/m/d) to get today’s date. Do let me know if you can help. Thanks. :slight_smile:

2

Why don’t you use MySQL’s CURDATE() instead?

3

Have you tried CURRENT_TIMESTAMP in the query to store the date? I think CURRENT_TIMESTAMP will work across multiple databases systems but can’t be sure, I don’t have a copy of @r937;'s book to hand to check

4

Date=2012/06/21

There are so many problems with that that I feel I should spell them out to you …

Date is a protected word and should be quoted with backticks date - just avoid using it, rename the column

The value should be quoted ‘2012/06/21’

Mysql is very forgiving about how you format a date but strictly speaking it should be dash separated.

Slashes might work, spaces do, but as you are formatting the date yourself then why not sort it out?

$date=date(‘Y-m-d’);

Overall though, make a note of all that because some things are going to haunt you otherwise, and just do what Guido2004 says.

5

except it’s better to use CURRENT_DATE instead of CURDATE() because the former is standard sql and will work in all database systems

6

@immohito

Check out phpMyAdmin and table structure:

table date type to TIMESTAMP
and
table date field attribute to “on update CURRENT_TIMESTAMP”

Once set then the date is automatically updated.

7

john, that’s a nice feature, but it’s not appropriate for a project date

first of all, it’ll get updated on any change to the row, e.g. even if you fix a typo on the project title

but more importantly, it’s a datetime value, not appropriate for a date column

8

The MySQL reference manual fooled me there:

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

I would have expected it to give the definition of the standard sql function, and then say that CURDATE() is a synonym for CURRENT_DATE…