date format 2

[d0nny]

OK, I've played around with this for long enough!

I'm using statements in my PHP files and this is what I have:

$result = mysql_query("select * from lunch where category='lunch' and date>=CURDATE()");
while ($row = mysql_fetch_assoc($result)) {
      echo '<div class="lunchItem">';
      echo '<div class="lunchDateHeader">';
      echo $row[date];
      echo '</div>';
      echo '<div class="lunchItems">';
      echo $row[maincourse].'<br />'.$row[pudding];
      echo '</div>';
      echo '</div>';
}

What I want to do do though is format the date.
I want the date to look like this

Monday 21 September

But I have no idea how to format this associative array.

 

[Vragabond]

Actually, this is best done within your SQL query. See here on how to do it:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

[small]Do something about world cancer today: Comprehensive cancer control information at PACT[/small]

 

[jpadie]

Donny you should enquote your array keys.

date should not be an associative array. i assume it is a mysql timestamp.

there are two ways to achieve what you want. a php method and a mysql method

$row['date'] = date( 'l j F', strtotime($row['date']));

$result = mysql_query("select *, date_format( date , '%W %e %M') as fDate from lunch where category='lunch' and date>=CURDATE()");

while ($row = mysql_fetch_assoc($result)) {
      $row['date'] =& $row['fDate']; //overwrite the date element
      //as normal

 

[d0nny]

Thanks Vragabond, but I had already read that. It does give you some theory on how to use the DATE_FORMAT functions, but as I was using '*' to retrieve my data into an associative array, I was unsure how I could format the date once it was in that array.

As ever, thanks jpadie. I was on those lines of though about the PHP solution, but I could work out how to format the date in my WHILE loop. Worked that out now.
And I didn't know I could request all field ('*') in my sql and also a specific field like date?? I thought I had to use '*' or then specify all the fields I wanted.

Which do you think is the better method? I've used the SQL method for now as I understand that better (;-)), but which is better?

 

[feherke]

Hi

Which do you think is the better method?

That may depend on some details of the application. Personally I also prefer to calculate/format dates in the SQL command, but there are cases when is better to do it in PHP :
[ul]
[li]the application is localized[/li]
[li]want to extract other information too, for example to display weekend dates with red[/li]
[li]want human-friendly display for near dates as "yesterday"/"today"/"tomorrow"[/li]
[/ul]


Feherke.

http://rootshell.be/~feherke/

 

[jpadie]

i agree with feherke. but i tend towards using php only and keeping my sql as close to ansi sql as possible as i now often code for database independence (able to use mysql, sqlite etc etc. if an app is to be forever tied to mysql, my leaning would be towards using mysql for the job, subject to feherke's exceptions.