Selecting Similar Records from the Same Table

[JDevOnline]

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.

Edited April 5, 2019 by JDevOnline

[Barand]

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

[JDevOnline]

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.

Edited April 7, 2019 by JDevOnline

[Barand]

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

Edited April 7, 2019 by Barand
correction to query

[Barand]

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

 

[JDevOnline]

Hi Barand, thank you very much! ?