newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

Hi all

I'm translating some code from another server-side language into PHP,
and I need something that 'summarizes' results found from a MySQL
SELECT. I.e. -

$foundTrackingRows=mysql_query("SELECT...
while ($TrackingRow = mysql_fetch_object($foundTrackingRows)) {...

...such that the while loop only loops *ONCE per unique _date_ found
(regardless of the hour/min./sec.)* in my column which is of type
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
PRIMARY KEY

For example, if I have column values like these:
2009-08-01 07:01:00
2009-07-30 18:16:37
2009-07-30 17:49:06
2009-07-27 17:35:52
2009-07-27 17:24:21
2009-07-27 17:23:03
...then my while { loop would only fire 3 times.

I do my RTFM; can someone just give me a good point in the right
direction.
Thanks!

------------
John Butler/(Govinda)
govinda.webdnatalk@gmail.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date(DAY!)* in a timestamp column?

Govinda wrote:
> Hi all
>
> I'm translating some code from another server-side language into PHP,
> and I need something that 'summarizes' results found from a MySQL
> SELECT. I.e. -
>
> $foundTrackingRows=mysql_query("SELECT...
> while ($TrackingRow = mysql_fetch_object($foundTrackingRows)) {...
>
> ..such that the while loop only loops *ONCE per unique _date_ found
> (regardless of the hour/min./sec.)* in my column which is of type
> TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY
>
> For example, if I have column values like these:
> 2009-08-01 07:01:00
> 2009-07-30 18:16:37
> 2009-07-30 17:49:06
> 2009-07-27 17:35:52
> 2009-07-27 17:24:21
> 2009-07-27 17:23:03
> ..then my while { loop would only fire 3 times.
>
> I do my RTFM; can someone just give me a good point in the right direction.
> Thanks!

You need to do this on the mysql side, not in php - php can't summarize
the data before processing it, so you need to use something like the
date() function in mysql on your timestamp column.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_date

without knowing your original query it's hard to give an example, but:

select distinct date(timestamp_column) from table;

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

--Apple-Mail-5-367593035
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

>
> You need to do this on the mysql side, not in php - php can't
> summarize the data before processing it, so you need to use
> something like the date() function in mysql on your timestamp column.
>
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_date
>
> without knowing your original query it's hard to give an example, but:
>
> select distinct date(timestamp_column) from table;

Thanks Chris,

I am getting closer, but so far it is not iterating only once per
unique 'date part of the datetime expression', it is returning all the
rows in the table, including those with the very same date but
different time in the value of the 'solarLandingDateTime' column.
There is not alot of discussion in the mysql docs that I saw about how
to work with DISTINCT. I need to grab data out of the 3 columns:
solarLandingIP, solarLandingDir, solarLandingDateTime (this part of
my SELECT is working).

This is what I have:

$foundTrackingRows=mysql_query("SELECT DISTINCT
DATE(solarLandingDateTime) solarLandingIP, solarLandingDir,
solarLandingDateTime FROM ".$whichTable." ORDER BY
solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query failed:
" .mysql_error());

-Govinda
--Apple-Mail-5-367593035--

Re: newbie: how to return one iteration *per unique date

On Sun, Aug 2, 2009 at 8:02 PM, Govinda wrote=
:
>>
>> You need to do this on the mysql side, not in php - php can't summarize
>> the data before processing it, so you need to use something like the dat=
e()
>> function in mysql on your timestamp column.
>>
>>
>> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#func=
tion_date
>>
>> without knowing your original query it's hard to give an example, but:
>>
>> select distinct date(timestamp_column) from table;
>
> Thanks Chris,
>
> I am getting closer, but so far it is not iterating only once per unique
> 'date part of the datetime expression', it is returning all the rows in t=
he
> table, including those with the very same date but different time in the
> value of the 'solarLandingDateTime' column. =A0There is not alot of discu=
ssion
> in the mysql docs that I saw about how to work with DISTINCT. =A0I need t=
o
> grab data out of the 3 columns: solarLandingIP, solarLandingDir,
> solarLandingDateTime =A0(this part of my SELECT is working).
>
> This is what I have:
>
> $foundTrackingRows=3Dmysql_query("SELECT DISTINCT DATE(solarLandingDateTi=
me)
> solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".$whichTable.=
"
> ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query
> failed: " .mysql_error());
>
> -Govinda

try

foundTrackingRows=3Dmysql_query("SELECT DISTINCT
DATE_FORMAT('%Y-%m,%d',solarLandingDateTime)
solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".$whichTable."
ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query
failed: " .mysql_error());

--=20

Bastien

Cat, the other other white meat

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

> On Sun, Aug 2, 2009 at 8:02 PM,
> Govinda wrote:
>>>
>>> You need to do this on the mysql side, not in php - php can't
>>> summarize
>>> the data before processing it, so you need to use something like
>>> the date()
>>> function in mysql on your timestamp column.
>>>
>>>
>>> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_date
>>>
>>> without knowing your original query it's hard to give an example,
>>> but:
>>>
>>> select distinct date(timestamp_column) from table;
>>
>> Thanks Chris,
>>
>> I am getting closer, but so far it is not iterating only once per
>> unique
>> 'date part of the datetime expression', it is returning all the
>> rows in the
>> table, including those with the very same date but different time
>> in the
>> value of the 'solarLandingDateTime' column. There is not alot of
>> discussion
>> in the mysql docs that I saw about how to work with DISTINCT. I
>> need to
>> grab data out of the 3 columns: solarLandingIP, solarLandingDir,
>> solarLandingDateTime (this part of my SELECT is working).
>>
>> This is what I have:
>>
>> $foundTrackingRows=mysql_query("SELECT DISTINCT
>> DATE(solarLandingDateTime)
>> solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".
>> $whichTable."
>> ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or
>> die("query
>> failed: " .mysql_error());
>>
>> -Govinda
>
> try
>
> foundTrackingRows=mysql_query("SELECT DISTINCT
> DATE_FORMAT('%Y-%m,%d',solarLandingDateTime)
> solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".
> $whichTable."
> ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query
> failed: " .mysql_error());


Bastien,

I just did try that. I also tried this, (thinking there was a typo in
your version, in the DATE_FORMAT formatting string), :

$foundTrackingRows=mysql_query("SELECT DISTINCT DATE_FORMAT('%Y-%m-
%d',solarLandingDateTime) solarLandingIP, solarLandingDir,
solarLandingDateTime FROM ".$whichTable." ORDER BY
solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query failed:
" .mysql_error());

still no luck.

-Govinda



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date

On Sun, Aug 2, 2009 at 11:08 PM, Govinda wrot=
e:
>> On Sun, Aug 2, 2009 at 8:02 PM, Govinda
>> wrote:
>>>>
>>>> You need to do this on the mysql side, not in php - php can't summariz=
e
>>>> the data before processing it, so you need to use something like the
>>>> date()
>>>> function in mysql on your timestamp column.
>>>>
>>>>
>>>>
>>>> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#fu=
nction_date
>>>>
>>>> without knowing your original query it's hard to give an example, but:
>>>>
>>>> select distinct date(timestamp_column) from table;
>>>
>>> Thanks Chris,
>>>
>>> I am getting closer, but so far it is not iterating only once per uniqu=
e
>>> 'date part of the datetime expression', it is returning all the rows in
>>> the
>>> table, including those with the very same date but different time in th=
e
>>> value of the 'solarLandingDateTime' column. =A0There is not alot of
>>> discussion
>>> in the mysql docs that I saw about how to work with DISTINCT. =A0I need=
to
>>> grab data out of the 3 columns: solarLandingIP, solarLandingDir,
>>> solarLandingDateTime =A0(this part of my SELECT is working).
>>>
>>> This is what I have:
>>>
>>> $foundTrackingRows=3Dmysql_query("SELECT DISTINCT
>>> DATE(solarLandingDateTime)
>>> solarLandingIP, solarLandingDir, solarLandingDateTime FROM
>>> ".$whichTable."
>>> ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query
>>> failed: " .mysql_error());
>>>
>>> -Govinda
>>
>> try
>>
>> foundTrackingRows=3Dmysql_query("SELECT DISTINCT
>> DATE_FORMAT('%Y-%m,%d',solarLandingDateTime)
>> solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".$whichTable=
.."
>> ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query
>> failed: " .mysql_error());
>
>
> Bastien,
>
> I just did try that. =A0I also tried this, (thinking there was a typo in =
your
> version, in the DATE_FORMAT formatting string), :
>
> $foundTrackingRows=3Dmysql_query("SELECT DISTINCT
> DATE_FORMAT('%Y-%m-%d',solarLandingDateTime) solarLandingIP,
> solarLandingDir, solarLandingDateTime FROM ".$whichTable." ORDER BY
> solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query failed: "
> .mysql_error());
>
> still no luck.
>
> -Govinda
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

What does the resultset look like?

--=20

Bastien

Cat, the other other white meat

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

--Apple-Mail-7-379093013
Content-Type: text/plain;
charset=US-ASCII;
format=flowed
Content-Transfer-Encoding: 7bit

>
> What does the resultset look like?

I am getting every record in the table back
--Apple-Mail-7-379093013--

Re: newbie: how to return one iteration *per unique date

On Sun, Aug 2, 2009 at 11:13 PM, Govinda wrote:
>>
>> What does the resultset look like?
>
> I am getting every record in the table back

my bad, i reversed the mysql and php date function formats

try

SELECT distinct(
DATE_FORMAT( fieldname, '%Y-%m-%d' )
)
FROM table
ORDER BY field DESC


--

Bastien

Cat, the other other white meat

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

> >
> > You need to do this on the mysql side, not in php - php can't
> > summarize the data before processing it, so you need to use
> > something like the date() function in mysql on your timestamp column.
> >
> > http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_date
> >
> > without knowing your original query it's hard to give an example, but:
> >
> > select distinct date(timestamp_column) from table;
>
> Thanks Chris,
>
> I am getting closer, but so far it is not iterating only once per
> unique 'date part of the datetime expression', it is returning all the
> rows in the table, including those with the very same date but
> different time in the value of the 'solarLandingDateTime' column.
> There is not alot of discussion in the mysql docs that I saw about how
> to work with DISTINCT. I need to grab data out of the 3 columns:
> solarLandingIP, solarLandingDir, solarLandingDateTime (this part of
> my SELECT is working).
>
> This is what I have:
>
> $foundTrackingRows=mysql_query("SELECT DISTINCT
> DATE(solarLandingDateTime) solarLandingIP, solarLandingDir,
> solarLandingDateTime FROM ".$whichTable." ORDER BY
> solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query failed:
> " .mysql_error());
>
> -Govinda


There is no comma between DATE(solarLandingDateTime) and solarLandingIP
which means the DATE column will use the alias 'solarLandingIP'. Is this
your intention? Or is the solarLandingIP another column from the table.
If the latter, you may want to do something like this:

$foundTrackingRows=mysql_query("SELECT DISTINCT
DATE(solarLandingDateTime) AS solarLandingDate,
solarLandingIP, solarLandingDir, solarLandingDateTime
FROM ".$whichTable.
" ORDER BY solarLandingDateTime DESC
LIMIT $Maxrecs2Show") or die("query failed: " . mysql_error());>

If you are aliasing a column it is better to use the optional AS keyword
to avoid confusion.
MySQL's DATE function returns dates formatted as 'YYYY-MM-DD' so DATE_FORMAT
is not needed here.

--
Niel Archer
niel.archer (at) blueyonder.co.uk



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

> > >
> > > You need to do this on the mysql side, not in php - php can't
> > > summarize the data before processing it, so you need to use
> > > something like the date() function in mysql on your timestamp column.
> > >
> > > http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_date
> > >
> > > without knowing your original query it's hard to give an example, but:
> > >
> > > select distinct date(timestamp_column) from table;
> >
> > Thanks Chris,
> >
> > I am getting closer, but so far it is not iterating only once per
> > unique 'date part of the datetime expression', it is returning all the
> > rows in the table, including those with the very same date but
> > different time in the value of the 'solarLandingDateTime' column.
> > There is not alot of discussion in the mysql docs that I saw about how
> > to work with DISTINCT. I need to grab data out of the 3 columns:
> > solarLandingIP, solarLandingDir, solarLandingDateTime (this part of
> > my SELECT is working).
> >
> > This is what I have:
> >
> > $foundTrackingRows=mysql_query("SELECT DISTINCT
> > DATE(solarLandingDateTime) solarLandingIP, solarLandingDir,
> > solarLandingDateTime FROM ".$whichTable." ORDER BY
> > solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query failed:
> > " .mysql_error());
> >
> > -Govinda
>
>
> There is no comma between DATE(solarLandingDateTime) and solarLandingIP
> which means the DATE column will use the alias 'solarLandingIP'. Is this
> your intention? Or is the solarLandingIP another column from the table.
> If the latter, you may want to do something like this:
>
> $foundTrackingRows=mysql_query("SELECT DISTINCT
> DATE(solarLandingDateTime) AS solarLandingDate,
> solarLandingIP, solarLandingDir, solarLandingDateTime
> FROM ".$whichTable.
> " ORDER BY solarLandingDateTime DESC
> LIMIT $Maxrecs2Show") or die("query failed: " . mysql_error());>

Oops, forgot to mention that with the alias you can change the ORDER BY
clause to use the aliased column data:
ORDER BY solarLandingDate DESC
this will only use the returned data instead of the entire column.

> If you are aliasing a column it is better to use the optional AS keyword
> to avoid confusion.
> MySQL's DATE function returns dates formatted as 'YYYY-MM-DD' so DATE_FORMAT
> is not needed here.

> --
> Niel Archer
> niel.archer (at) blueyonder.co.uk
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
Niel Archer
niel.archer (at) blueyonder.co.uk



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

> Oops, forgot to mention that with the alias you can change the ORDER
> BY
> clause to use the aliased column data:
> ORDER BY solarLandingDate DESC
> this will only use the returned data instead of the entire column.
>
>> If you are aliasing a column it is better to use the optional AS
>> keyword
>> to avoid confusion.
>> MySQL's DATE function returns dates formatted as 'YYYY-MM-DD' so
>> DATE_FORMAT
>> is not needed here.

Niel, Bastien,

thanks for your efforts to lead me to understanding this!

I tried everything you both suggested.
Ideally I would have some clear docs that outline the syntax for me,
for such an example as I need.. and I would be able to check my code
myself.
Meanwhile, In every case, I just get every record in the table back as
a result.

So then I thought, "try and make even a *simple* DISTINCT work, and
then move on to the date thing"... so I try this:

//$foundTrackingRows=mysql_query("SELECT DISTINCT solarLandingDir,
solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".
$whichTable." ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show")
or die("query failed: " .mysql_error());

In all the records in this table, there are only 3 possible values in
the 'solarLandingDir' column (TINYTEXT):
diysolar
solar_hm
(null)

but I still get all the records back, with each distinct
'solarLandingDir' column value represented several times.

So something really basic is missing in my understanding/code.
Can you see what it is?

-Govinda


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date(DAY!)* in a timestamp column?

Govinda wrote:
>> Oops, forgot to mention that with the alias you can change the ORDER BY
>> clause to use the aliased column data:
>> ORDER BY solarLandingDate DESC
>> this will only use the returned data instead of the entire column.
>>
>>> If you are aliasing a column it is better to use the optional AS keyword
>>> to avoid confusion.
>>> MySQL's DATE function returns dates formatted as 'YYYY-MM-DD' so
>>> DATE_FORMAT
>>> is not needed here.
>
> Niel, Bastien,
>
> thanks for your efforts to lead me to understanding this!
>
> I tried everything you both suggested.
> Ideally I would have some clear docs that outline the syntax for me, for
> such an example as I need.. and I would be able to check my code myself.
> Meanwhile, In every case, I just get every record in the table back as a
> result.
>
> So then I thought, "try and make even a *simple* DISTINCT work, and then
> move on to the date thing"... so I try this:
>
> //$foundTrackingRows=mysql_query("SELECT DISTINCT solarLandingDir,
> solarLandingIP, solarLandingDir, solarLandingDateTime FROM
> ".$whichTable." ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show")
> or die("query failed: " .mysql_error());
>
> In all the records in this table, there are only 3 possible values in
> the 'solarLandingDir' column (TINYTEXT):
> diysolar
> solar_hm
> (null)
>
> but I still get all the records back, with each distinct
> 'solarLandingDir' column value represented several times.

Provide an example of what you have now, and what you want to get back.

We're all just guessing what you're trying to do so an example might
help us understand and help you better.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date

--0016e64135ea5529e5047035692e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

The distinct can only work as you want it to work when you only select the
date column and only the date part (not the time part).
Unfortunately I'm more an oracle DBA where the date functions are more clear
to me so can not help with exact syntax.

I think what you should be doing is returning the entire set of records (all
required columns) in sorted by date order and loop through all of them. keep
track of the last date processed and if it is the same as the current
record, process nothing and go get the next record.


BUT.... I am not quite sure what you are trying to achieve so my advise may
be completely flawed.

Jack
2009/8/3 Govinda

> Oops, forgot to mention that with the alias you can change the ORDER BY
>> clause to use the aliased column data:
>> ORDER BY solarLandingDate DESC
>> this will only use the returned data instead of the entire column.
>>
>> If you are aliasing a column it is better to use the optional AS keyword
>>> to avoid confusion.
>>> MySQL's DATE function returns dates formatted as 'YYYY-MM-DD' so
>>> DATE_FORMAT
>>> is not needed here.
>>>
>>
> Niel, Bastien,
>
> thanks for your efforts to lead me to understanding this!
>
> I tried everything you both suggested.
> Ideally I would have some clear docs that outline the syntax for me, for
> such an example as I need.. and I would be able to check my code myself.
> Meanwhile, In every case, I just get every record in the table back as a
> result.
>
> So then I thought, "try and make even a *simple* DISTINCT work, and then
> move on to the date thing"... so I try this:
>
> //$foundTrackingRows=mysql_query("SELECT DISTINCT solarLandingDir,
> solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".$whichTable."
> ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query
> failed: " .mysql_error());
>
> In all the records in this table, there are only 3 possible values in the
> 'solarLandingDir' column (TINYTEXT):
> diysolar
> solar_hm
> (null)
>
> but I still get all the records back, with each distinct 'solarLandingDir'
> column value represented several times.
>
> So something really basic is missing in my understanding/code.
> Can you see what it is?
>
> -Govinda
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--
Jack van Zanen

-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation

--0016e64135ea5529e5047035692e--

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

> The distinct can only work as you want it to work when you only select the
> date column and only the date part (not the time part).
> Unfortunately I'm more an oracle DBA where the date functions are more clear
> to me so can not help with exact syntax.
>
> I think what you should be doing is returning the entire set of records (all
> required columns) in sorted by date order and loop through all of them. keep
> track of the last date processed and if it is the same as the current
> record, process nothing and go get the next record.
>
>
> BUT.... I am not quite sure what you are trying to achieve so my advise may
> be completely flawed.
>
> Jack
> 2009/8/3 Govinda

Doh, should have realised this before, but it's after 4 AM here.

Jack is absolutely correct here, you are getting all the rows back
because that is what the query asks for. Try this as your first step

SELECT DISTINCT DATE(solarLandingDateTime) AS solarLandingDate
FROM " . $whichTable . " ORDER BY solarLandingDateTime DESC
LIMIT $Maxrecs2Show") or die("query failed: " . mysql_error());

This should get your unique rows by date. The previous queries used ALL
of the column names to form a unique row, and that made all the rows
DISTINCT.

Give us an idea of your table structure, if possible post the CREATE
statement for it.


> > Oops, forgot to mention that with the alias you can change the ORDER BY
> >> clause to use the aliased column data:
> >> ORDER BY solarLandingDate DESC
> >> this will only use the returned data instead of the entire column.
> >>
> >> If you are aliasing a column it is better to use the optional AS keyword
> >>> to avoid confusion.
> >>> MySQL's DATE function returns dates formatted as 'YYYY-MM-DD' so
> >>> DATE_FORMAT
> >>> is not needed here.
> >>>
> >>
> > Niel, Bastien,
> >
> > thanks for your efforts to lead me to understanding this!
> >
> > I tried everything you both suggested.
> > Ideally I would have some clear docs that outline the syntax for me, for
> > such an example as I need.. and I would be able to check my code myself.
> > Meanwhile, In every case, I just get every record in the table back as a
> > result.
> >
> > So then I thought, "try and make even a *simple* DISTINCT work, and then
> > move on to the date thing"... so I try this:
> >
> > //$foundTrackingRows=mysql_query("SELECT DISTINCT solarLandingDir,
> > solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".$whichTable."
> > ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query
> > failed: " .mysql_error());
> >
> > In all the records in this table, there are only 3 possible values in the
> > 'solarLandingDir' column (TINYTEXT):
> > diysolar
> > solar_hm
> > (null)
> >
> > but I still get all the records back, with each distinct 'solarLandingDir'
> > column value represented several times.
> >
> > So something really basic is missing in my understanding/code.
> > Can you see what it is?
> >
> > -Govinda
> >
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
>
> --
> Jack van Zanen
>
> -------------------------
> This e-mail and any attachments may contain confidential material for the
> sole use of the intended recipient. If you are not the intended recipient,
> please be aware that any disclosure, copying, distribution or use of this
> e-mail or any attachment is prohibited. If you have received this e-mail in
> error, please contact the sender and delete all copies.
> Thank you for your cooperation

--
Niel Archer
niel.archer (at) blueyonder.co.uk



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

On Sun, 2 Aug 2009 16:30:35 -0600, Govinda wrote:

> I'm translating some code from another server-side language into PHP,
> and I need something that 'summarizes' results found from a MySQL
> SELECT. I.e. -
>
> $foundTrackingRows=mysql_query("SELECT...
> while ($TrackingRow = mysql_fetch_object($foundTrackingRows)) {...
>
> ..such that the while loop only loops *ONCE per unique _date_ found
> (regardless of the hour/min./sec.)* in my column which is of type
> TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
> PRIMARY KEY
>
> For example, if I have column values like these:
> 2009-08-01 07:01:00
> 2009-07-30 18:16:37
> 2009-07-30 17:49:06
> 2009-07-27 17:35:52
> 2009-07-27 17:24:21
> 2009-07-27 17:23:03
> ..then my while { loop would only fire 3 times.

You could use the date as an index:

... SELECT DATE(`datetimecolumn`) AS `date` ...

while ($TrackingRow = mysql_fetch_object (...)) {
$data[$TrackingRow['date']] = $TrackingRow;
/* Store the last row from each set of dates */
}

or

while ($TrackingRow = mysql_fetch_object (...)) {
if (!isset ($data[$TrackingRow['date']])) {
$data[$TrackingRow['date']] = $TrackingRow;
}
/* Store the first row from each set of dates */
}


/Nisse

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

On Mon, 3 Aug 2009 11:52:11 +0200, Nisse Engström wrote:

> You could use the date as an index:
>
> ... SELECT DATE(`datetimecolumn`) AS `date` ...
>
> while ($TrackingRow = mysql_fetch_object (...)) {
> $data[$TrackingRow['date']] = $TrackingRow;
> /* Store the last row from each set of dates */
> }
>
> or
>
> while ($TrackingRow = mysql_fetch_object (...)) {
> if (!isset ($data[$TrackingRow['date']])) {
> $data[$TrackingRow['date']] = $TrackingRow;
> }
> /* Store the first row from each set of dates */
> }

And, of course, if you want all rows to be indexed by date:

while ($TrackingRow = mysql_fetch_object (...)) {
$data[$TrackingRow['date']][] = $TrackingRow;
}


/Nisse

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

On Aug 3, 2009, at 12:29 AM, Govinda
wrote:

>> Oops, forgot to mention that with the alias you can change the
>> ORDER BY
>> clause to use the aliased column data:
>> ORDER BY solarLandingDate DESC
>> this will only use the returned data instead of the entire column.
>>
>>> If you are aliasing a column it is better to use the optional AS
>>> keyword
>>> to avoid confusion.
>>> MySQL's DATE function returns dates formatted as 'YYYY-MM-DD' so
>>> DATE_FORMAT
>>> is not needed here.
>
> Niel, Bastien,
>
> thanks for your efforts to lead me to understanding this!
>
> I tried everything you both suggested.
> Ideally I would have some clear docs that outline the syntax for me,
> for such an example as I need.. and I would be able to check my
> code myself.
> Meanwhile, In every case, I just get every record in the table back
> as a result.
>
> So then I thought, "try and make even a *simple* DISTINCT work, and
> then move on to the date thing"... so I try this:
>
> //$foundTrackingRows=mysql_query("SELECT DISTINCT solarLandingDir,
> solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".
> $whichTable." ORDER BY solarLandingDateTime DESC LIMIT
> $Maxrecs2Show") or die("query failed: " .mysql_error());
>
> In all the records in this table, there are only 3 possible values
> in the 'solarLandingDir' column (TINYTEXT):
> diysolar
> solar_hm
> (null)
>
> but I still get all the records back, with each distinct
> 'solarLandingDir' column value represented several times.
>
> So something really basic is missing in my understanding/code.
> Can you see what it is?
>
> -Govinda
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

The issue is with the select distinct, if you wrap the date in the
parantheses for the with the distinct, the example I sent last night
works fine.

Select distinct ( date_format( solarLandingDate , '%Y-%m-%d')),
solarLandingIP,...

If you don't place the distinct parentheses around the date, the
engines tries for a distinct on the entire row, which is why you end
up with all rows

Bastien

Sent from my iPod


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

Bastien,
I had tried it with the parantheses around the date for the distinct.
I tried again just now. Same result. But that's ok. I am onto the
next step now.

Niel, Jack,
I got your fix working. It shows me that I am still so new; I own yet
so little mastery of MySQL.

Nisse, I see what you are suggesting. It seems I can go that route
too. I have much to learn in every direction, so for right now anyway
I am thinking to pursue the stream of thought started with what Niel
and Jack just gave me.

I do need data from the other columns too, and not just the date
extracted from that timestamp field, ...and I need to count # of
records in other tables that have the same unique date as the list of
unique dates I just found in my first table, etc.

... so my thought is to want to do nested query(ies), where:
*within* the while loop of the first recordset (which is now
successfully returning just rows with unique dates), I do other
query(ies) which will (in their own code block) find all rows of the
date we are iterating.. so I can, for example, count number of
records for each unique date, do math/statistics, etc.

I need to play with everything before asking for more detailed help; I
am just now asking if you think I am on the right track with my
thinking - as I just mentioned in the sentence above this one?

Thanks everyone!
-Govinda

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

Govinda wrote:
> .. so my thought is to want to do nested query(ies), where:
> *within* the while loop of the first recordset (which is now
> successfully returning just rows with unique dates), I do other
> query(ies) which will (in their own code block) find all rows of the
> date we are iterating.. so I can, for example, count number of records
> for each unique date, do math/statistics, etc.

I had to do something similar in code of my own a little while ago, and got
some very good guidance on Stack Overflow. Here's the thread, you might find it
helpful:

http://stackoverflow.com/questions/946214/one-sql-query-or-m any-in-a-loop

The user whose answer is marked as the correct one ("Quassnoi") also writes a
helpful blog on SQL. You should be able to find the blog by clicking on the
username.

Ben



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

Just keep in mind that while that may be a very possible solution when
datasets are small. This could get problematic when for instance there are a
10 years worth of dates and millions of records in the other tables. The
resulting program could end up taking lots of time to display data. In your
case this might not happen if you do not get that much data, but again we do
not know.

Just something to keep in mind before deploying.

Jack

-----Original Message-----
From: Govinda [mailto:govinda.webdnatalk@gmail.com]
Sent: Tuesday, August 04, 2009 12:34 AM
To: php-db@lists.php.net
Subject: Re: [PHP-DB] newbie: how to return one iteration *per unique date
(DAY!)* in a timestamp column?

Bastien,
I had tried it with the parantheses around the date for the distinct.
I tried again just now. Same result. But that's ok. I am onto the
next step now.

Niel, Jack,
I got your fix working. It shows me that I am still so new; I own yet
so little mastery of MySQL.

Nisse, I see what you are suggesting. It seems I can go that route
too. I have much to learn in every direction, so for right now anyway
I am thinking to pursue the stream of thought started with what Niel
and Jack just gave me.

I do need data from the other columns too, and not just the date
extracted from that timestamp field, ...and I need to count # of
records in other tables that have the same unique date as the list of
unique dates I just found in my first table, etc.

... so my thought is to want to do nested query(ies), where:
*within* the while loop of the first recordset (which is now
successfully returning just rows with unique dates), I do other
query(ies) which will (in their own code block) find all rows of the
date we are iterating.. so I can, for example, count number of
records for each unique date, do math/statistics, etc.

I need to play with everything before asking for more detailed help; I
am just now asking if you think I am on the right track with my
thinking - as I just mentioned in the sentence above this one?

Thanks everyone!
-Govinda

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.392 / Virus Database: 270.13.40/2276 - Release Date: 08/01/09
18:04:00


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

>> .. so my thought is to want to do nested query(ies), where:
>> *within* the while loop of the first recordset (which is now
>> successfully returning just rows with unique dates), I do other
>> query(ies) which will (in their own code block) find all rows of the
>> date we are iterating.. so I can, for example, count number of
>> records
>> for each unique date, do math/statistics, etc.
>
> I had to do something similar in code of my own a little while ago,
> and got
> some very good guidance on Stack Overflow. Here's the thread, you
> might find it
> helpful:
>
> http://stackoverflow.com/questions/946214/one-sql-query-or-m any-in-a-loop
>
> The user whose answer is marked as the correct one ("Quassnoi") also
> writes a
> helpful blog on SQL. You should be able to find the blog by clicking
> on the
> username.
>
> Ben

Thanks Ben. And yes Jack,

...I was attracted to the nested query as that required less new SQL
ground to learn right now while I am expected to produce! But as that
user (and others in that thread you gave, Ben) said, "better to learn
to do things the right way".

So I need to read/learn more MySQL. Can you guys point me to where in
the mysql docs I should be burying myself?

Here's what I am trying to do:

I have a table created by this:
$SQL="CREATE TABLE t7solar_landing (solarLandingDateTime TIMESTAMP
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY
KEY,solarLandingDir TINYTEXT,solarLandingIP TINYTEXT)";

and other tables too, like this:
$SQL="CREATE TABLE aw_7solar_confirm (solarAwConfDateTime TIMESTAMP
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY
KEY,solarAwConfIP TINYTEXT)";

and this:
$SQL="CREATE TABLE aw_7solar_aw (solarAWDateTime TIMESTAMP DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAWIP
TINYTEXT,solarAWfm_email TINYTEXT,solarAWfm_meta_adtracking
TINYTEXT, ... (plus more columns)";


I need to query these 3 tables (in one query! ;-) ...to return to
me: one iteration of a while loop...

....which will echo:
"#records in 't7solar_landing' matching the given
(iterating) date (in the 'solarLandingDateTime' column) td>#records in 'aw_7solar_confirm' matching the given (iterating)
date (in the 'solarAwConfDateTime' column)#records in
'aw_7solar_aw' matching the given (iterating) date (in the
'solarAWDateTime' column)"...

....*per unique DATE* found in the 'solarLandingDateTime' column of the
1st (t7solar_landing) table.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date(DAY!)* in a timestamp column?

Govinda wrote:
>>> .. so my thought is to want to do nested query(ies), where:
>>> *within* the while loop of the first recordset (which is now
>>> successfully returning just rows with unique dates), I do other
>>> query(ies) which will (in their own code block) find all rows of the
>>> date we are iterating.. so I can, for example, count number of records
>>> for each unique date, do math/statistics, etc.
>>
>> I had to do something similar in code of my own a little while ago,
>> and got
>> some very good guidance on Stack Overflow. Here's the thread, you
>> might find it
>> helpful:
>>
>> http://stackoverflow.com/questions/946214/one-sql-query-or-m any-in-a-loop
>>
>> The user whose answer is marked as the correct one ("Quassnoi") also
>> writes a
>> helpful blog on SQL. You should be able to find the blog by clicking
>> on the
>> username.
>>
>> Ben
>
> Thanks Ben. And yes Jack,
>
> ..I was attracted to the nested query as that required less new SQL
> ground to learn right now while I am expected to produce! But as that
> user (and others in that thread you gave, Ben) said, "better to learn to
> do things the right way".
>
> So I need to read/learn more MySQL. Can you guys point me to where in
> the mysql docs I should be burying myself?
>
> Here's what I am trying to do:
>
> I have a table created by this:
> $SQL="CREATE TABLE t7solar_landing (solarLandingDateTime TIMESTAMP
> DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY
> KEY,solarLandingDir TINYTEXT,solarLandingIP TINYTEXT)";
>
> and other tables too, like this:
> $SQL="CREATE TABLE aw_7solar_confirm (solarAwConfDateTime TIMESTAMP
> DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY
> KEY,solarAwConfIP TINYTEXT)";
>
> and this:
> $SQL="CREATE TABLE aw_7solar_aw (solarAWDateTime TIMESTAMP DEFAULT
> CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAWIP
> TINYTEXT,solarAWfm_email TINYTEXT,solarAWfm_meta_adtracking TINYTEXT,
> ... (plus more columns)";
>
>
> I need to query these 3 tables (in one query! ;-) ...to return to me:
> one iteration of a while loop...
>
> ...which will echo:
> "#records in 't7solar_landing' matching the given (iterating)
> date (in the 'solarLandingDateTime' column)#records in
> 'aw_7solar_confirm' matching the given (iterating) date (in the
> 'solarAwConfDateTime' column)#records in 'aw_7solar_aw'
> matching the given (iterating) date (in the 'solarAWDateTime'
> column)"...
>
> ...*per unique DATE* found in the 'solarLandingDateTime' column of the
> 1st (t7solar_landing) table.

What's the obsession with just doing one loop?

To start off, do it in two steps, then worry about making it one
statement (though it doesn't necessarily need to be done in one go).

You need to get it right first before anything else.

Get unique dates:

$query = "select DISTINCT DATE(solarLandingDateTime) AS landing_date
from t7solar_landing";

$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {

$date = $row['landing_date'];

$query = "
select
count(solarLandingDateTime) as landing_count,
count(solarAwConfDateTime) as confirm_count,
count(solarAWDateTime) as aw_count
from
t7solar_landing
left join aw_7solar_confirm
left join aw_7solar_aw
where
date(solarLandingDateTime) = '".mysql_real_escape_string($date)."'
or
date(solarAwConfDateTime) = '".mysql_real_escape_string($date)."'
or
date(solarAWDateTime) = '".mysql_real_escape_string($date)."'
";

// print results

}

After you're sure that you are getting the right results, work on doing
it in one query.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: newbie: how to return one iteration *per unique date(DAY!)* in a timestamp column?

> ...which will echo:
> "#records in 't7solar_landing' matching the given (iterating)
> date (in the 'solarLandingDateTime' column)#records in
> 'aw_7solar_confirm' matching the given (iterating) date (in the
> 'solarAwConfDateTime' column)#records in 'aw_7solar_aw'
> matching the given (iterating) date (in the 'solarAWDateTime'
> column)"...

If you just need to count the records with a particular date you should be able
to use this construction:

SELECT COUNT(*) AS `record_count`,
DATE() AS `date_field`
FROM

\n";

while ($row = mysql_fetch_assoc($tableDumpResult)) {

	print_r($row);

}

echo "