how to retrieve huge data from mysql database with multiple table using php

Question

I need monthly attendance report of a particular department. In my procedure it will takes more time to generate. If more than 50 employees then it will not generating report at all.

I have two table one is Employee table and another one Attendance table.

I am generating report like this.

Here is my code.

<div class="row">
    <div class="col-md-12">   
        <table style="width:100%;" border="1">                                
            <tr style="font-weight: bold; background-color: #D3D3D3; text-align: center;">
                <td style="text-align: center;padding: 5px;">
SLNo.
                </td>   
                <td>EMPID</td>
                <td style="width:250px; text-align: center; padding-left: 5px; padding-right: 5px;">
                    Employee&nbsp;Name
                </td>

                <?php
                /* Default Date Indexing starts here. */
                $timezone = "Asia/Calcutta";
                date_default_timezone_set($timezone);
                $timestamp = mktime(0,0,0,$Month,1,$Year);
                $maxday = date("t",$timestamp);

                //echo "<td></td>";
                for ($k=1; $k<= $maxday; $k++) {  
                    $kv = sprintf('%02d', $k);
                    echo "<td style=''>$kv</td>";  
                }

                /* Default Date Indexing ends here. */
                ?>
                <td>TOT.P</td>
                <td>TOT.HLF</td>
                <td>TOT.A</td>
                <td>TOT.WO</td>
                <td>TOT.HLD</td>
            </tr>
            <?php

            //Getting all employee details according roster assign table.
            $sqlRA = "SELECT * from employee_details where DivisionDbKey = '$DivisionDbKey' and Status='ACTIVE' ORDER BY EmployeeName ASC";
            $resultRA = mysqli_query($conn, $sqlRA);
            $i = 1;
            while ($rowRA = mysqli_fetch_array($resultRA, MYSQLI_ASSOC)) {
                $EmployeeDbKey = $rowRA['DbKey'];
                $EmployeeId = $rowRA['EmployeeId'];
                $EmployeeName = $rowRA['EmployeeName'];                                                                        
                echo "<tr>";
                echo "<td style='text-align:center;'>$i</td>";
                echo "<td style='width:200px;'>$EmployeeId</td>";
                echo "<td>$EmployeeName</td>";
                $PCount = 0;$ACount = 0;$HLFCount = 0;$WOCount=0;$HLDCount=0;
                for ($kk=1; $kk<= $maxday; $kk++) {  
                    $kkv = sprintf('%02d', $kk);
                    $CurrentOPDate = $kkv."/".$Month."/".$Year; 

                    $sqlRAD = "SELECT * from employee_attendance where EmployeeDbKey = '$EmployeeDbKey' and Date ='$CurrentOPDate' and Status='ACTIVE'";
                    $resultRAD = mysqli_query($conn, $sqlRAD);
                    $rowcountRAD = mysqli_num_rows($resultRAD);
                    if ($rowcountRAD != 0) {
                        while ($rowRAD = mysqli_fetch_array($resultRAD, MYSQLI_ASSOC)) {
                            $Attendance = $rowRAD['Attendance'];                                                                                   

                            $colorcode="";
                            if($Attendance == "P" || $Attendance == "p"){
                               $PCount = $PCount + 1;
                               $colorcode = "background-color:#00C0EF; color:#FFF;"; 
                            }                                                                                    
                            elseif ($Attendance == "WO" || $Attendance == "wo" || $Attendance == "Wo") {
                                 $WOCount = $WOCount + 1;
                                $colorcode = "background-color:#2980B9; color:#FFF;";
                            }
                            elseif ($Attendance =="HLD" || $Attendance =="hld" || $Attendance == 'Hld') {
                                $HLDCount = $HLDCount + 1;
                                $colorcode = "background-color:#FFD455;color:#000;";
                            }
                            elseif ($Attendance == "HLF" || $Attendance == "hlf" || $Attendance == "Hlf") {
                                $HLFCount = $HLFCount + 1;
                                $colorcode = "background-color:#931336; color:#FFF;";
                            } 
                            elseif ($Attendance == "A" || $Attendance == "a") {
                                $ACount = $ACount + 1;
                                $colorcode = "background-color:#F00; color:#FFF;";
                            } 
                            else {

                            }

                            echo "<td style='text-align:center;font-weight:bold;$colorcode' title=''>$Attendance</td>";
                        }
                    }
                    else{
                        $ACount = $ACount + 1;
                        $colorcode = "background-color:#F00; color:#FFF;";
                        echo "<td style='text-align:center;font-weight:bold;$colorcode'>A</td>";
                    }

                }
                ?>                                                              
                <td style="text-align: center;font-weight: bold;color:#F00;"><?php echo $PCount; ?></td>
                <td style="text-align: center;font-weight: bold;color:#F00;"><?php echo $HLFCount; ?></td>
                <td style="text-align: center;font-weight: bold;color:#F00;"><?php echo $ACount; ?></td>
                <td style="text-align: center;font-weight: bold;color:#F00;"><?php echo $WOCount; ?></td>
                <td style="text-align: center;font-weight: bold;color:#F00;"><?php echo $HLDCount; ?></td>                                            
                <?php
                echo "</tr>";                                                                       
                $i = $i + 1;
            }        
            ?>
        </table> 
    </div>
</div>

Please guide me what to do so that I will get fast with more employee data. I have to show 1000 employees record in one time.

Barmar · Accepted Answer · 2020-01-25T09:18:38.827

2

You should join the tables and do a single query for all employees and all days of the month, rather than doing a separate query for each employee and day.

SELECT e.EmployeeId, e.EmployeeName, a.Date, a.Attendance
FROM employee_details AS e
JOIN employee_attendance AS a ON a.EmployeeDbKey = e.DbKey
WHERE e.DivisionDbKey = '$DivisionDbKey' and e.Status='ACTIVE'
AND a.Date BETWEEN '$Year-$Month-01' AND LAST_DAY('$Year-$Month-01') and a.Status='ACTIVE'
ORDER BY e.EmployeeName, a.Date

Then when you're displaying the table, start a new <tr> whenever $row['EmployeeId'] changes.

Also, when you're looping over the dates in the month for an employee, subtract the previous date from the current date. If the difference more than 1, insert $difference - 1 <td>A</td> columns in the row to show the absences.

Or see generate days from date range. You can use one of these techniques to create a subquery that generates all the dates in the month. Left join the employee_attendance table with this, and you'll get rows for every day, but a.Attendance will be NULL if they were absent.

edited Jan 25 '20 at 09:18

answered Jan 25 '20 at 08:03

Barmar

741,623
53
500
612

Thanks @Barmar, It works like charm. But which date attendance is not there that column is showing blank. That I want to fill as Absent (A). – Manoranjan Jena Jan 25 '20 at 09:05
Thanks for helping @Barmar. One more suggestion If from date and End date is then how will achive the result? Eg. From Date - 20/09/2020 to 19/10/2020 – Manoranjan Jena Jan 07 '21 at 13:23
I don't understand the question. "if from date and end date is"? – Barmar Jan 07 '21 at 14:18
You mean if the date range crosses different months? That can't happen with just one `$Year` and `$Month` variable. If you need other date ranges, you'll need to use date arithmetic instead of just comparing the day of month. – Barmar Jan 07 '21 at 14:24
Yes the date range crosses different months. I will pass the from date and to date manually. – Manoranjan Jena Jan 08 '21 at 08:38
PHP has functions for date arithmetic, just use them instead of just comparing day numbers. – Barmar Jan 08 '21 at 14:16

how to retrieve huge data from mysql database with multiple table using php

1 Answers1