Show month wise report in mysql

Databases
1

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:

$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> </td>”;} in while loop. but no luck!

Can anyone please guide me in this regard?

2

please stop using the dreaded, evil “select star” – it is the source of your problem here, and is never a good idea

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)
3

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?

4

no, it does not

if you would kindly show your query, i will point out what’s wrong