-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
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
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
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:
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
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
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
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
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
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
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,
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
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,
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
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
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
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
: [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
.. 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
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
...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
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'
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
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
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
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.
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.
What does the resultset look like?
I am getting every record in the table back
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
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
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
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
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 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
34 matches
Mail list logo
