>> 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 o
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
DATE(so
> 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:
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
`CountUniqueDatesI
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 y
> -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
> >
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 and
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,
'aweber_7solar_confi
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 `CountUniqueD
On Aug 4, 2009, at 6:21 PM, Chris 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,
'my_table_2' as table_name
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
> 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 reco
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 | 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 DATE(solarLandingDat
> > 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 | da
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
-
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
-
Chris wrote:
Ben Dunlap wrote:
> ...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'
ma
Ben Dunlap wrote:
> ...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 giv
> ...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 (iterati
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
.. 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 examp
: [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 wo
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 iterat
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
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
> 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 r
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
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 keywor
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 c
> > >
> > > 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
> >
> > 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_da
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
--
Ba
What does the resultset look like?
I am getting every record in the table back
On Sun, Aug 2, 2009 at 11:08 PM, Govinda wrote:
>> 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
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-t
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/
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
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 t
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
40 matches
Mail list logo