Query for One To Many Relation In The Same Table

JDevOnline · May 27, 2020

Hi Barand,

I am seeking you help to make an appropriate query to fetch data from monthly fee table of mysql db, whereby data is stored in such a way that each row stores the data of a student for one month, there are several rows per student.

I want to fetch the data such that the result set should have the one row per student with multiple columns for months, each column showing the status of fee, paid or not.

I have created the following query but the problem is that it is showing only one month which is '2020-03-01'.

SELECT DISTINCT name, month, paid from mfee WHERE month >= '2020-03-01' GROUP BY name order by name

Edited May 27, 2020 by JDevOnline

Barand · May 27, 2020

Easiest way I know is first to store your query data in an array that has the same structure as your required output

<?php
$start = '2020-03-01';

//
// Query to get column headings
//
$res = $conn->prepare("SELECT DISTINCT
                              month(month)     as mno
                            , monthname(month) as mname
                       FROM fee
                       WHERE month >= ?
                       ORDER BY mno    
                       ");
$res->execute([$start]);
$heads = [];
foreach ($res as $row) {
    $heads[$row['mno']]  = $row['mname'];
}
$empty_array = array_fill_keys(array_keys($heads), '');

//
//  now process the fee data and store in an array
//
$data = [];
$res = $conn->prepare("SELECT name
                            , month(month) as mno
                            , paid
                       FROM fee
                       WHERE month >= ? 
                       ORDER BY name, month
                      ");
$res->execute([$start]);
foreach ($res as $row) {
    if (!isset($data[$row['name']])) {
        $data[$row['name']] = $empty_array;
    }
    $data[$row['name']][$row['mno']] = $row['paid'];
}

//
//  now output the array
//
$theads = "<tr><th>Name</th><th>" . join('</th><th>', $heads) . "</th></tr>\n";
$tdata = '';
foreach ($data as $name => $paid_arr) {
    $tdata .= "<tr><td>$name</td><td>" . join('</td><td>', $paid_arr) . "</td></tr>\n";
}
?>
<html>
<head>
<title>Sample</title>
<style type="text/css">
     body, table {
         font-family: verdana, sans-serif;
         font-size: 11pt;
     }
     table {
         width: 400px;
     }
     th {
         background-color: black;
         color: white;
     }
     td {
         text-align: center;
     }
</style>
</head>
<body>
    <table>
    <?=$theads?>
    <?=$tdata?>
    </table>
</body>
</html>

image.png.ad1513dfb69be5354e32581e93aaaf1c.png

JDevOnline · May 27, 2020

Barand thank you very much for the prompt reply. After make 2 changes to your code I saved the code into a php file and called it into the browser, but it is showing few errors, kindly let me know how to remove these errors.

Changes:

Table name changed to "mfee" at both the occurrences.

In both the queries "WHERE month >= ?" is changed to "WHERE month >= '$start'"

Output:

JDevOnline · May 27, 2020

I want to complete the task today, I would therefore be extremely grateful if you could help me remove the error asap, thanks.

Barand · May 27, 2020

https://www.php.net/manual/en/mysqli-stmt.bind-param.php

https://www.php.net/manual/en/mysqli-stmt.execute.php

or use a PDO connection as I did.

Edited May 27, 2020 by Barand

JDevOnline · May 27, 2020

Hello Barand,

Thank you very much! That perfectly resolved my issue, I am once again very grateful to you.

JDevOnline · May 27, 2020

Hi Barand,

Using CSS I want to highlight the cells where paid is equal to "N". I can't figure out where to insert this code due to arrays. Please help:

<td 
    <?php
    		if($row['paid']=='N'){echo 'class="highlight"';}
    ?>
</td>

Barand · May 27, 2020

You need to rewrite this line ...

 $tdata .= "<tr><td>$name</td><td>" . join('</td><td>', $paid_arr) . "</td></tr>\n";

so you output the name then loop through the $paid_array.

JDevOnline · May 28, 2020

Hello Barand,

I've tried to change the code you mentioned as below but it is still now working. Please help me modify the code to achieve the desired results:

if($paid_arr == 'Y'){$cls='class="hilite"';}else{$cls='class="centex"';}
echo "<tr><td class='centex'>$index</td><td>$name</td><td $cls>" . join("</td><td $cls>", $paid_arr) . "</td></tr>\n";

Barand · May 28, 2020

foreach ($data as $name => $paid_arr) {
    $tdata .= "<tr><td>$name</td>";
    foreach ($paid_arr as $p) {
        $cls = $p=='N' ? 'hilite' : '';
        $tdata .= "<td class='$cls'>$p</td>";
    }
    $tdata .= "</tr>\n";
}

image.png.0ecab09218fc2aa0da00cb432dcadafb.png

JDevOnline · May 29, 2020

Excellent! Thanks a lot! 😇

JDevOnline · June 2, 2020

Hi Barand,

I now want to extract the details of students who have paid all 2 months, i.e. march, april and may. I have written the following query but it is not bringing any records, however there are many students who have paid all 3 months.

SELECT nroll.id as id, nroll.sname, nroll.ctclass, parents.memail, parents.femail FROM nroll 
INNER JOIN parents on nroll.id=parents.id 
INNER JOIN mfee on nroll.id=mfee.sid 
WHERE nroll.ctstudent = 'Y' and nroll.ctclass='1 Reception-1' and nroll.shift='1' and 
(mfee.month='2020-01-01' and mfee.month='2020-02-01') 
ORDER BY nroll.sname

Barand · June 2, 2020

3 hours ago, JDevOnline said:

who have paid all 2 months, i.e. march, april and may.

Ah yes , the three types of programmer, those who can count and those who can't

Use a table subquery to find those who paid all two and match against it with a join

SELECT name
	, month(month) as mno
	, paid
FROM fee
     JOIN (
            SELECT name
                 , count(*)
            FROM fee
            WHERE month(month) IN (3,4,5) AND paid = 'Y'
            GROUP BY name
            HAVING count(*) = 3
          ) paid3 USING (name)
     
WHERE month >= '2020-03-01' 
ORDER BY name, month

JDevOnline · June 2, 2020

7 hours ago, Barand said:

Ah yes , the three types of programmer, those who can count and those who can't

😇

JDevOnline · June 2, 2020

Hi Barand, thanks for the help. How can we show just one row per student's name and only two columns, name and "Y", those who paid all 3 months.

Barand · June 2, 2020

4 hours ago, JDevOnline said:

How can we show just one row per student's name and only two columns, name and "Y"

In a very to similar manner to the code already given.

SELECT name
     , 'Y' as paid
FROM fee
WHERE month(month) IN (3,4,5) AND paid = 'Y'
GROUP BY name
HAVING count(*) = 3;

Edited June 2, 2020 by Barand

JDevOnline · June 3, 2020

Hello Barand, thank you very much, that resolved the issue. ✔️ 👌

JDevOnline · July 12, 2020

On 5/28/2020 at 1:48 PM, Barand said:


foreach ($data as $name => $paid_arr) {
    $tdata .= "<tr><td>$name</td>";
    foreach ($paid_arr as $p) {
        $cls = $p=='N' ? 'hilite' : '';
        $tdata .= "<td class='$cls'>$p</td>";
    }
    $tdata .= "</tr>\n";
}

Hi Barand,

I want to insert one more value to the above table after "Name", like email or phone number. Please tell me how can that value be put inside your loops?

Barand · July 13, 2020

Change your query to retrieve the extra item and restructure the $data array to store it.

$data = [];
$res = $conn->prepare("SELECT name
                            , email                             -- extra item
                            , month(month) as mno
                            , paid
                       FROM fee
                       WHERE month >= ? 
                       ORDER BY name, month
                      ");
$res->execute([$start]);
foreach ($res as $row) {
    if (!isset($data[$row['name']])) {
        $data[$row['name']] = [ 'email' => $row['email'], 
                                'pays' => $empty_array ];
    }
    $data[$row['name']]['pays'][$row['mno']] = $row['paid'];
}

JDevOnline · July 13, 2020

Dear Barand, thank you sooo very much! 👌

JDevOnline · July 26, 2020

On 6/2/2020 at 12:54 PM, Barand said:

Ah yes , the three types of programmer, those who can count and those who can't

Use a table subquery to find those who paid all two and match against it with a join


SELECT name
	, month(month) as mno
	, paid
FROM fee
     JOIN (
            SELECT name
                 , count(*)
            FROM fee
            WHERE month(month) IN (3,4,5) AND paid = 'Y'
            GROUP BY name
            HAVING count(*) = 3
          ) paid3 USING (name)
     
WHERE month >= '2020-03-01' 
ORDER BY name, month

Hi Barand, hope you are well,

Can you please suggest the edit to this sql query to fetch only the rows in which any month (from January to May) is not paid but at the same time keep showing all the columns of months. Doing it by using the quoted query, it removes the columns in which the fee is paid, thanks.

Barand · July 26, 2020

That query is looking at March to May only, so that needs changing. Also it looks for those who paid; you now want those who didin't pay. In addition, you are looking for any occurence, not when there are 3.

Sign In

Query for One To Many Relation In The Same Table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information