JDevOnline Posted April 5, 2019 Share Posted April 5, 2019 Hi, I have students fee record saved in a table called feerecord. It has structure like the following: I want to use a mysqli query to extract results as shown in the following table: I am using a query like the following but it is not working as desired: Select * from feerecord group by name, month order by shift asc, class asc, name asc Can anyone please help me devise a right query? thanks. Link to comment Share on other sites More sharing options...
Barand Posted April 5, 2019 Share Posted April 5, 2019 I'd use a query like this SELECT name , month , paid FROM feerecord ORDER BY name, month and as I read each record, build an array like this $data = [ 'name1' = [ 'Jan' => 'Y', 'Feb' => 'Y', 'Mar' => 'N' ], 'name2' = [ 'Jan' => 'N', 'Feb' => 'Y', 'Mar' => 'N' ], ]; You can then loop through the array to get your desired output. Note, you have made life difficult for yourself by using a non-sortable date format Link to comment Share on other sites More sharing options...
JDevOnline Posted April 7, 2019 Author Share Posted April 7, 2019 Hi Barand, thanks for your reply, I have inserted a column name mont with "date" type and have also inserted date values (like 2019-01-01 for January-2019 in month column) corresponding to the month column. Can you please now update your query and array building code accordingly, thanks. Link to comment Share on other sites More sharing options...
Barand Posted April 7, 2019 Share Posted April 7, 2019 The query would become SELECT name , date_format(mont, '%b') as mname , paid FROM feerecord WHERE QUARTER(mont) = 1 AND YEAR(mont) = 2019 ORDER BY name, mont The array structure remains unaltered Link to comment Share on other sites More sharing options...
Barand Posted April 7, 2019 Share Posted April 7, 2019 Just to give a bit of processing help. To build the array foreach row in the results $data[name][mname] = paid endforeach to output to html table output table headings row foreach data as name = namedata start new output row output name cell foreach namedata as paid output paid cell endforeach end output row endforeach Link to comment Share on other sites More sharing options...
JDevOnline Posted April 10, 2019 Author Share Posted April 10, 2019 Hi Barand, thank you very much! ? Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.