PHP & MYSQL DATE Function?

[BuLLDoG]

Hi,

I am trying to insert a date into a field in the format of YYYY-MM-DD. I have tried several ways, but it isn't inserting into the database correctly.

My SQL statement I am using is:

$sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime)VALUES('$itpname', '$detail', '$author', '$externalurl', curdate())";

but when I am viewing it in phpmyadmin I see the result:

02/11/09 1


My field has been set to type VARCHAR.


Anyone know how to do it?

 

[DomainMOD]

If you're going to store your dates in the format YYYY-MM-DD you should probably use the date type instead of varchar. You'll find that you'll run into all sorts of problems using varchar if you're trying to query on those dates.

Also, depending on what type of data you're storing in this table, you may also want to think about using the datetime type, which will save the full date and time in the format YYYY-MM-DD HH:MM:SS.

 

[BuLLDoG]

If I set the field to 'date' type, how should my sql statement look?

---------- Post added at 05:11 PM ---------- Previous post was at 05:05 PM ----------

Because, I have just changed field type to 'datetime'

And used the following code:

$datestamp=date("y-m-d h:i:s"); //create date time


$sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime)VALUES('$itpname', '$detail', '$author', '$externalurl', $datestamp)";

and the result I see in phpmyadmin is now: 2002-11-09 11:08:36

I want it to be displayed as YYYY-MM-DD HH:MM:SS
but as you can see its put it in the wrong order?

 

[DomainMOD]

If I set the field to 'date' type, how should my sql statement look?

curdate() already returns the date in the YYYY-MM-DD format, so your current query should work as is.

 

[BuLLDoG]

Even with curdate() its putting it as 2002-11-09 :S

 

[DomainMOD]

For your question about datetime, try using the following format for your php date function - "Y-m-d H:i:s" (capital Y and H).

In other words, use this:

$datestamp=date("Y-m-d H:i:s");

$sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime)VALUES('$itpname', '$detail', '$author', '$externalurl', '$datestamp')";

 

[BuLLDoG]

Same problem is happening...

 

[DomainMOD]

Now that you're using the php date function and then inserting the variable into the db, instead of running curtime, you'll have to put ' ' around the $datestamp variable when inserting.

 

[BuLLDoG]

Yup, it has one but still giving the same problem? quite strange I didn't think dates would be this problematic

 

[DomainMOD]

That's really bizarre, it should be working. Try narrowing it down first...

Replace:

$datestamp=date("Y-m-d H:i:s");

With:

$datestamp=date("Y-m-d H:i:s"); 
echo $datestamp; 
exit;

When you load that page in your browser is it displaying the date and time correctly?

 

[BuLLDoG]

it works whilst I echo it out, just when it gets added to the database I hit the problem..

 

[DomainMOD]

Try the same with the insert statement.

$sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime) VALUES ('$itpname', '$detail', '$author', '$externalurl', '$datestamp')";
echo $sql;
exit;

If that's displaying properly, and the table field is set to use the datetime type, I'm not really sure what to tell you, the code you have should be working.

 

[BuLLDoG]

Ah school boy error, it is working now, the type was date instead of datetime my bad. Thanks ever so much for your help demaxxus

 

[Kate]

Instead of formatting the date in PHP you can also do it with mySQL using the DATE_FORMAT function.

 

[nasaboy007]

i believe the mysql NOW() function also does the same thing to get datetime.

i personally always just keep it in terms of epoch time and then use php functions like date and mktime to go back and forth between epoch and readable time. it's MUCH easier to do computations with epoch time, so if you'll be doing things like time between datetimes etc, i'd suggest looking into it.