"COUNT() returns 0 if there were no matching rows." .... really?!

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

 



Hi all

I am 99.9% sure it is my lack of understanding something, but it sure seems like my PHP/MySQL code is returning something other than what i expect from reading the docs.

to explain:
this code is serving me (almost perfectly) well:

-------------------------------------------------------------------------------------------------------
$query = "SELECT COUNT(*) AS `CountRowsThisDateThisTBL`, date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing GROUP BY date(solarLandingDateTime) UNION ALL SELECT count(*) AS `CountRowsThisDateThisTBL`, date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm' AS `tableAlias` FROM aweber_7solar_confirm GROUP BY date(solarAweberConfDateTime) UNION ALL SELECT count(*) AS `CountRowsThisDateThisTBL`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY date(solarAWDateTime) ORDER BY uniqueDate DESC LIMIT 300";

$foundUniqueDateROWS = mysql_query($query) or die("query failed: " .mysql_error());
while ($uniqueDateROW = mysql_fetch_object($foundUniqueDateROWS)) {

	$CountRowsThisDateThisTBL=0;

	$uniqueDate=htmlentities($uniqueDateROW->uniqueDate);
	$tableAlias=htmlentities($uniqueDateROW->tableAlias);
$CountRowsThisDateThisTBL=htmlentities($uniqueDateROW- >CountRowsThisDateThisTBL);

	$TBLsubarray["$uniqueDateROW->tableAlias"]=$CountRowsThisDateThisTBL;
	$BuildPerUniqueDateArray[$uniqueDateROW->uniqueDate]=$TBLsubarray;
}
-------------------------------------------------------------------------------------------------------

...but I am having the problem, that on iterations of the while loop where there are NO records in one of those tables on a particular date (when the *other* tables *do* have records for that date), then the variable $CountRowsThisDateThisTBL seems to persist its previous value (from what it was on the last iteration for the same table (previous date, same table), as opposed to what I would expect (that it should be set to "0" since count() should be returning "0" for this iteration.

What am I missing?

Here's an example (snip) from a var_dump of that $BuildPerUniqueDateArray: (note that the 'aweber_7solar_aw' table does NOT have a record for the date '2009-07-28', so I would expect to see that "1" to be a "0" there.)
["2009-07-29"]=>
  array(3) {
    ["aweber_7solar_aw"]=>
    string(1) "1"
    ["t7solar_landing"]=>
    string(1) "1"
    ["aweber_7solar_confirm"]=>
    string(1) "1"
  }
  ["2009-07-28"]=>
  array(3) {
    ["aweber_7solar_aw"]=>
    string(1) "1"
    ["t7solar_landing"]=>
    string(1) "5"
    ["aweber_7solar_confirm"]=>
    string(1) "2"

thanks,
-Govinda

--
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