Jump to content

Dashboard is taking long tome to load page because of the huge data in databse


Senthilkumar

Recommended Posts

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.

Link to comment
Share on other sites

the code for any page should be laid out in this general order -

  1. initialization
  2. post method form processing
  3. get method business logic - get/produce data needed to display the page
  4. 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 by mac_gyver
Link to comment
Share on other sites

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.Ā 

Link to comment
Share on other sites

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 by Senthilkumar
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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        |
+--------+---------+---------+-----------+-----------+----------+

Ā 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

@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

image.png.38d26995a7cb1fa41574a688b0a9b3eb.png

  • Like 1
Link to comment
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.