I HAVE GOT A BIG PROBLEM: re: DATE ORDE

kappaluppa

I created a mysql table. I could not get the dates entered in a DATE field to read mm-dd-yyyy, so i made it a tinytext field instead.
I want to order by date, but its ordering by the months rather than the year, jan 2001 is next to jan 2002.

So I figured I change the field to DATE rather than TINYTEXT and the dates came out all screwy (good thing I tested the change on a copy of the DB!!!).

Any suggestions on how i can get the dates in MM-DD-YYYY format AND in descending order, so that 2002 comes before 2001.

PLEASE SOMEONE HELP!!!

thanks,
K

sroensberg

From the manual:
"The format of a DATE value is 'YYYY-MM-DD'. According to ANSI SQL, no other format is allowed. You should use this format in UPDATE expressions and in the WHERE clause of SELECT statements."

You must use a combination of:
SUBSTRING(str,pos,len)
CONCAT(str1,str2,...)
to convert to the correct format for a DATE.

When outputing use:
DATE_FORMAT(date,format)
to get your american format.

Soren

Kathy wrote:

So I figured I change the field to DATE rather than TINYTEXT and the dates came out all screwy (good thing I tested the change on a copy of the DB!!!).

Any suggestions on how i can get the dates in MM-DD-YYYY format AND in descending order, so that 2002 comes before 2001.

[deleted]

Always store your dates as a DATE type, then use DATE_FORMAT() to re-arrange the date during the select:

http://www.mysql.com/doc/D/a/Date_and_time_functions.html

kappaluppa

thanks...

is this what i put in?
DATE_FORMAT(date,%m %d %Y)

if so, where do i put it (i'm new to this)

this is the code i'm using:

$Link = mysql_connect ($Host, $User);

$Query = "SELECT * from $TableName ORDER BY date desc";
$Result = mysql_db_query ($DBName, $Query, $Link);

// Create a table.
print ("\n");
print ("<TABLE BORDER=1 WIDTH=\"100%\" CELLSPACING=1 CELLPADDING=2 ALIGN=CENTER>\n");
print ("<TR ALIGN=CENTER VALIGN=TOP>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>Date</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>Author</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>Title</TD>\n");
print ("</TR>\n");

// Fetch the results from the database.
while ($Row = mysql_fetch_array ($Result)) {
print ("<TR ALIGN=CENTER VALIGN=TOP>\n");
print ("\n");
print ("\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>$Row[date]</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>$Row[author_name]</TD>\n");
printf("<TD ALIGN=CENTER VALIGN=TOP><a href=\"results.php3?news_id=%s\">$Row[heading]</TD></a>\n", $Row["news_id"]);
$last_date = $row["date"];

}
mysql_close ($Link);
print ("</TABLE>\n");
?>

kappaluppa

You must use a combination of:
SUBSTRING(str,pos,len)
CONCAT(str1,str2,...)
to convert to the correct format for a DATE.

how and where do i do this & are there supposed to be numbers or something else substituted for the things in the ( ) ?

thanks

[deleted]

Forget that method, date_format() does this for you (in a way)

[deleted]

Well, if you read the examples in the manual, you should know it goes in the query:

$Query = "SELECT *,
DATE_FORMAT(date, '%m %d %Y') as datestring
FROM $TableName
ORDER BY date desc";

After running that query you can access the results like:

echo $row['datestring']

BTW: always quote the names of the fields in the $row[], otherwise PHP will assume you mean to use a constant, which will give a sh*tload of warnings and errors. (which apperently you have disabled)

sroensberg

In short:
Add a new DATE field
Use a UPDATE to convert your TINYTEXT to a real a DATE using CONCAT(SUBSTRING(....),'-', ...
Delete your TINYTEXT field

See the manual at www.mysql.com/doc for details.

Then you are ready to proceed as vincent writes.

Soren

kathy wrote:

You must use a combination of:
SUBSTRING(str,pos,len)
CONCAT(str1,str2,...)
to convert to the correct format for a DATE.

how and where do i do this & are there supposed to be numbers or something else substituted for the things in the ( ) ?

thanks