Professional Web Applications Themes

selecting by date - MySQL

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

  1. #1

    Default 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 Guest

  2. #2

    Default Re: selecting by date

    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 Guest

  3. #3

    Default Re: selecting by date

    Bill Karwin <billkarwin.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 Guest

  4. #4

    Default Re: selecting by date

    Peter wrote:
    > Bill Karwin <billkarwin.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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  5. #5

    Default Re: selecting by date

    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 "&".
    Christian Kirsch Guest

Similar Threads

  1. JSObject returns wrong date. How can Iextract correct date from digital signature?
    By Hal_Underwood@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: February 19th, 01:22 AM
  2. Replies: 1
    Last Post: October 24th, 11:38 AM
  3. Automatically selecting date range
    By chipjohns in forum Coldfusion Database Access
    Replies: 8
    Last Post: October 19th, 06:06 PM
  4. selecting based on a month in a date
    By Creative Solutions New Media in forum PHP Development
    Replies: 0
    Last Post: August 28th, 10:19 PM
  5. Selecting Date
    By Ramesh in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 5th, 03:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139