Senthilkumar Posted December 15, 2023 Share Posted December 15, 2023 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted December 15, 2023 Share Posted December 15, 2023 (edited) the code for any page should be laid out in this general order - initialization post method form processing get method business logic - get/produce data needed to display the page html document all the database specific code you have on this page should be in section #3 in this layout. this makes it easier to write, test, and debug your code and query(ies), and now that you need to modify the code/query(ies), puts it all in one place. if you are only getting a count of the number of matching rows, don't select all the columns (SELECT *) and all the rows. use a SELECT COUNT(*) ... query instead, then fetch the count value. the three queries getting a count of the number of matching rows with status = 0, 1, and 2 can be done in a single query. you can get the SUM(amount) and COUNT(*), where the rest of the query is the same, in a single query. lastly, the json_encode() should not be inside the loops. this is repeatedly encoding the array after each row is added, leaving only the last result. this probably where the most time is being taken. the json_encode() statements should be after the end of the relevant loop. Edited December 15, 2023 by mac_gyver Quote Link to comment Share on other sites More sharing options...
Phi11W Posted December 15, 2023 Share Posted December 15, 2023 Five of your queries use "SELECT *". Do not do this in Production code. Databases are intrinsically shared entities and table structures can be changed [by anyone] at any time. Retrieving more fields than you actually need leaves you open to expected slow-downs, not of your making. Your remaining four queries could be combined into two. Taking the first pair, you can retrieve both aggregated values in one query: SELECT date_format(Date,'%Y') as month, COUNT(*) COUNT FROM calibrationdata WHERE Branch = '$userbranch' group by year(Date) order by year(Date) SELECT date_format(Date,'%Y') as month, sum(amount) FROM calibrationdata WHERE Branch = '$userbranch' group by year(Date) order by year(Date) // Can be combined into SELECT DATE_FORMAT( `Date`,'%Y' ) as month , COUNT( * ) as tally , SUM( amount ) as total FROM calibrationdata WHERE Branch = '$userbranch' GROUP BY year( `Date` ) ORDER BY year( `Date` ) Execute the query once, retrieve the values into an intermediate variable, then display that at the relevant point on the page. Also,. make sure that you have a database index supporting querying this table by Branch. Also, take a look at Parameterised Queries (Prepared Statements) to protect against SQL Injection Attacks. Obligatory XKCD Reference: Little Bobby Tables. Regards, Phill W. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted December 15, 2023 Author Share Posted December 15, 2023 (edited) 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 Edited December 15, 2023 by Senthilkumar Quote Link to comment Share on other sites More sharing options...
Barand Posted December 15, 2023 Share Posted December 15, 2023 Look at the results from your first query. How many rows are returned? How many fetches do you perform? Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted December 15, 2023 Author Share Posted December 15, 2023 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 15, 2023 Share Posted December 15, 2023 Try this to replace your first 6 queries SELECT u.branch , sum(status=1) as printed , sum(status=0) as pending , sum(status=2) as cancelled , sum(status=0 AND today <= CURDATE() - INTERVAL 7 DAY) as over7days , sum(first='0000-00-00' AND date <= CURDATE() - INTERVAL 80 DAY) as reminder FROM calibrationdata c JOIN userdetails u USING (branch) WHERE u.id = 16; +--------+---------+---------+-----------+-----------+----------+ | branch | printed | pending | cancelled | over7days | reminder | +--------+---------+---------+-----------+-----------+----------+ | Mumbai | 8 | 3 | 0 | 3 | 3 | +--------+---------+---------+-----------+-----------+----------+ Quote Link to comment Share on other sites More sharing options...
Phi11W Posted December 15, 2023 Share Posted December 15, 2023 Hi Barand, If only for completeness, shouldn't you have a "group by Branch" in there? Regards, Phill Ward. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 15, 2023 Share Posted December 15, 2023 Yess Phill, you are correct but there will only be a single branch. My original query was SELECT branch , sum(status=1) as printed , sum(status=0) as pending , sum(status=2) as cancelled , sum(status=0 AND today <= CURDATE() - INTERVAL 7 DAY) as over7days , sum(first='0000-00-00' AND date <= CURDATE() - INTERVAL 80 DAY) as reminder FROM calibrationdata c WHERE branch = ? but I changed it on posting to demonstrate that he should be joining to the userdetails table intead of the first query to get the branch Quote Link to comment Share on other sites More sharing options...
Barand Posted December 15, 2023 Share Posted December 15, 2023 @Senthilkumar is this any faster than yours? It should get all the data you need in a single query. <?php $sql = "SELECT c.branch , c.printed , c.pending , c.cancelled , c.over7days , c.reminder , y.year , y.yrtotal , y.yrcount , m.month , m.mthtotal , m.mthcount FROM ( SELECT c.branch , sum(status=1) as printed , sum(status=0) as pending , sum(status=2) as cancelled , sum(status=0 AND today <= CURDATE() - INTERVAL 7 DAY) as over7days , sum(first='0000-00-00' AND date <= CURDATE() - INTERVAL 80 DAY) as reminder FROM calibrationdata1 c JOIN userdetails1 u USING (branch) WHERE u.id = ? GROUP BY branch ) c JOIN ( SELECT branch , date_format(date, '%Y') as year , sum(amount) as yrtotal , count(*) as yrcount FROM calibrationdata1 GROUP BY branch, year(date) ) y USING (branch) JOIN ( SELECT branch , date_format(date, '%b') as month , sum(amount) as mthtotal , count(*) as mthcount FROM calibrationdata1 WHERE year(date) = year(curdate()) GROUP BY branch, month(date) ) m USING (branch) "; $res = $pdo->prepare($sql); // NOTE: PDO connection in use $res->execute([ $_SESSION['id'] ?? 16 ]); $counts = []; $yrdata = []; $monthdata = []; foreach ($res as $row) { $counts = array_slice($row, 0, 6); $yrdata[$row['year']] = array_slice($row, 6,3); $monthdata[$row['month']] = array_slice($row, 9); } echo "<table style='width: 600px; margin: 20px; text-align: center'>" . "<tr><th>" . join('</th><th>', array_keys($counts)) . "</th></tr>" . "<tr><td>" . join('</td><td>', array_values($counts)) . "</td></tr>" . "</table>"; echo printArray($yrdata); echo printArray($monthdata); function printArray($arr) { $out = "<table style='width: 200px; margin: 20px; text-align: center'>" . "<tr><th>" . join('</th><th>', array_keys(current($arr))) . "</th></tr>"; foreach ($arr as $a) { $out .= "<tr><td>" . join('</td><td>', array_values($a)) . "</td></tr>"; } $out .= "</table>\n"; return $out; } ?> Example output 1 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted December 15, 2023 Share Posted December 15, 2023 you also need to profile your code (calculate differences in microtime(true) values for the different operations on the page) to determine where it spends its time. i suspect that the time taken on the server isn't where the problem is, but in the execution of the javascript in the browser. 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.