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

edit: I have tested a few things, and it seems that the db is sorting the results by the month. that is, if i change one of the dates from 01/18/07 to 11/18/07, it appears along with all the other November entries (from 2006).

    The output for "date("m / d / y")" is just a string. If you "ORDER BY" a string, it follows the basic rules for sorting strings; since you have "month" first, it will start sorting with that - it will not care what year it is.

    You might want to look into UnixTimeStamp and store that in the DB. You'll have to format it AFTER it leaves the DB to your "date("m / d / y")".

      thanks Neumy

      is there no way to, in my SQL query, make the db understand that it should treat the string as a date?

        set the column type to datetime and reinsert all your dates with a mysql timestamp

          Create another field in your table that is DATE type, parse all the records and using [man]strtotime[/man] and [man]date[/man] function insert your dates from the VARCHAR type field in your DATE type field so u wont loose or have to manualy insert all the dates ...

            SELECT *, STR_TO_DATE(date, '%m / %d / %y') AS sort_date FROM files 
WHERE name LIKE '%$searchTerm%' ORDER BY sort_date DESC

              its viewing it as: 011807, which comes directly after 011806... to get past your "bug", you need put the date order like 20070118 (YYYYMMDD)... since its stored as a varchar, its ordering by string value, which in this case is its integer value... like ordering from 1,2,3,etc... for additional help on date/time functions in sql, see link below.

              http://www.databasejournal.com/features/mysql/article.php/2172731

                thanks all for your replies. my concern at the moment however is to re-entering the dates for each db entry because while tinkering with the 'date' column of my db table, I managed to reset all the dates so that they read '0000-00-00'.

                I have an XML file from an earlier db dump that I will use to reset the dates for each entry. How do I go about parsing the XML? Entries are in the following format:

                <files>
        <fid>17</fid>
        <name>eFamz backup 23-7</name>
        <type>plain text</type>
        <owner>Geoffroy</owner>
        <email>foid025@msn.com</email>
        <security>public</security>
        <date>07 / 23 / 06</date>
        <description></description>
        <downloads>3</downloads>
        <rating></rating>
</files>

                but all I want are the dates so that I can fix the database. In other words, I want to loop through each file entry in the XML file, and get the file's date by storing it in a PHP variable. Any links to any good tutorials or just some code would be appreciated. Thanks!

                  foid025 - Please don't post duplicate topics. I've merged a post or two into this thread and deleted the duplicate threads.

                    Write a Reply...