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

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

??
Thanks for taking a look. It seems super easy to me; I don't see why
it doesn't work.

-John

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: adding a simple WHERE clause to this MySQL query causesthe 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/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

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

Thanks Chris!

You were right, I just needed to quote those incoming date form input
values.

I was trying to alert myself to any errors with this:
$foundUniqueDateDirROWS = mysql_query($query) or die("query failed:
" .mysql_error());

but it did not catch it. The query apparently worked.. just did not
return any rows.

> then copy it to mysql (either command line or something like
> phpmyadmin) and run it. What do you get?

I know I need to duplicate the remote setup here on my local dev
machine... but I have not done it. and being daunted by server
admin stuff I have never even tried to run mysql from the command
line. I do not know about phpadmin. I doubt it is available on the
remote server I am working on (it's a newbie friend's box, not a
shared host). I would ask for some clues but for all I know phpadmin
is not only OT here, but also easy to educate myself more about before
I take more of your time. I assume there is loads on google, etc...

Anyway now my script is happy/working!
-John

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

John Butler wrote:
>> 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;
>>
>
> Thanks Chris!
>
> You were right, I just needed to quote those incoming date form input
> values.
>
> I was trying to alert myself to any errors with this:
> $foundUniqueDateDirROWS = mysql_query($query) or die("query failed: "
> .mysql_error());
>
> but it did not catch it. The query apparently worked.. just did not
> return any rows.

Interesting.

>> then copy it to mysql (either command line or something like
>> phpmyadmin) and run it. What do you get?
>
> I know I need to duplicate the remote setup here on my local dev
> machine... but I have not done it. and being daunted by server admin
> stuff I have never even tried to run mysql from the command line. I do
> not know about phpadmin. I doubt it is available on the remote server
> I am working on (it's a newbie friend's box, not a shared host). I
> would ask for some clues but for all I know phpadmin is not only OT
> here, but also easy to educate myself more about before I take more of
> your time. I assume there is loads on google, etc...

If you're using windows, it's very easy.

http://www.apachefriends.org/en/index.html

It has a webserver (apache), mysql, phpmyadmin + a bunch of other stuff
included in the one download. One click installer .. voila.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

--Apple-Mail-1-1023132889
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

> If you're using windows, it's very easy.
>
> http://www.apachefriends.org/en/index.html
>
> It has a webserver (apache), mysql, phpmyadmin + a bunch of other
> stuff included in the one download. One click installer .. voila.

I'm on mac here where I develop. I see they say the mac version is in
"first steps of development; use at your own risk".

But anyway maybe I am closer than that to start with. I do have these
running already (just have so little fluency in actually tweaking/
playing with them from the admin perspective) -
MySQL
PHP
Apache

So I gather I just need to know what is this, and where to install/
learn about it, and why-
phpMyAdmin (3.2.0.1)

------------
John Butler (Govinda)
govinda.webdnatalk@gmail.com




--Apple-Mail-1-1023132889--

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

On Aug 10, 2009, at 7:07 AM, John Butler wrote:

>> If you're using windows, it's very easy.
>>
>> http://www.apachefriends.org/en/index.html
>>
>> It has a webserver (apache), mysql, phpmyadmin + a bunch of other
>> stuff included in the one download. One click installer .. voila.
>
> I'm on mac here where I develop. I see they say the mac version is
> in "first steps of development; use at your own risk".

Since you're on a Mac, check out MAMP:

http://www.mamp.info/en/index.html

They have a free version and a Pro version.

Frank

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

read 2nd something not right.. or should show ERRORRR?? but. let's skip
it.. i think you tried it and work.. soo ignore this line.

uniqueDate??
why not order by id.. is the same right?

>>It seems super easy to me;
great.. i need 1 hour to understand this

can you include sample from those table.. hope we can try at home and
try to figure it out?

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";
>
> ??
> Thanks for taking a look. It seems super easy to me; I don't see why
> it doesn't work.
>
> -John
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php