Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #13856 > unrolled thread

How to loop through the dates?

Back to article view | Back to comp.lang.php

Contents

  How to loop through the dates? richard <noreply@example.com> - 2014-06-15 12:11 -0400
    Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 18:38 +0200
      Re: How to loop through the dates? Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-15 17:43 +0000
        Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 20:03 +0200
    Re: How to loop through the dates? Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-15 17:40 +0000
      Re: How to loop through the dates? richard <noreply@example.com> - 2014-06-15 13:59 -0400
        Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 20:27 +0200
        Re: How to loop through the dates? Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-15 19:14 +0000
        Re: How to loop through the dates? Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-15 21:39 +0000
        Re: How to loop through the dates? Arno Welzel <usenet@arnowelzel.de> - 2014-06-17 08:12 +0200
      Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 20:01 +0200
        Re: How to loop through the dates? Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-15 19:07 +0000
          Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 21:58 +0200
            Re: How to loop through the dates? Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-16 00:55 +0000
          Re: How to loop through the dates? richard <noreply@example.com> - 2014-06-16 13:02 -0400
            Re: How to loop through the dates? Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-16 17:57 +0000
      Re: How to loop through the dates? Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2014-06-15 19:14 -0400
        Re: How to loop through the dates? richard <noreply@example.com> - 2014-06-16 12:26 -0400
          Re: How to loop through the dates? Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-16 16:39 +0000
          Re: How to loop through the dates? Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2014-06-16 14:14 -0400
          Re: How to loop through the dates? Arno Welzel <usenet@arnowelzel.de> - 2014-06-17 08:18 +0200
    Re: How to loop through the dates? Tim Streater <timstreater@greenbee.net> - 2014-06-15 20:30 +0100
      Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 22:01 +0200
        Re: How to loop through the dates? Tim Streater <timstreater@greenbee.net> - 2014-06-15 21:34 +0100
          Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 23:32 +0200
          Re: How to loop through the dates? richard <noreply@example.com> - 2014-06-16 12:19 -0400
            Re: How to loop through the dates? Norman Peelman <npeelman@cfl.rr.com> - 2014-06-16 20:44 -0400
    Re: How to loop through the dates? Norman Peelman <npeelman@cfl.rr.com> - 2014-06-15 20:59 -0400

Page 1 of 2  [1] 2  Next page →

#13856 — How to loop through the dates?

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);

[toc] | [next] | [standalone]

#13857

On 15-6-2014 18:11, richard wrote:
> In BASIC, this is no big deal.
>

... (sigh) ...

> for($i=0;$i<$num_results;$i++){
>

You seem to know how to loop through a number of results,
so why can you not loop through 'the dates'?

[toc] | [prev] | [next] | [standalone]

#13859

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?

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#13861

On 15-6-2014 19:43, Denis McMahon wrote:
> [2] It's richard, who really knows?

assumption is the mother....

[toc] | [prev] | [next] | [standalone]

#13858

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@gmail.com

[toc] | [prev] | [next] | [standalone]

#13860

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.

[toc] | [prev] | [next] | [standalone]

#13863

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.

as far as PHP is concerned you may be right, but you are not:
http://www.php.net//manual/en/language.types.php

and as far a MySQL is concerned you are not:
http://dev.mysql.com/doc/refman/5.6/en/data-type-overview.html

;-)

[toc] | [prev] | [next] | [standalone]

#13865

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@gmail.com

[toc] | [prev] | [next] | [standalone]

#13870

richard <noreply@example.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.

[toc] | [prev] | [next] | [standalone]

#13894

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
http://arnowelzel.de
http://de-rec-fahrrad.de
http://fahrradzukunft.de

[toc] | [prev] | [next] | [standalone]

#13862

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.
>

[toc] | [prev] | [next] | [standalone]

#13864

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, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#13867

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
http://www.youtube.com/watch?v=IxS-ICzjO6I

[toc] | [prev] | [next] | [standalone]

#13873

On Sun, 15 Jun 2014 21:58:53 +0200, Luuk wrote:

> On 15-6-2014 21:07, Denis McMahon wrote:

>> 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...

My current guess is that he wants something like this:

http://www.sined.co.uk/tmp/rts/test1.php

but he's messed up generating the table.

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.

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#13883

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.

http://mroldies.net/radio/tracker2.php

[toc] | [prev] | [next] | [standalone]

#13885

richard <noreply@example.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.

[toc] | [prev] | [next] | [standalone]

#13872

On Sunday 15 June 2014 13:40, in comp.lang.php, "Denis McMahon"
<denismfmcmahon@gmail.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#function_str-to-date



[snip]

-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request

[toc] | [prev] | [next] | [standalone]

#13880

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@gmail.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#function_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.

[toc] | [prev] | [next] | [standalone]

#13882

richard <noreply@example.com> wrote in news:1g25lygstopv1$.1kxa4pv8ilp2u.dlg@
40tude.net:


> 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?

[toc] | [prev] | [next] | [standalone]

#13887

On Monday 16 June 2014 12:26, in comp.lang.php, "richard"
<noreply@example.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@gmail.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

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.lang.php

csiph-web