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
