Senthilkumar

Dear Team, I am using Canvas grap on my PHP project. I am using PHP query on the same page and defining the as a variable on a script and passing the script to another js file. My code is $previous_month = date('Y-m', strtotime(date('Y-m') . " -1 month")); $Year = date('Y'); $Mc = "SELECT Region, McTotal, McAssigned FROM ( SELECT count(a.BranchCode) as McTotal, c.regnName as Region FROM sbms.machinemaster as a inner join sbms.branch as b on b.branchcode = a.BranchCode inner join sbms.region as c on c.regnID = b.regnID WHERE a.MachineStatus='A' AND c.regnID !='5' group by c.regnID ) total LEFT JOIN (Select count(a.RowID) as McAssigned , c.regnName as Region FROM sbms.production as a inner join sbms.branch as b on b.branchID = a.Branch inner join sbms.region as c on c.regnID = b.regnID WHERE a.Month = '$previous_month' group by c.regnID order by c.regnID ASC ) assigned USING (Region)"; $Mcresult = mysqli_query($conn, $Mc); while ($Mcrow = mysqli_fetch_array($Mcresult)) { $McRegion[] = $Mcrow['Region']; $McTotal[] = $Mcrow['McTotal']; $McAssigned[] = $Mcrow['McAssigned']; } $MachineTotal = json_encode($McAssigned); $MachineAssigned = json_encode($McTotal); $MachineRegion = json_encode($McRegion); <div class="col-12 col-md-6 col-lg-6 col-xl-6"> <div class="card"> <div class="card-body"> <div class="chart-title"> <div class="float-left"> <h5>Active Machine vs Production Data Collected for <?PHP echo $previous_month1; ?></h5> </div> <div class="float-right"> <a href="Machine_Report.php" type="button" class="btn btn-default subs-btn">View Details</a> </div> </div> <canvas id="MachineBarGraph"></canvas> </div> </div> </div> <script type="text/javascript"> var MachineTotal = <?php echo $MachineTotal ?>; var MachineAssigned = <?php echo $MachineAssigned ?>; var MachineRegion = <?php echo $MachineRegion ?>; </script> <script src="../assets/js/DashboardGraph.js"></script> DashboardGraph.js $(document).ready(function () { // Active Equipment Vs Data Collected var barChartData = { labels: MachineRegion, datasets: [{ label: 'Active Machine', backgroundColor: 'rgba(0, 158, 251, 0.5)', borderColor: 'rgba(0, 158, 251, 1)', borderWidth: 1, data: MachineAssigned }, { label: 'Data Collected', backgroundColor: 'rgba(255, 188, 53, 0.5)', borderColor: 'rgba(255, 188, 53, 1)', borderWidth: 1, data: MachineTotal }] }; var myoption = { responsive: true, tooltips: { enabled: true, }, hover: { animationDuration: 1 }, scales: { yAxes: [{ ticks: { beginAtZero: true, } }], xAxes: [{ ticks: { autoSkip: false } }] }, animation: { duration: 1, onComplete: function () { var chartInstance = this.chart, ctx = chartInstance.ctx; ctx.textAlign = 'center'; ctx.fillStyle = "rgba(0, 0, 0, 1)"; ctx.textBaseline = 'bottom'; this.data.datasets.forEach(function (dataset, i) { var meta = chartInstance.controller.getDatasetMeta(i); meta.data.forEach(function (bar, index) { var data = dataset.data[index]; ctx.fillText(data, bar._model.x, bar._model.y + 1); }); }); } }, maintainAspectRatio: true }; var ctx = document.getElementById('MachineBarGraph').getContext('2d'); window.myBar = new Chart(ctx, { type: 'bar', data: barChartData, options: myoption, }); }); I want to apply a filter to this query. For that, I want to use the Ajax method. so that if I change my filter, the graph will change accordingly. I don't know how to do this. Can any one please help me how to do this

Dear Mr.Barrand, Thanks for your suggestion. The problem is joining ints to varchar(45). Now I corrected and it is working properly.

But when I am running it takes 7 sec. Please suggest

Dear Team, I wrote a MySQL query with multiple inner joins using multiple tables. I am getting my required output with this query. But executing this query takes 6 to 7 seconds. My query is SELECT Category, Budget, Billing, PTarget,PBilling,CTarget FROM (SELECT c.eqipName as Category, sum(a.Budget) as Budget, sum(a.Mar) as PTarget, sum(a.Apr) as CTarget FROM sbms.target as a inner join sbms.material as b on b.id = a.Category inner join sbms.equipment as c on c.eqipID = b.eqipID inner join sbms.plant as d on d.plantCode = a.Branch inner join sbms.branch as e on e.branchID = d.branchID where e.branchID='2' group by c.eqipID) TG LEFT JOIN (select c.eqipName as Category, sum(a.gross_amount) as Billing from sbms.billing as a inner join sbms.division as b on b.divCode = a.division inner join sbms.equipment as c on c.eqipID = b.eqipID inner join sbms.distributionchannel as d on d.dcno = a.dchannel inner join sbms.plant as e on e.plantCode= a.sales_office inner join sbms.branch as f on f.branchID = e.branchID where c.equipcatID = '1' and d.dcgroupid='1' and DATE_FORMAT(a.billing_date,'%Y-%m') between '2024-01' and '2024-03' and f.branchID='2' and a.sales_doc_type !='ZL2W' and a.sales_doc_type !='ZS2' and a.sales_doc_type !='ZIPJ' group by c.eqipName) Billing USING (Category) LEFT JOIN (select c.eqipName as Category, sum(a.gross_amount) as PBilling from sbms.billing as a inner join sbms.division as b on b.divCode = a.division inner join sbms.equipment as c on c.eqipID = b.eqipID inner join sbms.distributionchannel as d on d.dcno = a.dchannel inner join sbms.plant as e on e.plantCode= a.sales_office inner join sbms.branch as f on f.branchID = e.branchID where c.equipcatID = '1' and d.dcgroupid='1' and DATE_FORMAT(a.billing_date,'%Y-%m') = '2024-03' and f.branchID='2' and a.sales_doc_type !='ZL2W' and a.sales_doc_type !='ZS2' and a.sales_doc_type !='ZIPJ' group by c.eqipName) PBilling USING (Category) All my table dump and PHP files are attached to the link below https://drive.google.com/file/d/1VLz-9EJoMEbxqbkykMT13iKaPLPu99u8/view?usp=sharing Can anyone please suggest me to work this as fast.

As per your code the below output is coming But there is the data available for Production Actual & Total for 2024-02 month for Ragul M I want the output should show if any one of the field data is submitted.

Dear Mr.Barand, The above query is working. I have another query that is used for another user. SELECT EmpID, Month, ProductionActual,CustomerActual, MarketingTotal,MarketingActual FROM (select count(*) as ProductionActual, Month, EmpID from sbms.production WHERE EmpID='OWC10148' AND NOT (MCubicmeter = '0' AND MHourmeter ='0') group by Month) ProductionActual LEFT JOIN ( select count(*) as CustomerActual, Month from sbms.customerdata WHERE EmpID='OWC10148' AND (VisitType != 'Not Visited') group by Month) CustomerActual USING (Month) LEFT JOIN (select count(*) as MarketingTotal, Month from sbms.marketing_target WHERE EmpID='OWC10148' group by Month ) marketing_target USING (Month) LEFT JOIN (select count(*) as MarketingActual, Month from sbms.marketing_data WHERE EmpID='OWC10148' AND Date!='' group by Month ) MarketingActual USING (Month) The output of this query is In this, I am having value CustomerActual is 3 for the month of 2024-03. But the output is not displaying here because ProductionActual is not there for the month of 2024-03. If am given any value for ProductionActual on 2024-03, then the third row (2024-03) is displayed on my output. Please suggest me.

Dear mr. Barand, Please help to solve the issue

ok

Dear Mr. Barand, We both are working together. I am in personal emergency leave. So informed her to post the query here.

Dear Team, I have a table which is generating data dynamically. Below is my table My table code is <table id="product_data" class="table table-striped table-bordered nowrap table-sm" style="width:100%;"> <thead class="bg-blue text-center"> <tr> <th style="width: 5%; vertical-align: middle">S.No</th> <th style="width: 25%; vertical-align: middle">Customer Name</th> <th style="width: 12%; vertical-align: middle">Location</th> <th style="width: 8%; vertical-align: middle">Model</th> <th style="width: 8%; vertical-align: middle">Machine S.No</th> <th style="width: 10%; vertical-align: middle">Machine Status</th> <th style="width: 8%; vertical-align: middle">Monthly (Cu.m) <span class="text-danger">*</span></th> <th style="width: 8%; vertical-align: middle">Total (Cu.m)</th> <th style="width: 8%; vertical-align: middle">Monthly (Hr.m)</th> <th style="width: 8%; vertical-align: middle">Total (Hr.m)</th> </tr> </thead> <tbody> <?php $n = 1; $sql = "select * from machinemaster AS a INNER JOIN machineassign AS b ON b.RowID = a.id WHERE b.EmpID = '$EmpNo'"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_assoc($result)) { $ID = $row['id']; $CustomerName = $row['CustomerName']; $MachineLocation = $row['MachineLocation']; $Model = $row['Model']; $MachineNo = $row['MachineSlNo']; $RowID = $row['RowID']; ?> <tr> <td align='center'> <?php echo $n++; ?> </td> <td> <?php echo $CustomerName; ?> </td> <td> <?php echo $MachineLocation; ?> </td> <td> <?php echo $Model; ?> </td> <td> <?php echo $MachineNo; ?> </td> <td> <select name='Ststus[]' id='Status<?php echo $RowID; ?>' class='form-control Status' style="width:auto"> <option value='1'>Working</option> <option value='0'>Not Working</option> </select> </td> <td align='center' hidden> <input type="number" name="RowID[]" id="RowID<?php echo $RowID; ?>" value="<?php echo $RowID; ?>" class="form-control" autocomplete="off" /> </td> <td align='center'> <input type="number" name="MCubicmeter[]" id="MCubicmeter<?php echo $RowID; ?>" value="" class="form-control MCubicmeter text-center cucalc" autocomplete="off" /> </td> <td align='center'> <input type="number" name="TCubicmeter[]" id="TCubicmeter<?php echo $RowID; ?>" value="" class="form-control TCubicmeter text-center " autocomplete="off" readonly /> </td> <td align='center'> <input type="number" name="MHourmeter[]" id="MHourmeter<?php echo $RowID; ?>" value="" class="form-control MHourmeter text-center" autocomplete="off" /> </td> <td align='center'> <input type="number" name="THourmeter[]" id="THourmeter<?php echo $RowID; ?>" value="" class="form-control THourmeter text-center" autocomplete="off" readonly /> </td> <td hidden> <input type="number" name="lang[]" value="<?php echo $RowID; ?>" class="form-control" autocomplete="off" /> </td> </tr> <?php } ?> </tbody> </table> In this table, the machine status is a dropdown (Working & Networking). When I select the not working on any row, the Monthly (Cu.m) & Monthly (Hr.m) columns should disabled. otherwise, it should be editable. I have tried the below code. <script> $(document).on('change', '#product_data tbody tr td:nth-child(6)', function () { var rowvlaue = row($(this).closest('tr')).data()[12]; }); </script> Afte this I am not sure how to go ahead. Please help me how to complete this.

Thanks for your reply. I will check it and update u

Dear Team, I am writing a query to count rows based on the two conditions. But I am not getting proper output. My table is my query is select count(*) as CustomerActual, Month from sbms.customerdata WHERE EmpID='83201858' AND NOT(VisitType = 'No Due' AND VisitDate ='') group by Month My condition is when the date is empty it should not count that row. But if the visit type is 'No due' it should count the row. for example, in 2024-04 month the actual row is 5. As per the above condition, the row count is 4. Because the visit type is No due on ID 1713 and ID 1712 the date is blank. so the count is 4. Instead of No due, if any other is in the visit type column on id 1713, then it should not count. the total count should show on 3. Can anyone help me with how to do this?

Dear Team, I have a data table. I want to display the table, which I have selected first and others next. My actual table is Wheni I select Senthil, then it should display if i select Srilekha Can any one help how to write query for displaying like this?

I created the index for all the columns used in the join query. Now the qury is completing in 30 to 35 seconds. Can you please confirm if this is okay?

I have created the index for columns dchannel, sales_office, division, and material on table billing. Then also same

how to put the index on the columns

Dear MR.Barand, I changed the column type same at both the table. Then also the query is taking long time to finesh it.

Dear Team, I am using the below join query to get output select sum(a.gross_amount) AS Total, b.DistributionChannelDesp AS Department, c.branchName AS Branch, d.grpName AS DepartmentGroup, e.equiSubName AS Equipment, g.mgName AS Material from sbms.billing AS a INNER JOIN sbms.department_code AS b ON b.DChannel = a.dchannel INNER JOIN sbms.branch AS c ON c.branchcode = a.sales_office INNER JOIN sbms.dept_group AS d ON d.grpID = b.grpID INNER JOIN sbms.equipmentsubcategory AS e ON e.eqipSubCode = a.division INNER JOIN sbms.materialsubgroup AS f ON f.mgsubNumber = a.material INNER JOIN sbms.materialgroup AS g ON g.mgID = f.mgID WHERE a.sales_office='801' group by b.DistributionChannelDesp, d.grpID, e.equiSubName, g.mgID When I am running this query, it takes about 300 seconds. The main table billing has more than 300,000 rows, and materialsubgroup has more than 7000 rows. I attached my dumps & sample output to the below link. https://drive.google.com/drive/folders/1IH3FobEkPqnsAwLEPerJJEh-jp7vR4Di?usp=sharing Please help me where i am making the mistake

Dear Mr.Barand, I changed that query and i am getting output. $sql = "select q1.total as printed, q2.total as pending, q3.total as reminder, q4.total as pending1 from ( select count(*) as total FROM calibration.calibrationdata WHERE Branch = 'Bangalore' And status = '1' ) q1, ( select count(*) as total FROM calibration.calibrationdata WHERE Branch = 'Bangalore' And status = '0' ) q2, ( select count(*) as total FROM calibration.calibrationdata WHERE Branch = 'Bangalore' And status = '2' ) q3, ( select count(*) as total FROM calibration.calibrationdata WHERE Branch = 'Bangalore' And status = '0' And today <= (NOW() - INTERVAL 7 DAY) ) q4"; But still, it is taking time to load.

Dear Team, I had modified the code as per suggestion. Please look in to the modified code <?php session_start(); error_reporting(1); include("connection.php"); $id = $_SESSION['id']; $query = "SELECT * FROM userdetails where id='$id'"; $qry_result = mysqli_query($conn, $query); $row = mysqli_fetch_array($qry_result); $userbranch = $row['branch']; $date = date('Y'); $sql = "select q1.total as printed, q2.total as pending, q3.total as reminder, q4.total as pending1 from ( select count(*) as total FROM calibration.calibrationdata WHERE Branch = 'Bangalore' And status = '1' ) q1, ( select count(*) as total FROM calibration.calibrationdata WHERE Branch = 'Bangalore' And status = '0' ) q2, ( select count(*) as total FROM calibration.calibrationdata WHERE Branch = 'Bangalore' And status = '2' ) q3, ( select count(*) as total FROM calibration.calibrationdata WHERE Branch = 'Bangalore' And status = '0' And today <= (NOW() - INTERVAL 7 DAY) ) q4"; $result = mysqli_query($conn, $sql); $row = mysqli_fetch_assoc($result); $Printedreading = $row['printed']; $Printpending = $row['pending']; $Remindermail = $row['reminder']; $Sevendayspending = $row['pending1']; $sql = "SELECT DATE_FORMAT( `Date`,'%b' ) as month, COUNT( * ) as COUNT, SUM( amount ) as total FROM calibrationdata WHERE YEAR(Date)=$date AND Branch = '$userbranch' group by year(Date),month(Date) order by year(Date),month(Date)"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_array($result)) { $Invoicemonth[] = $row['month']; $Invoiceamount[] = $row['total']; $Calibemonth[] = $row['month']; $Calibqty[] = $row['COUNT']; } $Inmonth = json_encode($Invoicemonth); $Invoice = json_encode($Invoiceamount); $calmonth = json_encode($Calibemonth); $qty = json_encode($Calibqty); $sql = "SELECT DATE_FORMAT( `Date`,'%b' ) as month, COUNT( * ) as COUNT, SUM( amount ) as total FROM calibrationdata WHERE Branch = '$userbranch' GROUP BY year( `Date` ) ORDER BY year( `Date` )"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_array($result)) { $yearlyInvoicemonth[] = $row['month']; $yearlyInvoiceamount[] = $row['total']; $yearlyCalibemonth[] = $row['month']; $yearlyCalibqty[] = $row['COUNT']; } $yearlyInmonth = json_encode($yearlyInvoicemonth); $yearlyInvoice = json_encode($yearlyInvoiceamount); $yearlycalmonth = json_encode($yearlyCalibemonth); $yearlyqty = json_encode($yearlyCalibqty); ?> <!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <meta http-equiv="X-UA-Compatible" content="IE=edge" /> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no" /> <meta name="description" content="" /> <meta name="author" content="" /> <link rel="stylesheet" href="../css/style.css" /> <link rel="stylesheet" href="../css/style1.css" /> <link rel="stylesheet" href="../css/bootstrap.min.css" /> <title>Dashboard</title> <link rel="icon" href="../Image/Company_Logo.jpg" type="image/x-icon" /> <link href="../vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css" /> <link href="../css/sb-admin-2.min.css" rel="stylesheet" /> <link href="https://fonts.googleapis.com/css?family=Nunito:200,200i,300,300i,400,400i,600,600i,700,700i,800,800i,900,900i" rel="stylesheet" /> <script src="../js/bootstrap.bundle.min.js"></script> <script src="../js/jquery.min.js"></script> </head> <body> <div> <?php include('header.php'); ?> </div> <div> <div class="row" style="margin-left:10px;margin-top:10px;margin-right:10px"> <!-- Certificate Printed Card Example --> <div class="col mb-4"> <div class="card border-left-success shadow h-100 py-2"> <div class="card-body"> <div class="row no-gutters align-items-center"> <div class="col mr-2"> <div class="text-xxl-center font-weight-bold text-center text-success text-uppercase mb-1"> Certificate Printed </div> <div class="h5 mb-2 font-weight-bold text-center text-gray-800"> <?php echo $Printedreading; ?> </div> <div class="col-auto text-center"> <input type="button" value="View" class="btn-sm btn-success" id="btnHome" onclick="document.location.href='printed.php'" /> </div> </div> </div> </div> </div> </div> <!-- Certificate Print Pending Card Example --> <div class="col mb-4"> <div class="card border-left-danger shadow h-100 py-2"> <div class="card-body"> <div class="row no-gutters align-items-center"> <div class="col mr-2"> <div class="text-xxl-center font-weight-bold text-center text-danger text-uppercase mb-1"> Certificate Print Pending </div> <div class="h5 mb-2 font-weight-bold text-center text-gray-800"> <?php echo $Printpending; ?> </div> <div class="col-auto text-center"> <input type="button" value="View" class="btn-sm btn-danger" id="btnHome" onclick="document.location.href='newreadings.php'" /> </div> </div> </div> </div> </div> </div> <!-- Certificate Pending beyond 7 days Card Example --> <div class="col mb-4"> <div class="card border-left-primary shadow h-100 py-2"> <div class="card-body"> <div class="row no-gutters align-items-center"> <div class="col mr-2"> <div class="text-xxl-center font-weight-bold text-center text-primary text-uppercase mb-1"> Pending beyond 7 days </div> <div class="h5 mb-2 font-weight-bold text-center text-gray-800"> <?php echo $Sevendayspending; ?> </div> <div class="col-auto text-center"> <input type="button" value="View" class="btn-sm btn-primary" id="btnHome" onclick="document.location.href='7daysreminder.php'" /> </div> </div> </div> </div> </div> </div> <!-- Canceled Certificate Card Example --> <div class="col mb-4"> <div class="card border-left-secondary shadow h-100 py-2"> <div class="card-body"> <div class="row no-gutters align-items-center"> <div class="col mr-2"> <div class="text-xxl-center font-weight-bold text-center text-gray-600 text-uppercase mb-1"> Canceled Certificate </div> <div class="h5 mb-2 font-weight-bold text-center text-gray-800"> <?php echo $Remindermail; ?> </div> <div class="col-auto text-center"> <input type="button" value="View" class="btn-sm btn-secondary" id="btnHome" onclick="document.location.href='canceled_certificate.php'" /> </div> </div> </div> </div> </div> </div> </div> <div class="row" style="margin-left:10px;margin-right:10px"> <div class="col-xl-6 col-lg-7"> <!-- Bar Chart --> <div class="card shadow mb-4"> <div class="card-header py-3"> <h6 class="m-0 font-weight-bold text-primary">Monthly Invoice Details</h6> </div> <div class="card-body"> <div class="chart-bar"> <canvas id="myBarChart"></canvas> </div> </div> </div> </div> <div class="col-xl-6 col-lg-7"> <!-- Area Chart --> <div class="card shadow mb-4"> <div class="card-header py-3"> <h6 class="m-0 font-weight-bold text-primary">No of Calibration</h6> </div> <div class="card-body"> <div class="chart-area"> <canvas id="myAreaChart"></canvas> </div> </div> </div> </div> </div> <div class="row" style="margin-left:10px;margin-right:10px"> <div class="col-xl-6 col-lg-7"> <!-- Bar Chart --> <div class="card shadow mb-4"> <div class="card-header py-3"> <h6 class="m-0 font-weight-bold text-primary">Yearly Invoice Details</h6> </div> <div class="card-body"> <div class="chart-bar"> <canvas id="yearlyBarChart"></canvas> </div> </div> </div> </div> <div class="col-xl-6 col-lg-7"> <!-- Area Chart --> <div class="card shadow mb-4"> <div class="card-header py-3"> <h6 class="m-0 font-weight-bold text-primary">Yearly Calibration</h6> </div> <div class="card-body"> <div class="chart-area"> <canvas id="yearlyAreaChart"></canvas> </div> </div> </div> </div> </div> </div> <script type="text/javascript"> var inmonth = <?php echo $Inmonth ?>; var invoice = <?php echo $Invoice ?>; var yearlyinmonth = <?php echo $yearlyInmonth ?>; var yearlyinvoice = <?php echo $yearlyInvoice ?>; var calmonth = <?php echo $calmonth ?>; var qty = <?php echo $qty ?>; var yearlycalmonth = <?php echo $yearlycalmonth ?>; var yearlyqty = <?php echo $yearlyqty ?>; </script> <!-- Bootstrap core JavaScript--> <script src="../vendor/jquery/jquery.min.js"></script> <script src="../vendor/bootstrap/js/bootstrap.bundle.min.js"></script> <!-- Core plugin JavaScript--> <script src="../vendor/jquery-easing/jquery.easing.min.js"></script> <!-- Custom scripts for all pages--> <script src="../js/sb-admin-2.min.js"></script> <!-- Page level plugins --> <script src="../vendor/chart.js/Chart.min.js"></script> <!-- Page level custom scripts --> <script src="../js/demo/monthly-area-chart.js"></script> <script src="../js/demo/monthly-bar-chart.js"></script> <script src="../js/demo/yearly-area-chart.js"></script> <script src="../js/demo/yearly-bar-chart.js"></script> </body> </html> But still, the page is loading slowly. so Please suggest

Dear Team, In my project, I created the dashboard page. Once the user is logged in, we will prompt them to visit the dashboard page. On the dashboard page, there are many separate fields to display the values fetched from the database. Because of the many queries with large amounts of data, it is taking a long time to load the page. My code is <?php session_start(); error_reporting(0); include("connection.php"); $id=$_SESSION['id']; $query = "SELECT * FROM userdetails where id='$id'"; $qry_result = mysqli_query($conn, $query); $row=mysqli_fetch_array($qry_result); $userbranch = $row['branch']; $date = date('Y'); ?> <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <meta name="description" content=""> <meta name="author" content=""> <link rel="stylesheet" href="../css/style.css"> <link rel="stylesheet" href="../css/style1.css"> <link rel="stylesheet" href="../css/bootstrap.min.css"> <title>Dashboard</title> <link rel = "icon" href ="../Image/Company_Logo.jpg" type = "image/x-icon"> <link href="../vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css"> <link href="../css/sb-admin-2.min.css" rel="stylesheet"> <link href="https://fonts.googleapis.com/css?family=Nunito:200,200i,300,300i,400,400i,600,600i,700,700i,800,800i,900,900i" rel="stylesheet"> <script src="../js/bootstrap.bundle.min.js"></script> <script src="../js/jquery.min.js"></script> </head> <body> <div> <?php include('header.php'); ?> </div> <div> <div class="row" style="margin-left:10px;margin-top:10px;margin-right:10px"> <!-- Certificate Printed Card Example --> <div class="col mb-4"> <div class="card border-left-success shadow h-100 py-2"> <div class="card-body"> <div class="row no-gutters align-items-center"> <div class="col mr-2"> <div class="text-xxl-center font-weight-bold text-center text-success text-uppercase mb-1"> Certificate Printed</div> <?PHP $sql = "SELECT * FROM calibrationdata WHERE Branch = '$userbranch' And status = '1'" ; $result = mysqli_query($conn,$sql); $Printedreading=mysqli_num_rows($result); ?> <div class="h5 mb-2 font-weight-bold text-center text-gray-800"><?php echo $Printedreading; ?></div> <div class="col-auto text-center"> <input type="button" value="View" class="btn-sm btn-success" id="btnHome" onClick="document.location.href='printed.php'" /> </div> </div> </div> </div> </div> </div> <!-- Certificate Print Pending Card Example --> <div class="col mb-4"> <div class="card border-left-danger shadow h-100 py-2"> <div class="card-body"> <div class="row no-gutters align-items-center"> <div class="col mr-2"> <div class="text-xxl-center font-weight-bold text-center text-danger text-uppercase mb-1"> Certificate Print Pending</div> <?PHP $sql = "SELECT * FROM calibrationdata WHERE Branch = '$userbranch' And status = '0' " ; $result = mysqli_query($conn,$sql); $Printpending=mysqli_num_rows($result); ?> <div class="h5 mb-2 font-weight-bold text-center text-gray-800"><?php echo $Printpending; ?></div> <div class="col-auto text-center"> <input type="button" value="View" class="btn-sm btn-danger" id="btnHome" onClick="document.location.href='newreadings.php'" /> </div> </div> </div> </div> </div> </div> <!-- Certificate Pending beyond 7 days Card Example --> <div class="col mb-4"> <div class="card border-left-primary shadow h-100 py-2"> <div class="card-body"> <div class="row no-gutters align-items-center"> <div class="col mr-2"> <div class="text-xxl-center font-weight-bold text-center text-primary text-uppercase mb-1"> Pending beyond 7 days</div> <?PHP $sql = "SELECT * FROM calibrationdata WHERE Branch = '$userbranch' And status = '0'And today <= (NOW() - INTERVAL 7 DAY)" ; $result = mysqli_query($conn,$sql); $Sevendayspending=mysqli_num_rows($result); ?> <div class="h5 mb-2 font-weight-bold text-center text-gray-800"><?php echo $Sevendayspending; ?></div> <div class="col-auto text-center"> <input type="button" value="View" class="btn-sm btn-primary" id="btnHome" onClick="document.location.href='7daysreminder.php'" /> </div> </div> </div> </div> </div> </div> <!-- Reminder Mail to be sent Card Example --> <div class="col mb-4"> <div class="card border-left-warning shadow h-100 py-2"> <div class="card-body"> <div class="row no-gutters align-items-center"> <div class="col mr-2"> <div class="text-xxl-center font-weight-bold text-center text-warning text-uppercase mb-1"> Reminder Mail to be sent</div> <?PHP $sql = "SELECT * FROM calibrationdata WHERE Branch = '$userbranch' And Date <= (NOW() - INTERVAL 80 DAY) And First = '0000-00-00'" ; $result = mysqli_query($conn,$sql); $Remindermail=mysqli_num_rows($result); ?> <div class="h5 mb-2 font-weight-bold text-center text-gray-800"><?php echo $Remindermail; ?></div> <div class="col-auto text-center"> <input type="button" value="View" class="btn-sm btn-warning" id="btnHome" onClick="document.location.href='reminder_mail.php'" /> </div> </div> </div> </div> </div> </div> <!-- Canceled Certificate Card Example --> <div class="col mb-4"> <div class="card border-left-secondary shadow h-100 py-2"> <div class="card-body"> <div class="row no-gutters align-items-center"> <div class="col mr-2"> <div class="text-xxl-center font-weight-bold text-center text-gray-600 text-uppercase mb-1"> Canceled Certificate</div> <?PHP $sql = "SELECT * FROM calibrationdata WHERE Branch = '$userbranch' And status = '2'" ; $result = mysqli_query($conn,$sql); $Remindermail=mysqli_num_rows($result); ?> <div class="h5 mb-2 font-weight-bold text-center text-gray-800"><?php echo $Remindermail; ?></div> <div class="col-auto text-center"> <input type="button" value="View" class="btn-sm btn-secondary" id="btnHome" onClick="document.location.href='canceled_certificate.php'" /> </div> </div> </div> </div> </div> </div> </div> <div class="row" style="margin-left:10px;margin-right:10px"> <div class="col-xl-6 col-lg-7" > <!-- Bar Chart --> <div class="card shadow mb-4"> <div class="card-header py-3"> <h6 class="m-0 font-weight-bold text-primary">Monthly Invoice Details</h6> </div> <div class="card-body"> <div class="chart-bar" > <canvas id="myBarChart"></canvas> </div> </div> </div> </div> <div class="col-xl-6 col-lg-7"> <!-- Area Chart --> <div class="card shadow mb-4"> <div class="card-header py-3"> <h6 class="m-0 font-weight-bold text-primary">No of Calibration</h6> </div> <div class="card-body"> <div class="chart-area"> <canvas id="myAreaChart"></canvas> </div> </div> </div> </div> </div> <div class="row" style="margin-left:10px;margin-right:10px"> <div class="col-xl-6 col-lg-7" > <!-- Bar Chart --> <div class="card shadow mb-4"> <div class="card-header py-3"> <h6 class="m-0 font-weight-bold text-primary">Yearly Invoice Details</h6> </div> <div class="card-body"> <div class="chart-bar" > <canvas id="yearlyBarChart"></canvas> </div> </div> </div> </div> <div class="col-xl-6 col-lg-7"> <!-- Area Chart --> <div class="card shadow mb-4"> <div class="card-header py-3"> <h6 class="m-0 font-weight-bold text-primary">Yearly Calibration</h6> </div> <div class="card-body"> <div class="chart-area"> <canvas id="yearlyAreaChart"></canvas> </div> </div> </div> </div> </div> <?php $sql ="SELECT date_format(Date,'%b') as month, sum(amount) FROM calibrationdata WHERE YEAR(Date)=$date AND Branch = '$userbranch' group by year(Date),month(Date) order by year(Date),month(Date)"; $result = mysqli_query($conn,$sql); while ($row = mysqli_fetch_array($result)) { $Invoicemonth[] = $row['month'] ; $Invoiceamount[] = $row['sum(amount)']; $Inmonth = json_encode($Invoicemonth); $Invoice = json_encode($Invoiceamount); } $sql ="SELECT date_format(Date,'%b') as month, COUNT(*) COUNT FROM calibrationdata WHERE YEAR(Date)=$date AND Branch = '$userbranch' group by year(Date),month(Date) order by year(Date),month(Date)"; $result = mysqli_query($conn,$sql); while ($row = mysqli_fetch_array($result)) { $Calibemonth[] = $row['month'] ; $Calibqty[] = $row['COUNT']; $calmonth = json_encode($Calibemonth); $qty = json_encode($Calibqty); } $sql ="SELECT date_format(Date,'%Y') as month, sum(amount) FROM calibrationdata WHERE Branch = '$userbranch' group by year(Date) order by year(Date)"; $result = mysqli_query($conn,$sql); while ($row = mysqli_fetch_array($result)) { $yearlyInvoicemonth[] = $row['month'] ; $yearlyInvoiceamount[] = $row['sum(amount)']; $yearlyInmonth = json_encode($yearlyInvoicemonth); $yearlyInvoice = json_encode($yearlyInvoiceamount); } $sql ="SELECT date_format(Date,'%Y') as month, COUNT(*) COUNT FROM calibrationdata WHERE Branch = '$userbranch' group by year(Date) order by year(Date)"; $result = mysqli_query($conn,$sql); while ($row = mysqli_fetch_array($result)) { $yearlyCalibemonth[] = $row['month'] ; $yearlyCalibqty[] = $row['COUNT']; $yearlycalmonth = json_encode($yearlyCalibemonth); $yearlyqty = json_encode($yearlyCalibqty); } ?> </div> <script type="text/javascript"> var inmonth = <?php echo $Inmonth ?>; var invoice = <?php echo $Invoice ?>; var yearlyinmonth = <?php echo $yearlyInmonth ?>; var yearlyinvoice = <?php echo $yearlyInvoice ?>; var calmonth = <?php echo $calmonth ?>; var qty = <?php echo $qty ?>; var yearlycalmonth = <?php echo $yearlycalmonth ?>; var yearlyqty = <?php echo $yearlyqty ?>; </script> <!-- Bootstrap core JavaScript--> <script src="../vendor/jquery/jquery.min.js"></script> <script src="../vendor/bootstrap/js/bootstrap.bundle.min.js"></script> <!-- Core plugin JavaScript--> <script src="../vendor/jquery-easing/jquery.easing.min.js"></script> <!-- Custom scripts for all pages--> <script src="../js/sb-admin-2.min.js"></script> <!-- Page level plugins --> <script src="../vendor/chart.js/Chart.min.js"></script> <!-- Page level custom scripts --> <script src="../js/demo/monthly-area-chart.js"></script> <script src="../js/demo/monthly-bar-chart.js"></script> <script src="../js/demo/yearly-area-chart.js"></script> <script src="../js/demo/yearly-bar-chart.js"></script> </body> </html> Please suggest me how to reduce the page loading time.

i would like to do option b

Dear Team, I have a data table in my PHP, which is data getting from databse. In this table, I have four drop-down lists on my tablehead for filtering the data. In a single query, I am updating all four drop-down lists using an Ajax request. All four drop-down lists are getting updated with repeated values. Please refer the below output image I want the list to update distinct values instead of being repeated. My script is <script type="text/javascript" language="javascript"> $(document).ready(function () { dropdown_load_data(); }); function dropdown_load_data() { var Model = document.getElementById("Model").value; var CustomerName = document.getElementById("CustomerName").value; var ModelGroup = document.getElementById("ModelGroup").value; var ModelSubGroup = document.getElementById("ModelSubGroup").value; $.ajax({ url: 'dropdownOrder.php', type: 'POST', dataType: 'JSON', data: { Model: Model, CustomerName: CustomerName, ModelGroup: ModelGroup, ModelSubGroup: ModelSubGroup }, success: function (response) { var len = response.length for (var i = 0; i < len; i++) { var Model = response[i].Model; var CustomerName = response[i].CustomerName; var ModelGroup = response[i].ModelGroup; var ModelSubGroup = response[i].ModelSubGroup; $("#Model").append("<option>" + Model + "</option>"); $("#CustomerName").append("<option>" + CustomerName + "</option>"); $("#ModelGroup").append("<option>" + ModelGroup + "</option>"); $("#ModelSubGroup").append("<option>" + ModelSubGroup + "</option>"); } } }) } $(document).on('change', '#Model', function () { dropdown_load_data(); }); $(document).on('change', '#CustomerName', function () { dropdown_load_data(); }); $(document).on('change', '#ModelGroup', function () { dropdown_load_data(); }); $(document).on('change', '#ModelSubGroup', function () { dropdown_load_data(); }); }); </script> dropdownOrder.php <?php include("../connection.php"); $column = array("id", "Model", "MachineSlNo", "CustomerID", "CustomerName", "MachineStateCode", "MachineStateName", "MachineDistrictCode", "MachineDistrictName", "InstallationDate", "ModelGroup", "ModelSubGroup"); $where_columns = ['Model' => 'Model', 'CustomerName' => 'CustomerName', 'ModelGroup' => 'ModelGroup', 'ModelSubGroup' => 'ModelSubGroup']; $where_terms = []; foreach ($where_columns as $col => $input) { if ($_POST[$input] == !'') { $where_terms[] = "$col= '$_POST[$input]' "; } } $where = ''; if (!empty($where_terms)) { $where = 'WHERE ' . implode(' AND ', $where_terms); } else { $where = 'WHERE 1 '; } $query = "SELECT * FROM machinemaster $where"; $result = mysqli_query($conn, $query); while ($row = mysqli_fetch_array($result)) { $Model = $row['Model']; $CustomerName = $row['CustomerName']; $ModelGroup = $row['ModelGroup']; $ModelSubGroup = $row['ModelSubGroup']; $return_arr[] = array( "Model" => $Model, "CustomerName" => $CustomerName, "ModelGroup" => $ModelGroup, "ModelSubGroup" => $ModelSubGroup, ); } echo json_encode($return_arr); ?> My table is <table id="product_data" class="table table-striped table-bordered" style="width:auto;margin-left:1%;margin-right:1%;overflow-x:scroll;white-space:nowrap"> <thead class="bg-light"> <tr> <th>S.No</th> <th> <select name="category" id="Model" class="form-control form-select" style="width:auto"> <option value="">Model</option> </select> </th> <th style="white-space:nowrap">Machine S.No</th> <th>Customer ID</th> <th> <select name="category" id="CustomerName" class="form-control form-select"> <option value="">Customer Name</option> </select> </th> <th>State</th> <th>District</th> <th>Commissionning Date</th> <th> <select name="category" id="ModelGroup" class="form-control form-select" style="width:auto"> <option value="">Model Group</option> </select> </th> <th> <select name="category" id="ModelSubGroup" class="form-control form-select" style="width:auto"> <option value="">Model Sub Group</option> </select> </th> </tr> </thead> </table> Can anyone help with how to distinct the dropdown list?

Dear Mr.Barand, Thank you very much. It is working now. The first row and the last row are not inserting now.

Dear Mr.Barrand, Thanks for the reply. I changed the above code, and the header row is not inserting into the database. Except for the gross_amount column, all the columns are empty on the last row. Is it possible to ignore a value if it is empty?