Re: adding a simple WHERE clause to this MySQL query causes the result to contain 0 rows?!

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/