In regards to DATE in mySQL and PHP

[Ardi]

Hello there

Does anybody know how to convert a mySQL DATE data type into a nice format, for example

dateConvert(2001-1-10)

returns

Wednesday, 10 January 2001

And also, if I wanted to go through dates using a for loop, how would I be able to do that?

e.g
for ($d=2001-1-10; $d < 2001-1-17; $d ??) {
steps...
}

Thanks in advance for any help!!
Ardi N

[PeterW]

if your date is in the year - month - day format, you can use this.. (if you have the dates in a different order, swap the order in mktime).

$mysqldate = explode ("-", $mysqldate);
$prettydate = date("l, j F Y",mktime(0,0,0,$mysqldate[1],$mysqldate[2],$mysqldate[0]));

[freddydoesphp]

As fot the first part

SELECT DATE_FORMAT(datefield, '%W, %e %b %Y') as date from tablename

The second part will be a bit more tricky since you can't run a for loop in MySQL you would need to convert your timestamp into unixtime then use mktime to create the days you could do something such as,

if the value returned from MySQL was 2001-1-10

<?
$data = explode("-", $mysqldatefield);
$year = $data[0];
$month = $data[1];
$day = $data[2];
$unixtime = mktime(0, 0, 0, $month, $day, $year);
//print today's date
print DATE("l, d M Y", $unixtime)."<br>";
//print the date in the smae format for the next one hundred days
for($i=1;$i<100;$i++) {
print DATE("l, d M Y", mktime(0, 0, 0, DATE($month), DATE($day + $i), DATE($year)))."<br>";
}
?>

[freddydoesphp]

sorry PeterW I didn't see your post till after I posted mine. Yeah your way works however there are some built in MySQL functions so you can do it in the query. Until I found that out I was using your method all the time. Just a note in case you care.

[Ardi]

Thank you very much PeterW and freddy for you help =)

Those solutions have worked like a charm.

[PeterW]

Originally posted by freddydoesphp
sorry PeterW I didn't see your post till after I posted mine. Yeah your way works however there are some built in MySQL functions so you can do it in the query. Until I found that out I was using your method all the time. Just a note in case you care.

Thanks for pointing it out
Yep, I care...

* quickly changes that code in scripts he has written using it..

[Vinay]

i dont like either implementations above

mysql has a UNIX_TIMESTAMP function which can be used in the form of:

SELECT UNIX_TIMESTAMP(date_column) as date_timestamp,id,etc FROM table

it gives you a standard unix timestamp which you can pass to date() .. better than exploding the data ...

[freddydoesphp]

Nice one Vinay! but why not use DATE_FORMAT if all you want is a formatted date form a date column for the second part of doing the loop your function is great, but for the first you would still need tio run the query then use DATE() instead of doing it all in one.