Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!news.swapon.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail From: Arno Welzel Newsgroups: comp.lang.php Subject: Re: How to loop through the dates? Date: Tue, 17 Jun 2014 08:18:15 +0200 Lines: 69 Message-ID: <539FDDA7.80100@arnowelzel.de> References: <1g25lygstopv1$.1kxa4pv8ilp2u.dlg@40tude.net> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Trace: individual.net mEnXxBwNYEiYEHaWtVnMsA1C7YpkmmDsVpO/fEcdxA90CmUbNE Cancel-Lock: sha1:iHRhD+RKS26MaaIl6QG3Z/wFZP0= User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 In-Reply-To: <1g25lygstopv1$.1kxa4pv8ilp2u.dlg@40tude.net> X-Enigmail-Version: 1.6 Xref: csiph.com comp.lang.php:13895 richard, 2014-06-16 18:26: > On Sun, 15 Jun 2014 19:14:22 -0400, Lew Pitcher wrote: > >> On Sunday 15 June 2014 13:40, in comp.lang.php, "Denis McMahon" >> wrote: >> >>> On Sun, 15 Jun 2014 12:11:01 -0400, richard wrote: >>> >>>> Data is ordered by date. >>> >>> See, this is where the richard database design starts to bite the richard >>> arse. >>> >>> You are storing dates as a string representation of the date, so your >>> "order by date" sql clause causes the data to be ordered according to the >>> string collation for the relevant table. >>> >>> If you want the mysql rdbms to sort the data into date sequence, then you >>> need to store the data as dates. >> >> That's good advice (the best). >> >> However, richard can get away with >> SELECT * FROM table ORDER BY STR_TO_DATE(date,date_string_format); >> >> That will convert his string dates to "real" dates for the purposes of >> ordering the resultset rows, and should come out in ascending sequence by >> calendar date. >> http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date >> >> >> >> [snip] > > What is it with you people over this damn date issue? Because YOU said in your initial posting, that you use THIS statement: SELECT * FROM tracker ORDER BY date Now - if "date" is not a date type but just a text field, the ORDER BY clause will NOT work as intended: 01-07-1996 02-03-2010 10-10-1985 12-11-2014 You see - the order seems to be quite "random" - 1996, then 2010, then 1985 and finally 2014. This is caused by not using the date type for the column "date" - so the dates will just be treated as text and therefore ordered alphabetically but not by their date values. > I am only using date in the table as a unique identifier. No - you are also using the date to order stuff as shown in your first post. But I guess you even don't know what "ORDER BY" means nor what the concept of a "data type" is. -- Arno Welzel http://arnowelzel.de http://de-rec-fahrrad.de http://fahrradzukunft.de