[RESOLVED] Date Problem

ChipRivers

I am having problems with the date references I am using in my script. Firstly I am creating a date value via a form in one page using the following:

$theday = mktime(0,0,0,date("m"),date("d")+$i,date("Y"));
$value = date("Y-m-d",$theday);

The variable $value being the date string that I send to the mysql database. In the database it seems to be entered in the correct format and displaying in mysql as YYYY-MM-DD.

I am then trying to extract this date and display it on another page as a text string using the following:

$display_date = date("l jS F Y",$date);

where $date has been allocated the value taken from the database.

Then when I display the string $display_date it always shows:

Thursday 1st January 1970

Can anybody see where I am going wrong? I am reasonably new to PHP and MYSQL but I have always had problems with using dates when entering into a database when I previously used ColdFusion!

tomhath

Try displaying what's in $date without formatting it first. It looks like you don't have a timestamp.

What datatype is the column in MySQL? You might have to select it using the UNIX_TIMESTAMP function:

select UNIX_TIMESTAMP(theColumn) as theDate from theTable

ChipRivers

Thinking about my script further, it is not quite as I explained! I create the date and enter it into the database as I have explained but what I am doing on the second page is slightly different. I don't actually take the date from the database, I just use it as a condition in my select.

i.e. I have a drop down select box on my second page which lists a selection of dates created and formatted in the same way as in the original form I use the selected value in this list to form my query of the data base and pull all records with a date matching the selection. This obviously works because I am using the same value to query the database as I used to create the records.

I then use the $display_date script to display a date on the resulting page based on the date selected from the drop down list.

So with this line:

$display_date = date("l jS F Y",$date);

$date is equal to the value selected in the drop down list which was created using:

$theday = mktime(0,0,0,date("m"),date("d")+$i,date("Y"));
$value = date("Y-m-d",$theday);

Does this make any more sense?

tomhath

I'll go back to my first post. What do you have in $date? It should be a timestamp, not a string. If you're getting it from the dropdown it's probably a string. A timestamp is a big number, it won't look anything like a date.

bradgrafelman

If you're passing $date along to the [man]date/man function, it's expecting a Unix timestamp, which, as tomhath described, appears to be a large number. It's actually the number of seconds since the Unix epoch (eh... Jan 1st 1970 or so?). The code you have here:

$theday = mktime(0,0,0,date("m"),date("d")+$i,date("Y"));
$value = date("Y-m-d",$theday);

returns a formatted date string due to the use of the [man]date/man function. Instead, you should simply return the timestamp generated by the [man]mktime/man utility, instead of converting it to the Y-m-d format.

ChipRivers

I think I've got it? $value should be equal to $theday and this is what I should put into my database. The date() function should really only be used for display purposes and used after taking the data from the database.

Do I need to change the column type in my database from DATE to something else?

tomhath

The column type in your database should be a DATE.

A timestamp is just a number; keeping dates as timestamps in the database makes it much easier to perform other operations such as comparing them, finding the difference between two dates, adding days or weeks to a date, etc.

Do a little more reading on how MySQL and php handle dates. Make sure you understand when your code has a timestamp and when you have a string.

ChipRivers

Struggling again! I have amended my date selection box to as below:

<?php
for($i=-1;$i<=6;$i++) {
$theday = mktime(0,0,0,date("m"),date("d")+$i,date("Y"));
$option = date("D M j, Y",$theday);
$value = $theday;
$dow = date("D",$theday);
if($i==0) {
$selected=' selected';
} else {
$selected='';
}
echo "<option value=\"$value\"$selected>$option</option>\n";
} ?>

When I view the source it creates values like this:

But when I enter the selected date into the database it ends up just being lots 0's ?????

bradgrafelman

And what are you trying to store into the database? The option values? If so, it should not be a DATE column, rather an INT column (this is what I've always used).

ChipRivers

Excellent! it is working. Changed the column type to INT and now it works,yahoo!

It is all working how I want it to at the moment but is there likely to be any problems with me using INT as as date storage type? Should I rewrite things to work with a DATE entry type to prevent any future problems?

bradgrafelman

I have always used the INT column because I always store Unix timestamps. The [man]date/man function requires a Unix timestamp, so you can simply drop whatever timestamp is into the database into a call to date() and you've got a date/time formatted exactly how you want it. If you decide you want to change the formatting later, or want to use a different formatting in a different place of the website, you simply change one line in your PHP scripts (the format parameter of the date() function) and there you go.

Roger_Ramjet

And you can do all of that in sql with a date column type and the inbuilt date functions, and a lot more besides.