Jump to content
JDevOnline

Selecting Similar Records from the Same Table

Recommended Posts

Posted (edited)

Hi, I have students fee record saved in a table called feerecord. It has structure like the following:

 

mysql-table-structure.jpg.a5050d2431521c44ff2824e4389e21e0.jpg

I want to use a mysqli query to extract results as shown in the following table:

desired-result.jpg.461f845dff8d7c2074a13c3c15e752c8.jpg

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 by JDevOnline

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Posted (edited)

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 by JDevOnline

Share this post


Link to post
Share on other sites
Posted (edited)

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 by Barand
correction to query

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.