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

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

 



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

If a table doesn't have a record for a given date, I wouldn't expect to see "1"
or "0" -- I would expect not to see any row at all for that date/table
combination. You're not looping through all possible dates, you're looping
through the result-set of your query.

Are you sure that the table in question doesn't have any 2009-07-28 records?

You could add the following column to each SELECT to help troubleshoot:

  GROUP_CONCAT(<date_column>) AS `all_timestamps_for_date`

This will give your result-set an additional column, which will contain a comma-separated list of all the records that GROUP BY is gathering together in
each row (and therefore all the records that COUNT() is counting).

I'm wondering if some sort of timezone discrepancy is maybe causing a timestamp
record to be attributed to 2009-07-28 unexpectedly.

Ben


Ben

I tried to implement your troubleshooting column like so:

$query = "SELECT GROUP_CONCAT(date(solarLandingDateTime)) AS `all_timestamps_for_date` COUNT(*) AS `CountRowsThisDateThisTBL`, date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing GROUP BY date(solarLandingDateTime) UNION ALL SELECT GROUP_CONCAT(date(solarAweberConfDateTime)) AS `all_timestamps_for_date` COUNT(*) AS `CountRowsThisDateThisTBL`, date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm' AS `tableAlias` FROM aweber_7solar_confirm GROUP BY date(solarAweberConfDateTime) UNION ALL SELECT GROUP_CONCAT(date(solarAWDateTime)) AS `all_timestamps_for_date` 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";

it returns this error:
query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(*) AS `CountRowsThisDateThisTBL`, date(solarLandingDateTime) AS `uniqueDat' at line 1


..So not to sit here helpless, I troubleshoot according to my current level of skill this way:
I added this lower down on my page:

$tableDump = "SELECT solarAWDateTime FROM aweber_7solar_aw ORDER BY solarAWDateTime DESC"; $tableDumpResult = mysql_query($tableDump) or die("query failed: " .mysql_error());
echo "<hr /><pre>\n";
while ($row = mysql_fetch_assoc($tableDumpResult)) {
	print_r($row);
}
echo "</pre>\n";
echo "<hr />\n";

it returns this:
Array
(
    [solarAWDateTime] => 2009-08-06 13:33:57
)
Array
(
    [solarAWDateTime] => 2009-08-06 09:41:54
)
Array
(
    [solarAWDateTime] => 2009-08-06 06:06:55
)
Array
(
    [solarAWDateTime] => 2009-08-05 16:19:27
)
Array
(
    [solarAWDateTime] => 2009-08-05 16:19:25
)
Array
(
    [solarAWDateTime] => 2009-08-05 16:19:02
)
Array
(
    [solarAWDateTime] => 2009-08-05 06:55:58
)
Array
(
    [solarAWDateTime] => 2009-08-04 06:46:42
)
...
Array
(
    [solarAWDateTime] => 2009-07-30 06:48:56
)
Array
(
    [solarAWDateTime] => 2009-07-29 16:11:20
)


I did not clip my paste here ^^^ the last entry is indeed '2009-07-29 16:11:20'.

..so you can see there is no record in this table with a timestamp on the date '2009-07-28'.. so HOW in the world does my array get that element, for that date, as if the while loop was iterating on a record in this table with that date, when none exists?? (You still have my OP on this?.. to see the code and var_dump for that array I build from the iterating results of the original query?)

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