Displaying dates in order

[Venom Rush]

Hi there

I started a thread about this in the MySQL forum about this (http://www.daniweb.com/forums/thread93312.html).

Basically I don't know what my db table design should be like in order for me to sort the dates from most current to latest date.
The format is as follows:

DD Month YYYY eg(05 June 2007)

I was told a little while ago that there might be a way through php to spit the date out in the format shown above while having my table column set to DATE. If you know how that would help alot ;)

[Shaffer]

Hello,
I have my table date/time column set to VARCHAR(40) for that matter, or anything above the length of the timestamp string.
Then, with PHP, I can get that information using date().

If you syntax is DD/MM/YYYY, you can explode the function by the forward slash (/), and then mktime(0,0,0,$date[1],$day[0],$day[2]), with $date being the result of the explode function above. Now, you have a timestamp that you can do whatever you want to with.

If you don't understand, I can present it in a code,
Shaffer.

[Venom Rush]

Hello,
I have my table date/time column set to VARCHAR(40) for that matter, or anything above the length of the timestamp string.
Then, with PHP, I can get that information using date().

If you syntax is DD/MM/YYYY, you can explode the function by the forward slash (/), and then mktime(0,0,0,$date[1],$day[0],$day[2]), with $date being the result of the explode function above. Now, you have a timestamp that you can do whatever you want to with.

If you don't understand, I can present it in a code,
Shaffer.

I am somewhat of a novice so an explanation with code would be great. Been racking my brain on this for the past couple of hours.

[ryan_vietnow]

I think MYSQL can produce that format if you like.
you can use query like:

<?php
$query="Select day(datefield),monthname(datefield),year(datefield) from table";
$result=mysql_query($query);

$day=mysql_result($result,0,"day(datefield)");//where 0 is the row you want to display..
$month=mysql_result($result,0,"monthname(datefield)");
$year=mysql_result($result,0,"year(datefield)");

$date=$day."-".$month."-".$year;

echo $date;
?>

[Venom Rush]

I think MYSQL can produce that format if you like.
you can use query like:

<?php
$query="Select day(datefield),monthname(datefield),year(datefield) from table";
$result=mysql_query($query);

$day=mysql_result($result,0,"day(datefield)");//where 0 is the row you want to display..
$month=mysql_result($result,0,"monthname(datefield)");
$year=mysql_result($result,0,"year(datefield)");

$date=$day."-".$month."-".$year;

echo $date;
?>

Hi ryan

Thanks for your help. I managed to solve it with the following code:

$query = "SELECT heading, day(date), monthname(date), year(date), description FROM event ORDER BY date";
$result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
						
if (mysql_num_rows($result) > 0 )
{
  while($row = mysql_fetch_row($result))
  {
  echo "<p>".$row[0]."<br>".$row[1]." ".$row[2]." ".$row[3]."<br>".$row[4]."</p>";
  }
}