EDIT The format in the .csv excel file for the date was 15-12-2011 and in the MySQL phpmyadmin database the column type was set to DATE so if I imported the file all data in date column changed to 0000-00-00. I did the next to fix this, went into the .csv file and selected the column for date and changed the format to 'Africa' so it would change everything from d-m-Y to Y-m-d. So in the .csv it would become 2011-12-15 instead of 15-12-2011. After importing it again the date was correctly imported without turning to 0000-00-00.
After that I Changed the ORDER BY in index.php:
$query = mysql_query("SELECT * FROM `newitems` ORDER BY startVisible DESC LIMIT $start, $per_page");
After that I changed the format date where was needed.
Available from: <?php
$timestamp = strtotime($query_row["startVisible"]);
echo date("d-m-Y", $timestamp);
?>
These scripts would first change the order by 1.year 2.month 3.day and after that change the format so it would echo 15-12-2011 instead of 2011-12-15 (how it is saved in the database)
Hope this will help others with the same problem.
Cheers.
below = question I asked
I have exported a table and modified some things that needed to be modified. Next I tried to import them into the table again but startVisible column Type was set to date. All my dates where stored as 0000-00-00. All my dates in the csv file are like 15-12-2011. I've changed the column Type to varchar and after that I could import the csv file with everything as 15-12-2011 I wanted to sort everything by date in www.domain.com/index.php. That is because the column type is not set to DATE. So I Changed the Column type from VARCHAR to DATE again and all the dates changed to 0000-00-00. So no matter what I did everything changed to 0000-00-00. I removed everything and imported the same file and all the dates where imported correctly. So it may be a MySQL load fail or something.
I used:
$query = mysql_query("SELECT * FROM newItems ORDER BY startVisible DESC LIMIT $start, $per_page");
But all the dates are mixed now. It has to short them by d-m-Y but does not do that.
9-8-2011
9-3-2011
8-3-2011
7-6-2011
7-3-2011
7-11-2011
6-7-2011
Now everything is sorted correctly but I want to change the order it shows from 2011-12-15 to 15-12-2011. ( I MEAN ON THE WEBSITE not in MySQL ). in index.php I want to show everything as d-m-Y but I still want to sort (ORDER BY) them correctly. How to do that?
Thanks,
F4LLCON
That's becouse the query is now ordered by a varchar column so it is sorted alphabetically, So try to convert it to a date before order the query with it like this:
select DATE_FORMAT(STR_TO_DATE(startVisible, '%d-%m-%Y'), '%d-%m-%Y') as startDate,...
from newItems
ORDER BY startDate DESC LIMIT $start, $per_page;
Your problem is the fact the column is no longer a date, and therefore cannot be sorted as a date. You've mentioned that it is now a VARCHAR. Ergo, it will sort alphabetically, not as a date.
The type is currently varchar, so it will use text sorting.
My advise is to add a column to your table of type date, fill it by converting the date-strings to proper dates using str-to-date() and then sort on that column. (To really do d-m-y you probably have to use
ORDER BY day(datecolumn) desc, month(datecolumn) desc, year(datecolumn) desc
But are you sure you don't just mean ORDER BY datecolumn DESC - most recent first?
You can update the columns using STR_TO_DATE
Do this.
You can try MySQL function STR_TO_DATE. Here is an example query.
SELECT STR_TO_DATE(dt,'%d-%m-%Y') AS fmtDate, dt FROM test.text ORDER BY fmtDate
Prasad.
Maybe, it will be useful to create a function (or a trigger), and use it when you need :
DROP FUNCTION IF EXISTS makedate;
CREATE FUNCTION makedate(your_field VARCHAR(10)) RETURNS DATE DETERMINISTIC
RETURN
DATE_FORMAT(STR_TO_DATE(your_field, '%d/%m/%Y' ) , '%Y-%m-%d' );
Hope it helps.