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

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

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux