I've got my report generator almost completed.
I only need to know how I can loop through the dates.
In BASIC, this is no big deal.
As a synopsis,
The table "tracker" is consulted for data.
Data is ordered by date.
Data is now transferred to the array $master.
From $master, data is outputted as desired.
In this case, each song played on a given date is listed.
$query = "SELECT * FROM tracker ORDER BY date";
$result = mysqli_query($con, $query);
@$num_results = mysqli_num_rows($result);
/*Loop through each row and display records */
for($i=0; $i<$num_results; $i++) {
$row = mysqli_fetch_assoc($result);
Denis McMahon Messages: 634 Registered: September 2010
Karma: 0
Senior Member
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. The mysql rdbms sorts dates perfectly.
It also sorts strings perfectly. But to sort dates it needs to know that
they are dates, and you have told it that they are strings. Because you
have told it that they are strings, when you ask it to sort them, it
sorts them as strings. So the sorting rules it applies to a column of
type datetime are different to the sorting rules it applies to a column
of type [var]char.
supposing you store the strings:
'06/21/2013', '07/21/12013' and '06/21/2014'
the sorted sequence will probably[1] be:
'06/21/2013'
'06/21/2014'
'07/21/2013'
[1] unless you have a weird collation defined
Because in most 'western alphabet' character sorting sequences, 6 comes
before 7, and ascii strings are sorted from left to right.
That aside, you don't seem to have actually described what the problem
that you want fixed is this time, nor have you given examples of (a) what
you expect to happen and (b) what is actually happening, so you'll not be
surprised that we can't diagnose and resolve the issue, and are instead
reduced to identifying, yet again, the generic flaws in your approach to
coding.
Instead of suppressing the error in the call to mysqli_num_rows, you
should first test whether mysqli_query() returned an error flag. You have
been told this several times.
The mechanisms you're using to loop through the result set and the
resulting array that you create are neither efficient or easy to follow.
As far as I can see, your code can be replaced with about 12 lines of
competently written php, with an extra 5 lines to add some comprehensive
error reporting and user friendly error handling.
Denis McMahon Messages: 634 Registered: September 2010
Karma: 0
Senior Member
On Sun, 15 Jun 2014 18:38:03 +0200, Luuk wrote:
> You seem to know how to loop through a number of results,
> so why can you not loop through 'the dates'?
His problem[1] may be[2] that he has stored dates as strings in a format
such that the character collation mysql is using for "order by" does not
sort them into what he considers to be date order.
[1] Or at least, one of them!
[2] It's richard, who really knows?
On Sun, 15 Jun 2014 17:40:43 +0000 (UTC), 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. The mysql rdbms sorts dates perfectly.
> It also sorts strings perfectly. But to sort dates it needs to know that
> they are dates, and you have told it that they are strings. Because you
> have told it that they are strings, when you ask it to sort them, it
> sorts them as strings. So the sorting rules it applies to a column of
> type datetime are different to the sorting rules it applies to a column
> of type [var]char.
>
> supposing you store the strings:
>
> '06/21/2013', '07/21/12013' and '06/21/2014'
>
> the sorted sequence will probably[1] be:
>
> '06/21/2013'
> '06/21/2014'
> '07/21/2013'
>
> [1] unless you have a weird collation defined
>
> Because in most 'western alphabet' character sorting sequences, 6 comes
> before 7, and ascii strings are sorted from left to right.
>
> That aside, you don't seem to have actually described what the problem
> that you want fixed is this time, nor have you given examples of (a) what
> you expect to happen and (b) what is actually happening, so you'll not be
> surprised that we can't diagnose and resolve the issue, and are instead
> reduced to identifying, yet again, the generic flaws in your approach to
> coding.
>
> Instead of suppressing the error in the call to mysqli_num_rows, you
> should first test whether mysqli_query() returned an error flag. You have
> been told this several times.
>
> The mechanisms you're using to loop through the result set and the
> resulting array that you create are neither efficient or easy to follow.
>
> As far as I can see, your code can be replaced with about 12 lines of
> competently written php, with an extra 5 lines to add some comprehensive
> error reporting and user friendly error handling.
In this instance "date" in the line you left quoted, refers to a column
name.
What if it had been "hits" instead or "title"?
You guys are hell bent on this date issue you forget that columns can hold
any data in any manner.Regardless of their name.
The only two things the table cares about is, the data is either a string
or a numeric.
On 15-6-2014 19:40, 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. The mysql rdbms sorts dates perfectly.
> It also sorts strings perfectly. But to sort dates it needs to know that
> they are dates, and you have told it that they are strings. Because you
> have told it that they are strings, when you ask it to sort them, it
> sorts them as strings. So the sorting rules it applies to a column of
> type datetime are different to the sorting rules it applies to a column
> of type [var]char.
>
> supposing you store the strings:
>
> '06/21/2013', '07/21/12013' and '06/21/2014'
>
> the sorted sequence will probably[1] be:
>
> '06/21/2013'
> '06/21/2014'
> '07/21/2013'
>
> [1] unless you have a weird collation defined
>
> Because in most 'western alphabet' character sorting sequences, 6 comes
> before 7, and ascii strings are sorted from left to right.
>
up until here, you did not visit the link to his site, which he gave,
and that's why above i see some things dat are not true.
The link is showing the date fields in the correct order:
01-06-2014
01-06-2014
01-06-2014
02-06-2014
02-06-2014
02-06-2014
02-06-2014
02-06-2014
02-06-2014
02-06-2014
02-06-2014
02-06-2014
03-06-2014
03-06-2014
03-06-2014
03-06-2014
03-06-2014
03-06-2014
03-06-2014
03-06-2014
03-06-2014
04-06-2014
04-06-2014
04-06-2014
04-06-2014
04-06-2014
04-06-2014
04-06-2014
04-06-2014
04-06-2014
> That aside, you don't seem to have actually described what the problem
> that you want fixed is this time, nor have you given examples of (a) what
> you expect to happen and (b) what is actually happening, so you'll not be
> surprised that we can't diagnose and resolve the issue, and are instead
> reduced to identifying, yet again, the generic flaws in your approach to
> coding.
@richard:
it's the link which gave an example, and some people (most of them) do
NOT want to follow a link to a website. It's much better to give a short
example of what's happening in the message with your problem, than
referring to a website.
>
> Instead of suppressing the error in the call to mysqli_num_rows, you
> should first test whether mysqli_query() returned an error flag. You have
> been told this several times.
>
> The mechanisms you're using to loop through the result set and the
> resulting array that you create are neither efficient or easy to follow.
>
> As far as I can see, your code can be replaced with about 12 lines of
> competently written php, with an extra 5 lines to add some comprehensive
> error reporting and user friendly error handling.
>
On 15-6-2014 19:59, richard wrote:
> You guys are hell bent on this date issue you forget that columns can hold
> any data in any manner.Regardless of their name.
stop doing that, that's not smart....
> The only two things the table cares about is, the data is either a string
> or a numeric.
Denis McMahon Messages: 634 Registered: September 2010
Karma: 0
Senior Member
On Sun, 15 Jun 2014 20:01:38 +0200, Luuk wrote:
> up until here, you did not visit the link to his site, which he gave,
> and that's why above i see some things dat are not true.
Note that all the dates have the same month and year component at
present. What will happen if / when his list contains dates from more
than one month?
I'm pretty sure he'll sort 01-07-2014 before 02-06-2014.
I'm still not sure how what he wants as output is differing from what
he's getting as output though.
Denis McMahon Messages: 634 Registered: September 2010
Karma: 0
Senior Member
On Sun, 15 Jun 2014 13:59:01 -0400, richard wrote:
> In this instance "date" in the line you left quoted, refers to a column
> name.
Yes, but it's a column that you appear to be using to store date
information.
> You guys are hell bent on this date issue you forget that columns can
> hold any data in any manner. Regardless of their name.
No we don't.
> The only two things the table cares about is, the data is either a
> string or a numeric.
See, this is where you're plain wrong. The mysql rdbms defines several
different types of data, and when it comes to performing various
operations on that data, it cares for a lot more than "is this column
numeric or string data".
This is the typical Richard that pisses us off so much again. You're
presuming to lecture me on the internals of the mysql rdbms, a subject
upon which you clearly know absolutely nothing, and the lecture you're
presuming to deliver is wholly factually wrong.
You haven't told us what the problem is you are experiencing.
> print $master[$i]['date'];
but from this I deduce that the dates are not coming out in the order
you expect (it's irritating to have to deduce what problem people are
experiencing. Cats, dogs, horses, etc, we forgive, because they can't
speak. You, OTOH, can. So please state, in future, what the problem is
and give *examples* of incorrect output).
My advice is not to store dates as strings (or even for whatever data
type mysql uses), but as integers, and use the number of seconds since
the epoch. You can use strtotime() to convert dates/times to a number
of seconds.
Then the sort will work properly.
Then for output,
echo date ('M jS, Y', $value);
will output the date in a suitable format.
--
"If you're not able to ask questions and deal with the answers without feeling
that someone has called your intelligence or competence into question, don't
ask questions on Usenet where the answers won't be carefully tailored to avoid
tripping your hair-trigger insecurities." - D M Procida, UCSM
On 15-6-2014 21:07, Denis McMahon wrote:
> On Sun, 15 Jun 2014 20:01:38 +0200, Luuk wrote:
>
>> up until here, you did not visit the link to his site, which he gave,
>> and that's why above i see some things dat are not true.
>
> Note that all the dates have the same month and year component at
> present. What will happen if / when his list contains dates from more
> than one month?
>
> I'm pretty sure he'll sort 01-07-2014 before 02-06-2014.
>
he will ask in about 1 days.... ;)
> I'm still not sure how what he wants as output is differing from what
> he's getting as output though.
>
it's a mistery to me...
(c) Dire Straits: Private Investigations
> On 15-6-2014 21:30, Tim Streater wrote:
>> Then for output,
>>
>> echo date ('M jS, Y', $value);
>>
>> will output the date in a suitable format.
>
> only if you live in a country where they put the month first......
>
> here, where i live, the do day-month-year (15-06-2014)
So do I. But richard appears to assume that his website is only to be
seen by Yanks. The string I put above for date is acceptable to me,
however, in as much as it leads to an unambiguous date such as:
Jun 8th, 2014
unlike:
06-08-2014
which certainly *is* ambiguous.
--
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
> In this instance "date" in the line you left quoted, refers to a column
> name.
Yes, we understand this. You don't appear to understand that *calling* a string column
"date" doesn't actually *make* it a date. A string column named "date", and a DATE
column are distinctly different data types.
> What if it had been "hits" instead or "title"?
Then you wouldn't have a problem -- because SQL sorts both numeric data and string data
in the order you expect it to be sorted, but when you store a date as a MMDDYYYY string,
the sorted order is *not* what you expect.
> You guys are hell bent on this date issue you forget that columns can hold
> any data in any manner.Regardless of their name.
We're not forgetting anything. You are failing to realize that strings are not appropriate
storage types for date data.
> The only two things the table cares about is, the data is either a string
> or a numeric.
Patently false. Those are not the only types of data that can be stored in a SQL database.
You, in particular, have no excuse for being unaware of that fact, since you have been told
many times that you should store dates using the DATE data type. This you have
truculently and ignorantly refused to do.
And now you have a perfect example of *why* you were advised to use the DATE date
type to store date information.
On 15-6-2014 22:34, Tim Streater wrote:
> In article <1la17b-rhh(dot)ln1(at)luuk(dot)invalid(dot)lan>, Luuk <luuk(at)invalid(dot)lan>
> wrote:
>
>> On 15-6-2014 21:30, Tim Streater wrote:
>>> Then for output,
>>>
>>> echo date ('M jS, Y', $value);
>>>
>>> will output the date in a suitable format.
>>
>> only if you live in a country where they put the month first......
>>
>> here, where i live, the do day-month-year (15-06-2014)
>
> So do I. But richard appears to assume that his website is only to be
> seen by Yanks. The string I put above for date is acceptable to me,
> however, in as much as it leads to an unambiguous date such as:
>
> Jun 8th, 2014
>
> unlike:
>
> 06-08-2014
>
> which certainly *is* ambiguous.
>
it is not as ambiguous as you want it to be,
if the list is long enough .....
( more than 12 days should do )
> 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);
Not sure how he managed to mess it up with his aeons of programming
experience. The basic algorithm he needs while walking through the data
set is the same in every programming language, he just needs to implement
it correctly.
Now stop laughing at the back. I know what the thought of RtS ever
implementing anything correctly does to you, but hush.
On 06/15/2014 12:11 PM, richard wrote:
> http://mroldies.net/radio/tracker2.php
>
>
> I've got my report generator almost completed.
> I only need to know how I can loop through the dates.
> In BASIC, this is no big deal.
>
> As a synopsis,
> The table "tracker" is consulted for data.
> Data is ordered by date.
> Data is now transferred to the array $master.
> From $master, data is outputted as desired.
> In this case, each song played on a given date is listed.
>
>
>
>
> $query = "SELECT * FROM tracker ORDER BY date";
> $result = mysqli_query($con, $query);
> @$num_results = mysqli_num_rows($result);
>
> /*Loop through each row and display records */
> for($i=0; $i<$num_results; $i++) {
> $row = mysqli_fetch_assoc($result);
>
> $master[$i]['date']=$row['date'];
> $master[$i]['songID']=$row['songID'];
> $master[$i]['track']=$row['track'];
> $master[$i]['artist']=$row['artist'];
> $master[$i]['hits']=$row['hits'];
> }
>
> for($i=0;$i<$num_results;$i++){
>
> print $master[$i]['date'];
> echo "<br>";
> }
> echo "<table border=\"1\">";
>
> $newdate=$master[0]['date'];
>
> $ct=0;
> print "<tr><td>".$master[$ct]['date']."</td></tr>";
> while ($ct<$num_results){
>
> if ($master[$ct]['date']==$newdate) {
> print "<tr><td>".$master[$ct]['track']."</td></tr>";
> }
>
> $ct++;
> }
>
> echo "</table>";
>
>
> mysqli_close($con);
>
Since you don't appear to be doing anything with $master, why not
just output you table directly?
$query = "SELECT `date`, songID, track, artist, hits FROM tracker
ORDER BY `date` ASC";
$result = mysqli_query($con, $query);
if (!$result)
{
echo "The query failed with the following MySQL error:<br>";
echo mysqli_errno($con) . ": " . mysqli_error($con);
mysqli_close($con);
exit;
} else {
$num_results = mysqli_num_rows($result);
if ($num_results == NULL)
{
echo "No records to display!";
exit;
}
}
On Sun, 15 Jun 2014 21:34:19 +0100, Tim Streater wrote:
> In article <1la17b-rhh(dot)ln1(at)luuk(dot)invalid(dot)lan>, Luuk <luuk(at)invalid(dot)lan>
> wrote:
>
>> On 15-6-2014 21:30, Tim Streater wrote:
>>> Then for output,
>>>
>>> echo date ('M jS, Y', $value);
>>>
>>> will output the date in a suitable format.
>>
>> only if you live in a country where they put the month first......
>>
>> here, where i live, the do day-month-year (15-06-2014)
>
> So do I. But richard appears to assume that his website is only to be
> seen by Yanks. The string I put above for date is acceptable to me,
> however, in as much as it leads to an unambiguous date such as:
>
> Jun 8th, 2014
>
> unlike:
>
> 06-08-2014
>
> which certainly *is* ambiguous.
As y'all like to kindly point out, "Read the frickin manual!".
The options allow you to show the date in ANY format you want.
For my pusposes, I chose that format because the leading numbers will be
the first to change. Making it easier for the code to spot the changes.
As I learned to do in BASIC.
Sometimes, when you have a very long variable name, and only change the
last character to differenitate two variables, the machine may have
problems.
Now if you have the leading character change, no problems.
ThisisoeverylongstringnameA
ThisisoeverylongstringnameB
As compared to:
AThisisoeverylongstringname
BThisisoeverylongstringname
FYI, redcoat, my website that is visible to the public does not use this
format for dates.
I use this format ONLY in my tables.
Kindly note that on my home page, I show the format as
16th of June,2014.
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"
> <denismfmcmahon(at)gmail(dot)com> 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#functio n_str-to-date
>
>
>
> [snip]
What is it with you people over this damn date issue?
I am only using date in the table as a unique identifier.
I could have chosen any number of other items but this works for me quite
well.
My table cell for the unique identifier is shown as
01-01-2014 60-001
A combination of the date and songID.
When a song is played more than once on a given date, the hit counter is
increased.
> What is it with you people over this damn date issue?
What is it with *you* over the damn date issue, Richard the Stupid? Why do you persist in
storing dates as strings, even *after* you see the problems it's causing you?
> I am only using date in the table as a unique identifier.
> I could have chosen any number of other items but this works for me quite
> well.
Oh, okay. Since it's working for you quite well, I guess you don't need any help with it after all,
do you?
On Sun, 15 Jun 2014 19:07:11 +0000 (UTC), Denis McMahon wrote:
> On Sun, 15 Jun 2014 20:01:38 +0200, Luuk wrote:
>
>> up until here, you did not visit the link to his site, which he gave,
>> and that's why above i see some things dat are not true.
>
> Note that all the dates have the same month and year component at
> present. What will happen if / when his list contains dates from more
> than one month?
>
> I'm pretty sure he'll sort 01-07-2014 before 02-06-2014.
It did. I changed dates just to see what happens.
I may drop the date part of the ID and just use the songID.
>
> I'm still not sure how what he wants as output is differing from what
> he's getting as output though.
> On Sun, 15 Jun 2014 19:07:11 +0000 (UTC), Denis McMahon wrote:
>
>> On Sun, 15 Jun 2014 20:01:38 +0200, Luuk wrote:
>>
>>> up until here, you did not visit the link to his site, which he gave,
>>> and that's why above i see some things dat are not true.
>>
>> Note that all the dates have the same month and year component at
>> present. What will happen if / when his list contains dates from more
>> than one month?
>>
>> I'm pretty sure he'll sort 01-07-2014 before 02-06-2014.
>
> It did. I changed dates just to see what happens.
The big question is whether you sort 01-07-2014 before, or after, 07-01-2013.
> 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"
>> <denismfmcmahon(at)gmail(dot)com> 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.
[snip]
> What is it with you people over this damn date issue?
Richard. I *DON'T CARE* about your "date issue". But, you apparently do,
since *YOU* asked how to get your data into date sequence.
Since you don't seem to appreciate the help you get, all I can say is....
PLONK
--
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request
On 06/16/2014 12:19 PM, richard wrote:
> On Sun, 15 Jun 2014 21:34:19 +0100, Tim Streater wrote:
>
>> In article <1la17b-rhh(dot)ln1(at)luuk(dot)invalid(dot)lan>, Luuk <luuk(at)invalid(dot)lan>
>> wrote:
>>
>>> On 15-6-2014 21:30, Tim Streater wrote:
>>>> Then for output,
>>>>
>>>> echo date ('M jS, Y', $value);
>>>>
>>>> will output the date in a suitable format.
>>>
>>> only if you live in a country where they put the month first......
>>>
>>> here, where i live, the do day-month-year (15-06-2014)
>>
>> So do I. But richard appears to assume that his website is only to be
>> seen by Yanks. The string I put above for date is acceptable to me,
>> however, in as much as it leads to an unambiguous date such as:
>>
>> Jun 8th, 2014
>>
>> unlike:
>>
>> 06-08-2014
>>
>> which certainly *is* ambiguous.
>
> As y'all like to kindly point out, "Read the frickin manual!".
> The options allow you to show the date in ANY format you want.
> For my pusposes, I chose that format because the leading numbers will be
> the first to change. Making it easier for the code to spot the changes.
>
As long as MySQL knows it's a proper date, you can do date *things*
with them...
> As I learned to do in BASIC.
> Sometimes, when you have a very long variable name, and only change the
> last character to differenitate two variables, the machine may have
> problems.
> Now if you have the leading character change, no problems.
>
> ThisisoeverylongstringnameA
> ThisisoeverylongstringnameB
>
PHP has no problem telling those apart...
As compared to:
> AThisisoeverylongstringname
> BThisisoeverylongstringname
>
or those for that matter.
> FYI, redcoat, my website that is visible to the public does not use this
> format for dates.
> I use this format ONLY in my tables.
> Kindly note that on my home page, I show the format as
> 16th of June,2014.
>
--
Norman
Registered Linux user #461062
-Have you been to www.php.net yet?-
Arno Welzel Messages: 317 Registered: October 2011
Karma: 0
Senior Member
richard, 2014-06-15 19:59:
[...]
> You guys are hell bent on this date issue you forget that columns can hold
> any data in any manner.Regardless of their name.
> The only two things the table cares about is, the data is either a string
> or a numeric.
No. A database can clearly distinguish between strings, integers,
floats, dates etc..
Did I already mention that software development is not the right hobby
for you?
Arno Welzel Messages: 317 Registered: October 2011
Karma: 0
Senior Member
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"
>> <denismfmcmahon(at)gmail(dot)com> 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#functio n_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.
Calendar
Search
Help
Members
Register
Login
Home
]