selecting by date

Suppose I have a month and a year (this is for a blog application):

$month = 10; // October
$year = 2005; // year 2005

I'd like to select items on or before the last day of October 2005 in
descending order. My first attempt was:

echo "SELECT * FROM $tblPosts " .
"WHERE MONTH(postDate)<=$month & YEAR(postDate)<=$year " .
"ORDER BY postDate DESC, id DESC LIMIT $to_request");

but for some reason, this is selecting ALL records.


Given a month and year, how do I select any item with a postDate (which is a
datetime field) on the last day of the month or earlier in the same year?

Thanks!
Pete

Peter wrote:
> echo "SELECT * FROM $tblPosts " .
> "WHERE MONTH(postDate)<=$month & YEAR(postDate)<=$year " .

The & symbol is not a boolean AND in SQL.

In MySQL, this is a bitwise AND operator, which may have different
behavior than you expect when applied to boolean terms.

Try the AND operator instead.

Regards,
Bill K.

Bill Karwin <bill@karwin.com> wrote:
> Peter wrote:
>> echo "SELECT * FROM $tblPosts " .
>> "WHERE MONTH(postDate)<=$month & YEAR(postDate)<=$year " .
>
> The & symbol is not a boolean AND in SQL.
>
> In MySQL, this is a bitwise AND operator, which may have different
> behavior than you expect when applied to boolean terms.
>
> Try the AND operator instead.
>
> Regards,
> Bill K.

Oh, good gravy!!! How embarrasing!

Thanks for clearing that up. *sheepish grin*

Pete

Peter wrote:
> Bill Karwin <bill@karwin.com> wrote:
>
>>Peter wrote:
>>
>>>echo "SELECT * FROM $tblPosts " .
>>> "WHERE MONTH(postDate)<=$month & YEAR(postDate)<=$year " .
>>
>>The & symbol is not a boolean AND in SQL.
>>
>>In MySQL, this is a bitwise AND operator, which may have different
>>behavior than you expect when applied to boolean terms.
>>
>>Try the AND operator instead.
>>
>>Regards,
>>Bill K.
>
>
> Oh, good gravy!!! How embarrasing!
>
> Thanks for clearing that up. *sheepish grin*
>
> Pete


Almost:

"SELECT * FROM $tblPosts " .
"WHERE YEAR(postDate) < $year OR " .
"(YEAR(postDate) = $year AND MONTH($postDate) <= $month"

Your existing query won't get November and December of any year.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Peter schrieb:
> Suppose I have a month and a year (this is for a blog application):
>
> $month = 10; // October
> $year = 2005; // year 2005
>
> I'd like to select items on or before the last day of October 2005 in
> descending order. My first attempt was:
>
> echo "SELECT * FROM $tblPosts " .
> "WHERE MONTH(postDate)<=$month & YEAR(postDate)<=$year " .
> "ORDER BY postDate DESC, id DESC LIMIT $to_request");
>
> but for some reason, this is selecting ALL records.
>
>
> Given a month and year, how do I select any item with a postDate (which is a
> datetime field) on the last day of the month or earlier in the same year?

Try your query in mysql (the commandline tool) - PHP is irrelevant for
your question.
And brush up your SQL - the "AND" operator is definitely not "&".