SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  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>";
                                            
                                            } 
    Plus, another problem is, if there is no record in database, <td> is not printing. I tried several things like mysql_num_rows !=0 or !empty($row['call_mngmt_date']) but no luck!

    Can anyone please guide me in this regard? I have attached a screen shot for reference.
    screenshot.jpg
    It's easy once you know how...

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    413
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If there is no record in the database, "<td>" won't print because the echo is within the while () loop, which won't execute if the mysql_fetch_array() doesn't return anything. Not sure if that's what you meant, as the screenshot isn't visible yet.

    If you run the query on its own, say in phpmyadmin, does it give you the results you want? Without knowing the database structure or quite what you want out of it, it's hard to say what might be better. Note also that the MONTH function seems to return 1 to 12 (or 0 to 11, I forget) so when you have more than one years results in the database it might not give you the results you expected, as I would think all January results for all years will be grouped.
    http://www.firenza.net - my homage to a car from the 1970s

  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)
    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.
    It's easy once you know how...

  4. #4
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    413
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What do 16, 17 and 22, 15 represent? Are these dates within October and November when the doctor had a call, so he was called on 16-Oct, 17-Oct, 22-Nov and 15-Nov? As your query uses GROUP BY, all you will see is a single return for each month that has any calls, and I believe the specific record it shows is just the first it finds. If you want separate entries for each call, you'd probably want to use ORDER BY instead, which will sort by the month of the call-out. I'm not sure if it's possible to get it to aggregate multiple records into a single entry via SQL, but this isn't really the correct forum for sql discussion.

    Same applies, though - in the thirteenth month you'll be merging October from two years unless there is other code elsewhere that stops it.
    http://www.firenza.net - my homage to a car from the 1970s

  5. #5
    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 droopsnoot.Yes 16, 17 and 22, 15 are the dates when doctor had a call. I simply want an alternative to GROUP BY I guess.Will ORDER BY fetch month wise records?
    It's easy once you know how...

  6. #6
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For now I have started same thread in Databases & MySQL forum.Thanks again for your reply
    It's easy once you know how...


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
  •