Senthilkumar

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?

Dear Mr.Barand, The above code is inserting headers along with data while importing a CSV file. In my CSV file, the last row has the total sum of the gross amount. How to ignore the header (first row) and sum value (last row) in this code

Thanks for your reply. I will check with pagination.

Dear Team, In my database table (billing), I have 271988 rows of data. When I am trying to display that data in the table of my PHP page, I am getting the following error while loading the page: My table code is <table class="table row-border hover cell-border" id="example" style="font-size:12px"> <tr> <th>Sales Document Type</th> <th>Billing Date</th> <th>Material</th> <th>Gross Amount</th> <th>Sales Office</th> <th>Plant</th> <th>Distribution Channel Desp</th> <th>Div(Hdr)</th> <th>Div(Hdr)</th> </tr> <?PHP $Query = "SELECT * FROM billing"; $result = mysqli_query($conn, $Query); while ($row = mysqli_fetch_assoc($result)) { ?> <tr> <td> <?PHP echo $row['sales_doc_type']; ?> </td> <td> <?PHP echo $row['billing_date']; ?> </td> <td> <?PHP echo $row['material']; ?> </td> <td> <?PHP echo $row['gross_amount']; ?> </td> <td> <?PHP echo $row['sales_office']; ?> </td> <td> <?PHP echo $row['plant']; ?> </td> <td> <?PHP echo $row['distribution_channel_description']; ?> </td> <td> <?PHP echo $row['division']; ?> </td> <td> <?PHP echo $row['division_header']; ?> </td> </tr> <?PHP } ?> </table> Is it possible to display 271988 rows? Please tell me how to do this. My database table dump is attached the below link https://drive.google.com/file/d/1i2fTg4GnMTgXWPHc1JkwOHfQu--eYKky/view?usp=sharing

The current process for adjusting the target is to import billing details from a CSV file that is downloaded from SAP. I will share the details with you shortly. There are still some tables that need to be finalized.

Dear Mr.Barand, Please confirm the below method is correct or worng. I modified the code for save the target month wise using the value and percentage. <?php // Load the database configuration file define("HOST", 'localhost'); define("USERNAME", 'root'); define("PASSWORD", '123456'); define("DATABASE", 'sbms'); // default database name function pdoConnect($dbname = DATABASE) { $db = new PDO("mysql:host=" . HOST . ";dbname=$dbname;charset=utf8", USERNAME, PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; } $pdo = pdoConnect(); if (isset($_POST['partssubmit'])) { ################################################################################ # CREATE TEMPORARY STAGING TABLE # ################################################################################ $pdo->exec("CREATE TEMPORARY TABLE tempdata ( id int not null auto_increment primary key, branch_id int(11), dept_id int(11), grp_id int(11), category_id int(11), sub_id int(11), description varchar(50), value int(11), Q1 int(11), Q2 int(11), Q3 int(11), Q4 int(11) ) "); ################################################################################ # CSV COLUMNS # ################################################################################ $main = [ 1 => 'branch_id', 3 => 'dept_id', 5 => 'grp_id', 7 => 'category_id', 9 => 'sub_id' ]; $descrips = [ 10 => 'Wear Part', // if you have ids for these items, use those instead of text descriptions 11 => 'Consumables', 12 => 'Accessories', 13 => 'Electrical', 14 => 'Other Parts', 15 => 'Pipeline', 16 => 'Mixer Drum' ]; $qtrs = [ 17 => 'Q1', 18 => 'Q2', 19 => 'Q3', 20 => 'Q4' ]; ################################################################################ # READ CSV AND CREATE DATA ARRAY # ################################################################################ $file = $_FILES['file']['tmp_name']; $fp = fopen($file, 'r'); $hdr = fgetcsv($fp); // ignore header row $data = []; while ($line = fgetcsv($fp)) { $reca = array_intersect_key($line, $main ); $recc = array_intersect_key($line, $qtrs ); foreach (array_intersect_key($line, $descrips ) as $k => $d) { // need a record for each description with a value if ($d) { $recb = [ $descrips[$k], (empty($d) ? 0 : $d ) ]; $data[] = vsprintf("(%d,%d,%d,%d,%d,'%s',%d,%d,%d,%d,%d)", array_merge($reca, $recb, $recc)); } } } fclose($fp); ################################################################################ # WRITE RECORDS FROM ARRAY TO target TABLE # ################################################################################ $chunks = array_chunk($data, 2000); foreach ($chunks as $ch) { $pdo->exec("INSERT INTO tempdata (branch_id, dept_id, grp_id, category_id, sub_id, description, value, Q1, Q2, Q3, Q4) VALUES ". join(',', $ch) ); } $pdo->exec("INSERT INTO target1 (branch_id, dept_id, grp_id, category_id, sub_id, description, value, Q1, Q2, Q3, Q4,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dece) SELECT t.branch_id , t.dept_id , t.grp_id , t.category_id , t.sub_id , t.description , t.value , t.Q1 , t.Q2 , t.Q3 , t.Q4 ,((t.value*(t.Q1/100))/3) As Jan ,((t.value*(t.Q1/100))/3) As Feb ,((t.value*(t.Q1/100))/3) As Mar ,((t.value*(t.Q2/100))/3) As Apr ,((t.value*(t.Q2/100))/3) As May ,((t.value*(t.Q2/100))/3) As Jun ,((t.value*(t.Q3/100))/3) As Jul ,((t.value*(t.Q3/100))/3) As Aug ,((t.value*(t.Q3/100))/3) As Sep ,((t.value*(t.Q4/100))/3) As Oct ,((t.value*(t.Q4/100))/3) As Nov ,((t.value*(t.Q4/100))/3) As Dece FROM tempdata t "); } // Redirect to the listing page header("Location: Target.php"); ?> The output i am getting is Also i need sugession for how to change the target subsequently for evey month based on the previous month target. Is there any new table to be create for store the target month wise. if yes how to create the table and update the data every month target1 schema.txt

Dear Barand, I created and split the data into two tables. But how can I display the percentage of Q1, Q2, Q3 and Q4 values in tables? <table class="table"> <tr> <th>Branch</th> <th>Department</th> <th>Group</th> <th>Category</th> <th>Equipment</th> <th>Description</th> <th>Target(Rs)</th> <th>Q1(%)</th> <th>Q2(%)</th> <th>Q3(%)</th> <th>Q4(%)</th> </tr> <?PHP $Query = "SELECT t.*, b.branchName, d.deptName,g.grpName, c.catName, p.subCatName FROM target t INNER JOIN branch b ON t.branch_id = b.branchID INNER JOIN deparment d ON t.dept_id = d.deptID INNER JOIN dept_group g ON t.grp_id = g.grpID INNER JOIN category c ON t.category_id = c.catID INNER JOIN productcategory p ON t.sub_id = p.subCatID"; $result = mysqli_query($conn, $Query); while ($row = mysqli_fetch_assoc($result)) { ?> <tr> <td> <?PHP echo $row['branchName']; ?> </td> <td> <?PHP echo $row['deptName']; ?> </td> <td> <?PHP echo $row['grpName']; ?> </td> <td> <?PHP echo $row['catName']; ?> </td> <td> <?PHP echo $row['subCatName']; ?> </td> <td> <?PHP echo $row['description']; ?> </td> <td> <?PHP echo $row['value']; ?> </td> <td> <?PHP echo $row['Q1']; ?> </td> <td> <?PHP echo $row['Q2']; ?> </td> <td> <?PHP echo $row['Q3']; ?> </td> <td> <?PHP echo $row['Q4']; ?> </td> </tr> <?PHP } ?> </table>

Dear Mr.Barand, You are correct. The quarterly target percentage is set individually for each branch, department,group, category, and subcategory. Initially, the percentage will be set to 15, 25, 25, and 35 by default for all. The admin will change that percentage based on the branch performance for every year. The value he selected will be divided into 12 months based on the percentage entered.Please refer to the below image. This above-mentioned monthly target is a basic target. Every month, the tatger will change based on the actual values updated. Consequetively the target will be added for the next month based on the actual update. The target file and the formulated example file are attached here for your reference. https://drive.google.com/drive/folders/1upVIFjTxfBKNgYhp2zK3jtooIeNhCLtN?usp=sharing

Ok. Thank you. Any other suggestion from your side for improving myself?

Thanks for the suggestion. I have a separate table for the description. For each description, there will be 8-digit codes available. Shall I use an 8-digit code or row ID

Dear Barrand, Thanks for your support. I converted the Excel file to CSV. Then, using your code, I imported the data row-wise with ID only. Please refer the below image And using the ID, i display name from different tables <table class="table"> <tr> <th>Branch</th> <th>Department</th> <th>Group</th> <th>Product Category</th> <th>Sub Category</th> <th>Description</th> <th>Target(Rs)</th> <th>Q1</th> <th>Q2</th> <th>Q3</th> <th>Q4</th> </tr> <?PHP $Query = "SELECT t.*, b.branchName, d.deptName,g.grpName FROM target t INNER JOIN branch b ON t.branch_id = b.branchID INNER JOIN deparment d ON t.dept_id = d.deptID INNER JOIN group1 g ON t.grp_id = g.grpID"; $result = mysqli_query($conn, $Query); while ($row = mysqli_fetch_assoc($result)) { ?> <tr> <td><?PHP echo $row['branchName']; ?></td> <td><?PHP echo $row['deptName']; ?></td> <td><?PHP echo $row['grpName']; ?></td> <td><?PHP echo $row['category_id']; ?></td> <td><?PHP echo $row['sub_id']; ?></td> <td><?PHP echo $row['description']; ?></td> <td><?PHP echo $row['value']; ?></td> <td><?PHP echo $row['Q1']; ?></td> <td><?PHP echo $row['Q2']; ?></td> <td><?PHP echo $row['Q3']; ?></td> <td><?PHP echo $row['Q4']; ?></td> </tr> <?PHP } ?> </table> I think this time I am not using the database, like an Excel spread sheet.

The Excel sheet that is in the link is correct. I hidden the ID column and took the snap from that excel for reference only

ok.

Dear Mr.Barand, Please guide me