Dates in PHP & MySql

Storing dates from PHP form into MySql

[mvaz]

Hello, I have a form in which visitors select a future date (dd, mm, yyyy) from three drop-down lists.

I want this to be stored in mysql db as a date. Further, I want mysql to display all those rows which have a date which is either today or earlier. In otherwords, I do not want to display rows that have a date that hasn't come yet.

I tried to create the drop-down for the date and year as $date = range(01, 31) and $year=range(2008, 2013) and for months I listed them through an array as $months=array(1=> 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December').

Please advise if this is the right approach to capture and store future dates and then display them using php chronologically yet only for the dates that have occured.

Many thanks for your assistance and guidance. - Melwyn

[rob7591]

I would use: strtotime() and input the timestamp into the database, then "SELECT * FROM table WHERE date < " . time()

[coopster]

Unix timestamps are one way to store data, but only for dates past the epoch. I prefer using ISO format (yyyy-mm-dd) to store dates, so whenever I create a date field I make the column of type DATE. Most RDBMS have extensive functions for date processing based on certain formats and the ISO format is one of the most, actually I believe it is THE most, widely accepted.

[mvaz]

Hi rob7591, sorry, I couldn't entirely understand what you meant. I would appreciate if you could elaborate a little more on this. Many thanks

[mvaz]

Apologies Coopster, didn't see your message before posting the previous post.

so, does that mean i will have to store the date in my db as yyyy-mm-dd and then do a "SELECT * FROM db WHERE auth = 'y' AND msg_date <" .date('Y-m-d) ."ORDER BY msg_date DESC LIMIT 20"; ?

I could do that, but then, how do I display the date in date('d-F-Y') format?

Any help is highly appreicated.

[coopster]

I just wanted you to know there were options for storing temporal data in most RDBMS and the DATE column type was another option. It just happens to be my preferred option. You can use standard SQL niladic datetime functions in your scalar operations to query your table, such as ...

SELECT * FROM db WHERE auth = 'y' AND msg_date <= CURRENT_DATE ORDER BY msg_date DESC LIMIT 20;

There are many ways to format the date as you desire, either using SQL or your application language.

[LifeinAsia]

I just wanted to clear up 1 issue for mvaz- how you STORE data in a DB and how you DISPLAY that data are 2 completely different issues. You can STORE the data in your table as a DATE or DATETIME field, but you can display it as "YYYY-MM-DD" or "MM/DD/YYYY" or however you want. What coopster is trying to explain is that if you STORE the data in the DB as a DATE field, you can take advantage of the built-in features for RDBMS for comparing and manipulating DATE data.

The problem is when you try to store temporal data in a VARCHAR field. Although to you "January 8, 2009" may be identical to "1/8/2009" or even "01/08/2009" those 3 values are completely different to a database.

For example, let's say you store a DATE value that has the value of January 8, 2009. You can DISPLAY that data as "1/8/2009" or "1-8-2009" or "the 8th of January in the year 2009" or 20090108" or any number of differently formatted ways. But the underlying 0s and 1s of exactly how that date is stored is transparent to the user and has absolutely no bearing on which way(s) you decide to display that data.

[edited by: LifeinAsia at 6:55 pm (utc) on Jan. 8, 2009]

[mvaz]

Thanks Coopster & LifeinAsia, I have now sussed the issue of storing the date as 'yyyy-mm-dd' in my db, and displaying it as 'd F Y'.

Now I am stuck with limiting my selected data to those rows that have a date which is <= date('Y-m-d').

Here is my code:
include $_SERVER['DOCUMENT_ROOT']."/includes/db_conx.php";
$today = date('Y-d-m');
$query = "SELECT * FROM wishes WHERE auth = 'y' AND msg_date <= $today ORDER BY msg_date DESC LIMIT 20";
$result = mysql_query($query);
while($c=mysql_fetch_array($result)) {
echo stripslashes($c['msg_type'])."<br />";
$newdate = ($c['msg_date']);
$newdate = date('d F Y', strtotime($newdate)); echo $newdate;
?>

Please advise where I have gone wrong. All help is very much appreciated and gratefully accepted. Many thanks - Melwyn

[mvaz]

Thanks guys, replaced $today with CURRENT_DATE and it seems to have done the trick.

Hats off to this forum and all you guys who support budding programmers like myself and many others. Keep up your good work and God Bless!

[coopster]

Just so you know why that didn't work ...

CURRENT_DATE

is a datetime function that returns the current date. If you provide a date for comparison in a date operation you need to surround it in quotation marks. Using your previous example, it would be ...

$query = "SELECT * FROM wishes WHERE auth = 'y' AND msg_date <= '$today' ORDER BY msg_date DESC LIMIT 20";

[mvaz]

Once again thanks Coopster, will bear that note in mind about date operation comparison.

[g1smd]

Consider displaying the date as 2009-01-08 to all users, as that is universally understood to be 2009 January 08.

In contrast, 1/8/09 means 1st August to many people outside of the US, and January 8th to many of those inside the US and Canada.

Avoid the ambiguity by using a four-digit year and the Year-Month-Day ordering. See also ANSI X3.30, NIST FIPS 4-1, ISO 8601, EN 28601, JIS X 0301, and various other standards.