 How to loop through the dates? [message #186117] |
Sun, 15 June 2014 16:11  |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
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);
|
|
|
|
|
|
| Re: How to loop through the dates? [message #186119 is a reply to message #186117] |
Sun, 15 June 2014 17:40  |
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, denismfmcmahon(at)gmail(dot)com
|
|
|
|
|
|
| Re: How to loop through the dates? [message #186122 is a reply to message #186119] |
Sun, 15 June 2014 17:59 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
|
|
| Re: How to loop through the dates? [message #186124 is a reply to message #186119] |
Sun, 15 June 2014 18:01 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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.
>
|
|
|
|
|
|
|
|
| Re: How to loop through the dates? [message #186127 is a reply to message #186122] |
Sun, 15 June 2014 19:14 |
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.
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| Re: How to loop through the dates? [message #186128 is a reply to message #186117] |
Sun, 15 June 2014 19:30 |
Tim Streater
Messages: 328
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
In article <ywx2wjj7uwar$(dot)105qt7ftnicew$(dot)dlg(at)40tude(dot)net>, richard
<noreply(at)example(dot)com> 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.
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
|
|
|
|
|
|
|
|
| Re: How to loop through the dates? [message #186131 is a reply to message #186130] |
Sun, 15 June 2014 20:34 |
Tim Streater
Messages: 328
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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.
--
"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
|
|
|
|
| Re: How to loop through the dates? [message #186132 is a reply to message #186122] |
Sun, 15 June 2014 21:39 |
Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
|
Senior Member |
|
|
richard <noreply(at)example(dot)com> wrote in news:cyaf96m0jpev$.1a2m47bjjrqjk$.dlg@
40tude.net:
> 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.
|
|
|
|
| Re: How to loop through the dates? [message #186133 is a reply to message #186131] |
Sun, 15 June 2014 21:32 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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 )
|
|
|
|
| Re: How to loop through the dates? [message #186134 is a reply to message #186119] |
Sun, 15 June 2014 23:14 |
Lew Pitcher
Messages: 60
Registered: April 2013
Karma: 0
|
Member |
|
|
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]
--
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request
|
|
|
|
|
|
| Re: How to loop through the dates? [message #186136 is a reply to message #186117] |
Mon, 16 June 2014 00:59 |
Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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;
}
}
/*Loop through each row and display records */
$last_date = "nomatch";
$tmp = 0;
for($i=1; $i<$num_results; $i++)
{
$row = mysqli_fetch_assoc($result);
if ($row['date'] <> $last_date)
{ // ---------- new table ----------
if ($tmp == 1)
{ // ---------- end last table ----------
echo "</table>\n\r";
echo "<br>\n\r";
$tmp = 0;
}
if ($tmp == 0)
{ // ---------- start new table ----------
echo "<table border='1'>\n\r";
echo " <th colspan='3'>" . $row['date'] . "</th>\n\r";
$last_date = $row['date'];
$tmp = 1;
}
}
if ($row['date'] == $last_date)
{ // ---------- cell data ----------
echo " <tr>\n\r";
echo " <td>" . $row['songID'] . "</td>\n\r";
echo " <td>" . $row['track'] . "</td>\n\r";
echo " <td>" . $row['artist'] . "</td>\n\r";
echo " <td>" . $row['hits'] . "</td>\n\r";
echo " </tr>\n\r";
}
}
echo "</table>\n\r";
echo "<br>\n\r";
mysqli_close($con);
--
Norman
Registered Linux user #461062
-Have you been to www.php.net yet?-
|
|
|
|
| Re: How to loop through the dates? [message #186144 is a reply to message #186131] |
Mon, 16 June 2014 16:19 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
| Re: How to loop through the dates? [message #186145 is a reply to message #186134] |
Mon, 16 June 2014 16:26 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
|
|
|
|
| Re: How to loop through the dates? [message #186150 is a reply to message #186148] |
Mon, 16 June 2014 17:57 |
Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
|
Senior Member |
|
|
richard <noreply(at)example(dot)com> wrote in news:3c2vdhslg8kw.w5icwsqwqrxa.dlg@
40tude.net:
> 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.
|
|
|
|
| Re: How to loop through the dates? [message #186152 is a reply to message #186145] |
Mon, 16 June 2014 18:14 |
Lew Pitcher
Messages: 60
Registered: April 2013
Karma: 0
|
Member |
|
|
On Monday 16 June 2014 12:26, in comp.lang.php, "richard"
<noreply(at)example(dot)com> wrote:
> 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
|
|
|
|
| Re: How to loop through the dates? [message #186159 is a reply to message #186144] |
Tue, 17 June 2014 00:44 |
Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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?-
|
|
|
|
|
|
| Re: How to loop through the dates? [message #186162 is a reply to message #186145] |
Tue, 17 June 2014 06:18 |
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.
--
Arno Welzel
http://arnowelzel.de
http://de-rec-fahrrad.de
http://fahrradzukunft.de
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]