SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Show month wise report in mysql

    Hello,

    I have made an application for call management and want to show month wise report of an employee. So far I was thinking about GROUP BY but it fetches only last report.

    Piece of my code is:
    PHP Code:
    $select mysql_query("SELECT  
                                            *  
                                    FROM  
                                            tbl_call_mngmt 
                                    WHERE  
                                            call_mngmt_emp_code = '"
    .$emp."'  
                                    AND  
                                            call_mngmt_on_field IN ( 'On Field', 'Holiday' )  
                                    AND  
                                            call_mngmt_dr_code = '"
    .$dr_code."' 
                                    GROUP BY 
                                            MONTH(call_mngmt_date)"
    ); 
                                             
                                            while(
    $row mysql_fetch_array($select)){ 
                                            echo 
    "<td>"; echo(date('d'strtotime($row['call_mngmt_date'])));  
                                            echo
    "</td>"
                                             
                                            } 
    If I run the query in phpmyadmin, I am still getting last result.And it shows as follows:

    Dr. Name|Oct|Nov
    ABC|16| 22

    But I want result like
    ABC|16,17|22,15

    Table structure is very simple. Doctor name and call dates are inserted in database along with some other info.

    Plus, another problem is, if there is no record in database, <td> is not printing. I tried if(!mysql_num_rows($sel)){ "<td>&nbsp;</td>";} in while loop. but no luck!

    Can anyone please guide me in this regard?
    It's easy once you know how...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please stop using the dreaded, evil "select star" -- it is the source of your problem here, and is never a good idea
    Code:
    SELECT MONTH(call_mngmt_date) AS month
         , SUM(some_numeric_data) AS sum_numeric
         , GROUP_CONCAT(some_string_data) AS strings
      FROM tbl_call_mngmt  
     WHERE call_mngmt_emp_code = '".$emp."'   
       AND call_mngmt_on_field IN ( 'On Field', 'Holiday' )   
       AND call_mngmt_dr_code = '".$dr_code."'  
    GROUP 
        BY MONTH(call_mngmt_date)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply r937. Still GROUP BY isnt solving my problem.It fetches first record only.Is there any alternative to fetch all records grouped by month?
    It's easy once you know how...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by anita_86 View Post
    Still GROUP BY isnt solving my problem.It fetches first record only.
    no, it does not

    if you would kindly show your query, i will point out what's wrong
    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
  •