[PHP-DB] 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: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Aug 3, 2009, at 12:29 AM, Govinda govinda.webdnat...@gmail.com 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 '-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: [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
Re: [PHP-DB] 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-many-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: [PHP-DB] 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.webdnat...@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: [PHP-DB] 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-many-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: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/ tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... ...*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: [PHP-DB] 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-many-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:
trtd#records in 't7solar_landing' matching the given (iterating)
date (in the 'solarLandingDateTime' column)/tdtd#records in
'aw_7solar_confirm' matching the given (iterating) date (in the
'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw'
matching the given (iterating) date (in the 'solarAWDateTime'
column)/td/tr...
...*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: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... 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(date_column) AS `date_field` FROM table GROUP BY `date_field` You could probably write a generalized PHP function (called 'build_query()' or something) that would construct this query given a table name and a date-column name, and call it once for each table/column pair. Then you could stitch the three query strings together, in PHP, into one large query using SQL's UNION ALL, which concatenates the results of multiple queries into one large result-set: (query 1) UNION ALL (query 2) UNION ALL (query 3) And then pass that one large query to the database. So I need to read/learn more MySQL. Can you guys point me to where in the mysql docs I should be burying myself? In my experience the MySQL manual isn't a great resource for learning SQL, at the level you're looking for. It's a fine reference if you already have a solid understanding of the basics. But to get that understanding, you might try the O'Reilly book called Learning SQL: http://oreilly.com/catalog/9780596520830/?CMP=AFC-ak_bookATT=Learning+SQL%2c+Second+Edition%2c Someone else here might know of some good online resources. I've not seen any, but then I haven't spent a whole lot of time looking. The parts of Learning SQL that I've seen are excellent. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Ben Dunlap wrote: ...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... 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(date_column) AS `date_field` FROM table GROUP BY `date_field` You could probably write a generalized PHP function (called 'build_query()' or something) that would construct this query given a table name and a date-column name, and call it once for each table/column pair. Then you could stitch the three query strings together, in PHP, into one large query using SQL's UNION ALL, which concatenates the results of multiple queries into one large result-set: (query 1) UNION ALL (query 2) UNION ALL (query 3) And then pass that one large query to the database. .. and a field describing which table it came from, otherwise you end up with: count | date 5 | 2009-01-01 10| 2009-01-01 and no reference point. -- 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: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Chris wrote: Ben Dunlap wrote: ...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... 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(date_column) AS `date_field` FROM table GROUP BY `date_field` You could probably write a generalized PHP function (called 'build_query()' or something) that would construct this query given a table name and a date-column name, and call it once for each table/column pair. Then you could stitch the three query strings together, in PHP, into one large query using SQL's UNION ALL, which concatenates the results of multiple queries into one large result-set: (query 1) UNION ALL (query 2) UNION ALL (query 3) And then pass that one large query to the database. .. and a field describing which table it came from, otherwise you end up with: count | date 5 | 2009-01-01 10| 2009-01-01 and no reference point. I should have given an example .. select count(*) as record_count, date(column_name) as date_field, 'my_table' as table_name union all select count(*) as record_count, date(column_name) as date_field, 'my_table_2' as table_name and end up with: count | date | table_name --- 5 | 2009-01-01 | table 1 10| 2009-01-01 | table 2 -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
