[PHP/MySQL] formatting the date
 

I'm still just learning PHP and MySQL and I'm having a problem understanding how to print out the date of a table entry. I can print it out so that it looks like this:

20040712112214

....but how do I format it so that it makes sense? I know there is a DATE_FORMAT function in MySQL, but I guess I don't know how to use it cause I can't get it to work.

Always store your timestamps as a Unix date integer and use the PHP date() function to format it as human-readable text.

http://us2.php.net/manual/en/function.date.php

The type of field I have my date/time setup as is TIMESTAMP. When I'm defining and running my query, I'm doing it like this:

$query = 'SELECT * FROM weblog2 ORDER BY entrydate DESC'
if ($r = mysql_query ($query))
while ($row = mysql_fetch_array ($r)) {
print ...

How do I go about using the PHP date() function? Where do I go from here?

SinisterMotives: I thought I was the only one that knew that secret... now it's out of the bag, everybody is going to be able to do time easily.... now if they just could not learn about setting the locale and printing the date normally to adjust for timezone settings, I could have cornered the market.

FaderMonkey: A great way to handle TIMESTAMP columns is either to alter them into INT columns that are unix timestamps, or if that isn't an option, on each and every select and insert, you can use UNIX_TIMESTAMP(column_name) (for selects) and FROM_UNIXTIME(timestamp) (for inserts/updates). MySQL's date/time functions are really lacking, so I normally just avoid them and use PHP all of the way. It's a lot easier, but still harder than Postgres's full timezone support for it's date/time columns.

The date() function requires a Unix time integer as its second argument, so it won't work with the MySQL timestamp. You'll need to use the MySQL UNIX_TIMESTAMP function in a query to get the timestamp into Unix format, as aoeuhtns suggests.

To answer your question - how to use the date() function: the first argument to date() is a string built up of the flags shown in the table on the manual page I linked you to. Example:

would print:

Please excuse my ignorance, but how do I go about doing that? So far I'm defining my query like this:

How do I add the UNIX_TIMESTAMP function?

You'll probably have to replace the asterisk with the actual names of the fields:

I've never actually worked with MySQL timestamps, so I don't know if that will work correctly in your case. That's just how I think it should work.

That worked. Thank you, thank you, thank you, thank you, thank you for your help.

Oops...I guess I was wrong. The date() function is printing the current time instead of the timestamp from the data base. Here's how I wrote it:

Is there something else I'm missing?

The second double-quote goes after the a in the first argument. $entrydate is the second argument, not part of the first argument.

You're going to have to get the MySQL timestamp into a Unix date integer format before you can use date() on it too. There's no telling what the result will be if you just supply $entrydate as an argument to date().

When I do this:

I get a parse error for the line with the date() function in it.

When I do this:

...it prints the date as December 31, 1969, 4:00 pm. :(

In that first one there you have a { before $row['entrydate'] which is causing the parse error.

Yep, that fixed the parse error, but it's still printing the date as December 31, 1969, 4:00 pm.

The date() function usually returns a representation of 12/31/1969 if the supplied Unix integer is not a valid date on or after midnight 01/01/1970. The problem is further up in your code. Not sure where.

Oh. Try this:

I ended up doing it this way and it seems to be working:

I have another question about this though. I got this code from someone else....a website about making a simple weblog....so I want to understand exactly how it's working. The seperate lines for defining the query ($query= and then $query.=)....is that just a way of setting different queries to the same variable? Does that question make sense?

The dot operator simply concatenates the three strings. It could have been written: