FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » How to loop through the dates?
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
How to loop through the dates? [message #186117] Sun, 15 June 2014 12:11 Go to next message
Mr Oldies is currently offline  Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 #186118 is a reply to message #186117] Sun, 15 June 2014 12:38 Go to previous messageGo to next message
Luuk is currently offline  Luuk
Messages: 329
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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'?
Re: How to loop through the dates? [message #186119 is a reply to message #186117] Sun, 15 June 2014 13:40 Go to previous messageGo to next message
Denis McMahon is currently offline  Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 #186120 is a reply to message #186118] Sun, 15 June 2014 13:43 Go to previous messageGo to next message
Denis McMahon is currently offline  Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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(at)gmail(dot)com
Re: How to loop through the dates? [message #186122 is a reply to message #186119] Sun, 15 June 2014 13:59 Go to previous messageGo to next message
Mr Oldies is currently offline  Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 #186123 is a reply to message #186120] Sun, 15 June 2014 14:03 Go to previous messageGo to next message
Luuk is currently offline  Luuk
Messages: 329
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On 15-6-2014 19:43, Denis McMahon wrote:
> [2] It's richard, who really knows?

assumption is the mother....
Re: How to loop through the dates? [message #186124 is a reply to message #186119] Sun, 15 June 2014 14:01 Go to previous messageGo to next message
Luuk is currently offline  Luuk
Messages: 329
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 #186125 is a reply to message #186122] Sun, 15 June 2014 14:27 Go to previous messageGo to next message
Luuk is currently offline  Luuk
Messages: 329
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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

;-)
Re: How to loop through the dates? [message #186126 is a reply to message #186124] Sun, 15 June 2014 15:07 Go to previous messageGo to next message
Denis McMahon is currently offline  Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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(at)gmail(dot)com
Re: How to loop through the dates? [message #186127 is a reply to message #186122] Sun, 15 June 2014 15:14 Go to previous messageGo to next message
Denis McMahon is currently offline  Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 15:30 Go to previous messageGo to next message
Tim Streater is currently offline  Tim Streater
Messages: 328
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 #186129 is a reply to message #186126] Sun, 15 June 2014 15:58 Go to previous messageGo to next message
Luuk is currently offline  Luuk
Messages: 329
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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
Re: How to loop through the dates? [message #186130 is a reply to message #186128] Sun, 15 June 2014 16:01 Go to previous messageGo to next message
Luuk is currently offline  Luuk
Messages: 329
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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)
Re: How to loop through the dates? [message #186131 is a reply to message #186130] Sun, 15 June 2014 16:34 Go to previous messageGo to next message
Tim Streater is currently offline  Tim Streater
Messages: 328
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 17:39 Go to previous messageGo to next message
Doug Miller is currently offline  Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 17:32 Go to previous messageGo to next message
Luuk is currently offline  Luuk
Messages: 329
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 19:14 Go to previous messageGo to next message
Lew Pitcher is currently offline  Lew Pitcher
Messages: 60
Registered: April 2013
Karma: 0
Member
add to buddy list
ignore all messages by this user
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 #186135 is a reply to message #186129] Sun, 15 June 2014 20:55 Go to previous messageGo to next message
Denis McMahon is currently offline  Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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(at)gmail(dot)com
Re: How to loop through the dates? [message #186136 is a reply to message #186117] Sun, 15 June 2014 20:59 Go to previous messageGo to next message
Norman Peelman is currently offline  Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 12:19 Go to previous messageGo to next message
Mr Oldies is currently offline  Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 12:26 Go to previous messageGo to next message
Mr Oldies is currently offline  Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 #186147 is a reply to message #186145] Mon, 16 June 2014 12:39 Go to previous messageGo to next message
Doug Miller is currently offline  Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
richard <noreply(at)example(dot)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?
Re: How to loop through the dates? [message #186148 is a reply to message #186126] Mon, 16 June 2014 13:02 Go to previous messageGo to next message
Mr Oldies is currently offline  Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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
Re: How to loop through the dates? [message #186150 is a reply to message #186148] Mon, 16 June 2014 13:57 Go to previous messageGo to next message
Doug Miller is currently offline  Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 14:14 Go to previous messageGo to next message
Lew Pitcher is currently offline  Lew Pitcher
Messages: 60
Registered: April 2013
Karma: 0
Member
add to buddy list
ignore all messages by this user
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] Mon, 16 June 2014 20:44 Go to previous messageGo to next message
Norman Peelman is currently offline  Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 #186161 is a reply to message #186122] Tue, 17 June 2014 02:12 Go to previous messageGo to next message
Arno Welzel is currently offline  Arno Welzel
Messages: 317
Registered: October 2011
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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
Re: How to loop through the dates? [message #186162 is a reply to message #186145] Tue, 17 June 2014 02:18 Go to previous message
Arno Welzel is currently offline  Arno Welzel
Messages: 317
Registered: October 2011
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: What is the purpose of "&" in this code?
Next Topic: why is it always an endless loop?
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Oct 22 13:29:48 EDT 2017

Total time taken to generate the page: 0.01118 seconds