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

2009-08-05 Thread Ford, Mike
-Original Message- From: Govinda [mailto:govinda.webdnat...@gmail.com] Sent: 05 August 2009 01:41 Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY

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

2009-08-05 Thread Govinda
Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: ... That's how I'd write it, anyway. I like to know

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

2009-08-05 Thread Govinda
Me/this again. this works good: $query = SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`, date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing GROUP BY uniqueDate ORDER BY uniqueDate DESC LIMIT 62 UNION ALL SELECT count(*) AS

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

2009-08-05 Thread Ben Dunlap
except that I just added the ORDER BY clause onto each SELECT segment, and now I get this error: query failed: Incorrect usage of UNION and ORDER BY How can I order the results while still doing the UNION ALLs? You should only need one ORDER BY clause at the end of the whole query:

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

2009-08-05 Thread Chris
Ford, Mike wrote: -Original Message- From: Govinda [mailto:govinda.webdnat...@gmail.com] Sent: 05 August 2009 01:41 Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY

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

2009-08-05 Thread Ben Dunlap
Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: [8] That's a mysqlism :( It's not portable to other db's

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

2009-08-04 Thread Govinda
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

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

2009-08-04 Thread Niel Archer
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 |

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

2009-08-04 Thread Govinda
Niel, thanks (I was wondering about that... thought it seemed odd without a FROM clause) Now I have this: $query = SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM t7solar_landing GROUP BY

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

2009-08-04 Thread Chris
Govinda wrote: 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 |

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

2009-08-04 Thread Ben Dunlap
Can someone point me to understand why? I thought that: SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM t7solar_landing GROUP BY DATE(solarLandingDateTime) would, among other thing, assign the number of records

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

2009-08-04 Thread Govinda
Yes, but when you UNION that query with others it gets a bit more complicated ... If you prefer the latter syntax, you can use mysql_fetch_object(). Ben Bless you Ben!! That last post of yours led to my first decent dose of real significant SQL-syntax understanding since I started writing

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

2009-08-04 Thread Phpster
On Aug 4, 2009, at 6:21 PM, Chris dmag...@gmail.com wrote: Govinda wrote: 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,

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

2009-08-04 Thread Chris
this: - $query = SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`, date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing GROUP BY DATE(solarLandingDateTime) UNION ALL SELECT count(*) AS

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

2009-08-04 Thread Chris
With a union the columns have to have the same name (or alias). Eg: SELECT COUNT(*) AS count, date(solarLandingDateTime) AS unique_date, 't7solar_landingALIAS' AS origin FROM tablename UNION ALL SELECT count(*) AS count, date(solarAweberConfDateTime) AS unique_date,

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

2009-08-04 Thread Govinda
Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) Your query says: get the date() from the solarAWDateTime field then group by that date and give me the date

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

2009-08-03 Thread Phpster
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

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

2009-08-03 Thread Govinda
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

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

2009-08-03 Thread Ben Dunlap
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

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

2009-08-03 Thread Jack van Zanen
: [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

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

2009-08-03 Thread Govinda
.. 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

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

2009-08-03 Thread Chris
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

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

2009-08-03 Thread Ben Dunlap
...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

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

2009-08-03 Thread Chris
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'

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

2009-08-03 Thread Chris
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

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

2009-08-03 Thread Govinda
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

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

2009-08-02 Thread Chris
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

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

2009-08-02 Thread Govinda
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-functions.html#function_date without knowing

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

2009-08-02 Thread Bastien Koert
On Sun, Aug 2, 2009 at 8:02 PM, Govindagovinda.webdnat...@gmail.com 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.

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

2009-08-02 Thread Govinda
On Sun, Aug 2, 2009 at 8:02 PM, Govindagovinda.webdnat...@gmail.com 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.

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

2009-08-02 Thread Bastien Koert
On Sun, Aug 2, 2009 at 11:08 PM, Govindagovinda.webdnat...@gmail.com wrote: On Sun, Aug 2, 2009 at 8:02 PM, Govindagovinda.webdnat...@gmail.com 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

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

2009-08-02 Thread Govinda
What does the resultset look like? I am getting every record in the table back

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

2009-08-02 Thread Bastien Koert
On Sun, Aug 2, 2009 at 11:13 PM, Govindagovinda.webdnat...@gmail.com 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

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

2009-08-02 Thread Niel Archer
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-functions.html#function_date

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

2009-08-02 Thread Niel Archer
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-functions.html#function_date

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

2009-08-02 Thread 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

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

2009-08-02 Thread Chris
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

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

2009-08-02 Thread Jack van Zanen
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

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

2009-08-02 Thread Niel Archer
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