Show month wise report in mysql

PHP
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.:confused:

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>";
										
										}

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.

2

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.

3

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.

4

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.

5

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?

6

For now I have started same thread in Databases & MySQL forum.Thanks again for your reply :slight_smile: