SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist stonedeft's Avatar
    Join Date
    Aug 2009
    Posts
    589
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    php mysql select month and year

    Hello forums I need to select the month and year of a mysql database (datetime)

    2010-05-06 15:33:45
    2010-05-06 15:33:45
    2010-05-06 15:33:45
    2010-05-06 15:33:45

    This one didn't work :



    PHP Code:
    $m 'May';
    $y '2010';

    $sql mysql_query("SELECT MONTH(s_in) AS month, YEAR(s_in) AS year FROM sd_record WHERE MONTH = '$m' AND YEAR = '$y'");
    echo  
    mysql_num_rows($sql); 
    Don't Panic

  2. #2
    SitePoint Evangelist smftre's Avatar
    Join Date
    Dec 2008
    Location
    London
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How does it look in phpMyAdmin?
    Statvoo.com The Website Traffic Monitor
    The best way to monitor traffic to your sites for free!


    Web Development London UK We make web 3.0 applications

  3. #3
    SitePoint Evangelist stonedeft's Avatar
    Join Date
    Aug 2009
    Posts
    589
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind I got it

    http://dev.mysql.com/doc/refman/5.1/...functions.html

    $sql = mysql_query("SELECT MONTHNAME(s_in) AS month,
    YEAR(s_in) AS year
    FROM sd_record
    WHERE MONTHNAME(s_in) = '$m' AND
    YEAR(s_in) = '$y' ");


    QUESTION -> How do I select only the records for the current month and year? Is there a mysql function to return the current month and year? or I need php to get the current month and year for me before passing the query?

    thnx
    Don't Panic

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it would probably be easier if you let php determine the starting and ending dates for the date range you want

    your target code will look like this, to retrieve all rows for April 2010 --
    Code:
    WHERE s_in >= '2010-04-01'
      AND s_in  < '2010-05-01'
    note that if you use MONTH, MONTHNAME, or YEAR in your WHERE clause, your query will be inefficient

    only the range test as shown on the full date is efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist stonedeft's Avatar
    Join Date
    Aug 2009
    Posts
    589
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it would probably be easier if you let php determine the starting and ending dates for the date range you want

    your target code will look like this, to retrieve all rows for April 2010 --
    Code:
    WHERE s_in >= '2010-04-01'
      AND s_in  < '2010-05-01'
    note that if you use MONTH, MONTHNAME, or YEAR in your WHERE clause, your query will be inefficient

    only the range test as shown on the full date is efficient
    You are definitely right and the query is much shorter Thanks I'll try that
    Don't Panic


Tags for this Thread

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
  •