SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query MySql database by month into array

    Hello,
    I have a table with a few hundred rows. each row contains a date field, for the date a student registered for a course.
    I need to build an array which gives me a sum of signups for each month for the last 12 months.
    Example, January, 12 signups
    feb, 14 signups

    The array should look like this after the query is complete.
    In this format:
    PHP Code:
        $signupsbymonth=array(
            
    "Jan" => 110,
            
    "Feb" => 130,
            
    "Mar" => 215,
            
    "Apr" => 81,
            
    "May" => 175,
            
    "Jun" => 110,
            
    "Jul" => 190,
            
    "Aug" => 175,
            
    "Sep" => 390,
            
    "Oct" => 286,
            
    "Nov" => 150,
            
    "Dec" => 196
        
    ); 
    can anyone assist? THANKS A MILLION!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT MONTH(signupdate) AS mm
         , DATE_FORMAT(signupdate,'%b') AS mth
         , COUNT(*) AS signups
      FROM daTable
    GROUP
        BY mm
    ORDER
        BY mm
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for this. did a query using the code provided and it did work,
    However it joined all years together. would like array to say
    PHP Code:
    $values=array(
    "January 2012" => 25
    "December 2011" => 30
    "November 2011" => 22 
    And to go back 12 months. This query only gave me 2 months worth.

    Also, how do I constuct the array? Would like to print it out in the format above.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by xeonman13 View Post
    However it joined all years together.
    doh!! i forgot the WHERE condition!


    Code:
    WHERE signupdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
                                 - INTERVAL 1 YEAR
    and if you want to append the year to the month, change the DATE_FORMAT format string from '%b' to '%b %Y'

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok perfect that worked. THANKS.now can anyone help with getting it into the array format above?


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
  •