John Butler wrote:
Can anyone see what must be a simple error in the way I am trying to
construct the 2 WHERE clauses, below? (in the latter query)
These two columns in the two separate tables are of type TIMESTAMP:
solarLandingDateTime
solarAWDateTime
The values I am passing into the query are like so
var_dump($userPikStartDate); // string(10) "2009-08-04"
var_dump($userPikEndDate); // string(10) "2009-08-09"
This query works as expected and returns all my rows: (including ones
where the 'solarLandingDateTime' or 'solarAWDateTime' column
contains a TIMESTAMP like '2009-08-09 03:14:07'
--------
$query = "SELECT COUNT(*) AS `CountRows_UNIQdateTblDir`,
date(solarLandingDateTime) AS `uniqueDate`, solarLandingDir AS
`trackingDir`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing
GROUP BY date(solarLandingDateTime), solarLandingDir UNION ALL SELECT
count(*) AS `CountRows_UNIQdateTblDir`, date(solarAWDateTime) AS
`uniqueDate`, solarAWfm_meta_adtracking AS `trackingDir`,
'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY
date(solarAWDateTime), solarAWfm_meta_adtracking ORDER BY uniqueDate
DESC LIMIT 300";
But thisquery returns zero rows:
--------
$query = "SELECT COUNT(*) AS `CountRows_UNIQdateTblDir`,
date(solarLandingDateTime) AS `uniqueDate`, solarLandingDir AS
`trackingDir`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing
WHERE date(solarLandingDateTime) >= $userPikStartDate AND
date(solarLandingDateTime) <= $userPikEndDate GROUP BY
date(solarLandingDateTime), solarLandingDir UNION ALL SELECT count(*) AS
`CountRows_UNIQdateTblDir`, date(solarAWDateTime) AS `uniqueDate`,
solarAWfm_meta_adtracking AS `trackingDir`, 'aweber_7solar_aw' AS
`tableAlias` FROM aweber_7solar_aw WHERE date(solarAWDateTime) >=
$userPikStartDate AND date(solarAWDateTime) <= $userPikEndDate GROUP BY
date(solarAWDateTime), solarAWfm_meta_adtracking ORDER BY uniqueDate
DESC LIMIT 300";
echo mysql_error();
You will need to quote your timestamps:
.... where date(solarLandingDateTime) >= '" .
mysql_real_escape_string($userPikStartDate) . "' AND
date(solarLandingDateTime) <= '" .
mysql_real_escape_string($userPikEndDate) . "' ....
If all else fails,
echo $query;
then copy it to mysql (either command line or something like phpmyadmin)
and run it. What do you get?
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
