data ordering - the y2k bug of january 2007!

foid025

Hey all,
I am having trouble with a php script used to show results to a mySQL query. I am using the following query:

SELECT * FROM files WHERE name LIKE '%$searchTerm%' ORDER BY date DESC

where date is stored in the database in the form "date("m / d / y")"

up till now (for the past six months), I have had no problems with this script, but since january started, new files appear at the very end of the list of files, as opposed to the beginning where they should be.

I am not sure whether the problem rests with my SQL query, or with my date format or what.

Thanks for any help

NogDog

My guess is that you are storing the date as a varchar field instead of a date field? (If it were a date field - as is appropriate for the data being stored in it - then it would sort correctly.)

foid025

thanks NogDog.

I changed the type of my 'date' entry in the db from varchar to date - and in the process deleted all the existing dates. Now every entry in the db has a date reading '0000-00-00'. Aye! Is there any way to revert this?

I think I will be keeping the date as a varchar, and I will store the timestamp instead of the date object. That way, it will actually order dates in the right order. However, right now, I need to get back the existing dates. I have an XML backup of the db table, so I can probably use that, but it doesn't have every entry in it - its missing 500 or so.

scrupul0us

foid025 wrote:
thanks NogDog.

I changed the type of my 'date' entry in the db from varchar to date - and in the process deleted all the existing dates. Now every entry in the db has a date reading '0000-00-00'. Aye! Is there any way to revert this?

yes there is... restore your backup of the DB... you DID backup your DB before you attempted to make changes right?