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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 more than the *simplest* of SQL statements which I had gleaned from an intro PHP book. Your time will not go to waste! Thank you!

I got it performing as expected. But there is one remaining thing that is mysterious to me. It will be easiest to explain/ask by showing my code and the result:

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 `CountUniqueDatesInMyTbl`, date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm' AS `tableAlias` FROM aweber_7solar_confirm GROUP BY DATE(solarAweberConfDateTime) UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) ";

while { ...
echo htmlentities($uniqueDateROW['uniqueDate'])." -------- ".htmlentities($uniqueDateROW['CountUniqueDatesInMyTbl'])." -------- ".htmlentities($uniqueDateROW['tableAlias'])."<br />\n";
-----------------------------------------------------

returns this:

2009-07-28 -------- 5 -------- t7solar_landing
2009-07-29 -------- 1 -------- t7solar_landing
2009-08-02 -------- 2 -------- t7solar_landing
2009-08-03 -------- 3 -------- t7solar_landing
2009-08-04 -------- 2 -------- t7solar_landing
2009-07-28 -------- 2 -------- aweber_7solar_confirm
2009-07-29 -------- 1 -------- aweber_7solar_confirm
2009-07-30 -------- 1 -------- aweber_7solar_confirm
2009-07-31 -------- 1 -------- aweber_7solar_confirm
2009-08-01 -------- 2 -------- aweber_7solar_confirm
2009-08-02 -------- 1 -------- aweber_7solar_confirm
2009-08-03 -------- 2 -------- aweber_7solar_confirm
2009-08-04 -------- 1 -------- aweber_7solar_confirm
2009-07-29 -------- 1 -------- aweber_7solar_aw
2009-07-30 -------- 1 -------- aweber_7solar_aw
2009-07-31 -------- 1 -------- aweber_7solar_aw
2009-08-01 -------- 2 -------- aweber_7solar_aw
2009-08-02 -------- 1 -------- aweber_7solar_aw
2009-08-03 -------- 2 -------- aweber_7solar_aw
2009-08-04 -------- 1 -------- aweber_7solar_aw

Now the first (date) and last (table) column are as expected. But what happened to count(*)?? There ARE 5 unique dates represented in the first table, as the highest returned value for count(*) shows, but as you can see, there are 8 unique dates in the second table, and 7 unique dates in the last table. Why then do we see only "1"s and "2"s in those columns for the latter 2 tables? And why does NOT the first table's rows include exclusively ONLY the values "1", "2", "3", "4", "5"? I see two "2"s and no "4". (??) I'm sure I can hack PHP in that while loop to generate the right count of unique dates for each table.. but I want to take advantage of this thread up to now and learn more SQL. The docs say count() "Return[s] a count of the number of rows returned". In relation to that definition, I don't know what I am looking at in the results I got above.
Can you 'splain me?

------------
John Butler (Govinda)
govinda.webdnatalk@xxxxxxxxx




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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux