SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Date

  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date

    Hi,

    Im trying to grab all records that match the month from:

    $eventid = '2007-05-11';

    using query:

    Code:
    $sql = mysql_query("SELECT id, firstname, lastname, phone, cellphone, email, starttime, finishtime, event FROM calendar WHERE MONTH(starttime) LIKE '".$eventid."%' ORDER BY starttime ASC");
    Seems to bring back zero rows when there are 2 rows iin he database matching the 5th month.

    Have I got it all wrong?

    Thanks

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2005
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Should be
    MONTH(starttime) = MONTH($eventid)
    Also you might want to check for year too

    To understand what MONTH(), YEAR() does, simple run SELECT MONTH('2007-05-11'); on your MySQL frontend tool

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Australia
    Posts
    137
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you mean $eventid = '2007-05-' to grab any day within the fifth month of 2007?

  4. #4
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works using:

    Code:
    WHERE MONTH(starttime) = '$monthnum' && YEAR(starttime) = '$year'
    Thanks
    Last edited by _matrix_; May 11, 2007 at 19:56.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    if you care about performance (i.e. to avoid table scans) then your query should look like this --
    Code:
    select ...
      from ...
     where starttime >= '2007-05-01'
       and starttime  < '2007-06-01'
    this way, an index on the starttime column can be utilized
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •