Date output from mysql database

[Badgerer]

Hi

I'm having problems getting a correct date out of a mysql database:

The function to input the date seems to work fine:

function input_date($closure_date){
//$closure_date = a string of 2004-03-05
$conn = db_connect();
$sql = "update league set date = '";
$sql .= $closure_date . "' where leagueid = 13";
$r = mysql_query($sql, $conn);
if($r){
echo "success";
return $r;
}else{
echo "failure";
}

}

the date column in the database is of type 'date'.

But when I try to get the date out of the database it gives me a date back in 1970..

The function to access the date is as follows:

function getClosureDate(){
$conn = db_connect();
$sql = "select DATE_FORMAT(date,'%m %d %Y') from league";
$sql .= " where leagueid = 13";
$result = mysql_query($sql, $conn);
return mysql_fetch_array($result);
}
$result = getClosureDate();
if($result){
$time = strftime("%A %d/%m/%y", $result['date']);
echo '<br> time: ' . $time . '<br>';
}else{
echo "bugger";
}

I had some success previously accessing timestamp dates from the same database using:

DATE_FORMAT(date,'%M,%D,%Y') AS dateformatted

But this didn't seem to work this time..

Can someone please help!

[rachel2004]

Could it be this line:

 $time = strftime("%A %d/%m/%y", $result['date']); 

You didn't use a column alias called "date" in your query for function getClosureDate() as far as I can see. Try this:

 $sql = "select DATE_FORMAT(date,'%m %d %Y') AS date from league"; 

[Badgerer]

Hi Rachel2004

I tried what you suggested, but I still get the date 'Thursday 01/01/70' from the database no matter what date is actually in the database.

Any other suggestions..

[liquidSilver]

y don't you try using strtotime() to change the date that you are going to put into the db BEFORE you insert it. This will put the dates into the db in unix timstamp. Then when you query the timestamp out use the date() function in php to convert it to the format that you want, and then display it.

This way you won't have to deal with that date function in sql. I hate the date col types in sql. This is how I slways do it to avoid the sql pains.

[rachel2004]

$time = strftime("%A %d/%m/%y", $result['date']);

strftime is expecting a timestamp as an argument, but what is contained in $result['date'] isn't. I didn't test for that the first time I looked at your code. I merely noticed you weren't using a column alias.

What you should use is the MySQL function UNIX_TIMESTAMP() instead of DATE_FORMAT() in your query. Swap out those two functions and see if it solves your problem. Just to be clear, your query should look like this:

$sql = "select UNIX_TIMESTAMP(date) AS date from league";

I'm on a computer right now, which doesn' t have access to MySQL at this time, so I can't test it out before posting my code, but give it a shot and post back how it goes.

[Badgerer]

Thanks Rachel

That worked a treat!

I think liquidSilvers solution would have worked too..

So thanks to both of you!!