Senthilkumar

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

As per your previous suggestion, all the data will be stored in the database using IDs only. The image displayed is just a sample. Please refer to my attached Excel. There, I put IDs for all the columns. While inserting the data, I will use that ID only. If this format is wrong, can you please suggest the correct format?

Dear Experts, I need suggestions from your side. I have an Excel sheet that I am using to enter the target values. My excel format looks like below image (column wise) I want to import those values into the MySQL database table in the below format (row-wise) because my actual value is updated in the same format. If my target table is also in the same format, it will be easy to display target and actual My excel file is attached with the below link https://docs.google.com/spreadsheets/d/1V15nBijl0sVeh-vGsz1XlRoyzdcslMNI/edit?usp=sharing&ouid=102271950721773454662&rtpof=true&sd=true Can anyone suggest how to do this? or suggest how to change the Excel file to get my required output.

Dear Mr. Barand, Thanks for providing the code. It is working perfectly and fast as well. Sorry for not replying. This is just testing data. I am not updating any rows. I am only inserting the data every month. There won't be any duplicate data updates.

Dear All, Thanks for the reply. As instructed by Mr. MacGyver, I modified the code. The table is now being filled with all of the data that has been imported. <?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['importsubmit'])) { $db_col = [0 => 'Sales_Document_Type', 2 => 'Billing_Date', 11 => 'Material', 25 => 'Gross_Amount', 45 => 'Sales_Office', 47 => 'Plant']; // database table name $table = 'billing'; // number of initial lines to skip in the file $skip = 1; $file = $_FILES['file']['tmp_name']; // open file if (($handle = fopen($file, "r")) !== FALSE) { // dynamically build the sql query $sql = "INSERT INTO `$table` (`" . implode('`,`', array_values($db_col)) . "`) VALUES (" . implode(',', array_fill(0, count($db_col), '?')) . ")"; // this example uses the much simpler and more modern PDO database extension $stmt = $pdo->prepare($sql); // start line counter at 1 $line = 1; // read lines from file while (($row = fgetcsv($handle, 1000, ",")) !== FALSE) { // process lines after initial skipped lines if ($line > $skip) { // special processing // convert Billing_Date to standard format $row[2] = date('Y-m-d', strtotime($row[2])); // remove comma separator from Gross_Amount $row[25] = str_replace(',', '', $row[25]); // build array of value using array intersection method $params = array_values(array_intersect_key($row, $db_col)); $stmt->execute($params); } $line++; } fclose($handle); } else { die('unable to open uploaded (temporary) file'); } } // Redirect to the listing page header("Location: Billing.php"); ?> Is it possible to group the rows material- and plant-wise and sum the gross amount before importing? So that the number of rows will reduced while importing

Billing.php <?PHP include("../connection.php"); ?> <!DOCTYPE html> <html lang="en"> <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="" /> <title>Branch Table</title> <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/style.min.css" rel="stylesheet" /> <link href="css/styles.css" rel="stylesheet" /> <link href="css/bootstrap.min.css" rel="stylesheet" /> <link href="vendor/fontawesome-free/css/fontawesome.min.css" rel="stylesheet" /> <script src="https://use.fontawesome.com/releases/v6.3.0/js/all.js" crossorigin="anonymous"></script> <link href="vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css" /> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.12.1/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/alasql/0.5.5/alasql.min.js"></script> </head> <body class="sb-nav-fixed" style="font-family:Cambria"> <nav class="sb-topnav navbar navbar-expand navbar-light bg-secondary border border-success p-2 mb-2 border-opacity-50 shadow"> <!-- Navbar Brand--> <img src="../Image/Logo.png" style="margin-left:5px" /> <!-- Sidebar Toggle--> <button class="btn btn-link btn-sm order-1 order-lg-0 me-4 me-lg-0 text-white" id="sidebarToggle" href="#!" style="margin-left:15px"> <i class="fas fa-bars"></i> </button> <!-- Navbar Search--> <form class="d-none d-md-inline-block form-inline ms-auto me-0 me-md-3 my-2 my-md-0 text-white"> <div class="input-group"> Srilekha R </div> </form> <!-- Navbar--> <ul class="navbar-nav ms-auto ms-md-0 me-3 me-lg-4 text-white"> <li class="nav-item dropdown text-white"> <a class="nav-link dropdown-toggle text-white" id="navbarDropdown" href="#" role="button" data-bs-toggle="dropdown" aria-expanded="false"> <i class="fas fa-user fa-fw"></i> </a> <ul class="dropdown-menu dropdown-menu-end" aria-labelledby="navbarDropdown"> <li> <a class="dropdown-item" href="#!">Settings</a> </li> <li> <a class="dropdown-item" href="#!">Activity Log</a> </li> <li> <hr class="dropdown-divider" /> </li> <li> <a class="dropdown-item" href="#!">Logout</a> </li> </ul> </li> </ul> </nav> <style> .sb-sidenav-menu .nav .nav-link .sb-nav-link-icon { color: white; } .sb-sidenav-menu .nav .nav-link { color: white; } .sb-sidenav-menu .nav .nav-link .sb-nav-link-icon { color: white; } .sb-sidenav-menu .nav .nav-link:hover { color: black; box-shadow: inset 2px 2px 2px 2px gray; background-color: lightgray } </style> <div id="layoutSidenav"> <div id="layoutSidenav_nav" style="margin-top:57px !important; height:100vh"> <nav class="sb-sidenav accordion sb-sidenav bg-secondary border border-success p-2 mb-2 border-opacity-50 shadow" id="sidenavAccordion"> <div class="sb-sidenav-menu"> <div class="nav"> <a class="nav-link " href="dashboard.php"> <div class="sb-nav-link-icon"> <i class="fas fa-tachometer-alt"></i> </div> Dashboard </a> <a class="nav-link collapsed" href="#" data-bs-toggle="collapse" data-bs-target="#collapseLayouts" aria-expanded="false" aria-controls="collapseLayouts"> <div class="sb-nav-link-icon"> <i class="fas fa-columns"></i> </div> Master <div class="sb-sidenav-collapse-arrow"> <i class="fas fa-angle-down"></i> </div> </a> <div class="collapse fade-up" id="collapseLayouts" aria-labelledby="headingOne" data-bs-parent="#sidenavAccordion"> <nav class="sb-sidenav-menu-nested nav"> <a class="nav-link" href="layout-static.html">Manpower Master</a> <a class="nav-link" href="layout-sidenav-light.html">Infrastructure</a> <a class="nav-link" href="layout-sidenav-light.html">Engineer Skill Matrix</a> <a class="nav-link" href="Department_Master.php">Department Master</a> <a class="nav-link" href="Branch_Master.php">Branch Master</a> </nav> </div> <a class="nav-link collapsed" href="#" data-bs-toggle="collapse" data-bs-target="#projectionandBilling" aria-expanded="false" aria-controls="collapseLayouts"> <div class="sb-nav-link-icon"> <i class="fas fa-columns"></i> </div> Projection & Billing <div class="sb-sidenav-collapse-arrow"> <i class="fas fa-angle-down"></i> </div> </a> <div class="collapse fade-up" id="projectionandBilling" aria-labelledby="headingOne" data-bs-parent="#sidenavAccordion"> <nav class="sb-sidenav-menu-nested nav"> <a class="nav-link" href="layout-static.html">Monthly Projection</a> <a class="nav-link" href="layout-sidenav-light.html">Monthly Billing</a> </nav> </div> <a class="nav-link collapsed" href="#" data-bs-toggle="collapse" data-bs-target="#collapsePages" aria-expanded="false" aria-controls="collapsePages"> <div class="sb-nav-link-icon"> <i class="fas fa-book-open"></i> </div> Pages <div class="sb-sidenav-collapse-arrow"> <i class="fas fa-angle-down"></i> </div> </a> <div class="collapse" id="collapsePages" aria-labelledby="headingTwo" data-bs-parent="#sidenavAccordion"> <nav class="sb-sidenav-menu-nested nav accordion" id="sidenavAccordionPages"> <a class="nav-link collapsed" href="#" data-bs-toggle="collapse" data-bs-target="#pagesCollapseAuth" aria-expanded="false" aria-controls="pagesCollapseAuth"> Authentication <div class="sb-sidenav-collapse-arrow"> <i class="fas fa-angle-down"></i> </div> </a> <div class="collapse" id="pagesCollapseAuth" aria-labelledby="headingOne" data-bs-parent="#sidenavAccordionPages"> <nav class="sb-sidenav-menu-nested nav"> <a class="nav-link" href="login.html">Login</a> <a class="nav-link" href="register.html">Register</a> <a class="nav-link" href="password.html">Forgot Password</a> </nav> </div> <a class="nav-link collapsed" href="#" data-bs-toggle="collapse" data-bs-target="#pagesCollapseError" aria-expanded="false" aria-controls="pagesCollapseError"> Error <div class="sb-sidenav-collapse-arrow"> <i class="fas fa-angle-down"></i> </div> </a> <div class="collapse" id="pagesCollapseError" aria-labelledby="headingOne" data-bs-parent="#sidenavAccordionPages"> <nav class="sb-sidenav-menu-nested nav"> <a class="nav-link" href="401.html">401 Page</a> <a class="nav-link" href="404.html">404 Page</a> <a class="nav-link" href="500.html">500 Page</a> </nav> </div> </nav> </div> <a class="nav-link" href="charts.html"> <div class="sb-nav-link-icon"> <i class="fas fa-chart-area"></i> </div> Charts </a> <a class="nav-link" href="tables.html"> <div class="sb-nav-link-icon"> <i class="fas fa-table"></i> </div> Tables </a> </div> </div> <div class="sb-sidenav-footer text-white" style="margin-bottom:55px"> <div class="small">Last Logged in:</div> 16-Oct-2023 10.52 PM </div> </nav> </div> <div id="layoutSidenav_content"> <main class="text-muted"> <div class="container-fluid px-4"> <h2 class="mt-2">Outstanding Report</h2> </div> <!--End of download file--> <!--upload file--> <?php if (!empty($_GET['status'])) { switch ($_GET['status']) { case 'succ': $statusType = 'alert-success'; $statusMsg = 'Member data has been imported successfully.'; break; case 'err': $statusType = 'alert-danger'; $statusMsg = 'Something went wrong, please try again.'; break; case 'invalid_file': $statusType = 'alert-danger'; $statusMsg = 'Please upload a valid Excel file.'; break; default: $statusType = ''; $statusMsg = ''; } } ?> <!-- Display status message --> <?php if (!empty($statusmsg)) { ?> <div class="col-xs-12 p-3"> <div class="alert <?php echo $statustype; ?>"> <?php echo $statusmsg; ?> </div> </div> <?php } ?> <div class="row p-3"> <!-- import link --> <!--Upload code--> <div class="col-md-12 head"> <div class="float-start"> <a href="javascript:void(0);" class="btn btn-success" onclick="formToggle();"> <i class="fas fa-plus"></i> Import Excel </a> </div> </div> <!-- excel file upload form --> <div class="col-md-12 mt-4" id="importfrm" style="display: none;"> <form class="row g-3" action="Billing_Import.php" method="post" enctype="multipart/form-data" style="border: 2px dashed #007bff;float:left"> <div class="col-auto"> <label for="fileinput" class="visually-hidden">file</label> <input type="file" class="form-control" name="file" id="fileinput" /> </div> <div class="col-auto"> <input type="submit" class="btn btn-primary mb-3" name="importsubmit" value="import" /> </div> </form> </div> </div> </main> </div> </div> <script src="vendor/bootstrap/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script> <script src="js/scripts.js"></script> <script src="vendor/bootstrap/js/bootstrap5.2.bundle.min.js"></script> <script src="vendor/jquery/jquery.min.js"></script> <script src="vendor/bootstrap/js/bootstrap.js"></script> <script src="js/Toggle.js"></script> </body> </html> Billing_Import.php <?php // Load the database configuration file include_once 'connection.php'; // Include PhpSpreadsheet library autoloader require_once 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; if(isset($_POST['importsubmit'])){ // Allowed mime types $excelMimes = array('text/xls', 'text/xlsx', 'application/excel', 'application/vnd.msexcel', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // Validate whether selected file is a Excel file if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $excelMimes)){ // If the file is uploaded if(is_uploaded_file($_FILES['file']['tmp_name'])){ $reader = new Xlsx(); $spreadsheet = $reader->load($_FILES['file']['tmp_name']); $worksheet = $spreadsheet->getActiveSheet(); $worksheet_arr = $worksheet->toArray(); // Remove header row unset($worksheet_arr[0]); foreach($worksheet_arr as $row){ $Sales_Document_Type = $row[0]; $Billing_Date = $row[2]; $Material= $row[11]; $Gross_Amount= $row[25]; $Sales_Office= $row[45]; $Plant= $row[47]; $Dateformat = date('Y-m-d', strtotime($Billing_Date)); $amount = str_replace(',', '', $Gross_Amount); // Check whether member already exists in the database with the same email $prevQuery = "SELECT billing FROM members WHERE Billing_Date = '".$Dateformat."' "; $prevResult = mysqli_query($conn, $prevQuery); if($prevResult->num_rows > 0){ // Update member data in the database $db = mysqli_query($conn,"UPDATE members SET first_name = '".$first_name."', last_name = '".$last_name."', email = '".$email."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'"); }else{ // Insert member data in the database $db = "INSERT INTO billing (Sales_Document_Type, Billing_Date, Gross_Amount, Sales_Office, Plant, Material) VALUES ('$Sales_Document_Type', '$Dateformat', '$amount', '$Sales_Office', '$Plant','$Material')"; $result = mysqli_query($conn, $db); } } $qstring = '?status=succ'; }else{ $qstring = '?status=err'; } }else{ $qstring = '?status=invalid_file'; } } // Redirect to the listing page header("Location: Billing.php".$qstring); ?>

The code is available with the link is provided above

I am getting the following error Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 33554432 bytes) in file:///C:/Users/senthilkumar.rp/Desktop/Calibration/Calibration/Calibration/Admin/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:209

Dear Team, Every month, I am downloading the billing data from SAP in Excel and importing it into the MySQL database (not all the columns from Excel). For one month, the downloaded file size was 17 MB, and it has over 31000 rows in Excel. So the file is not getting imported into the database. To reduce the number of rows, I planned to group the data in the columns Material (11) and Sales Office (45), like we are using in the MySQL query (group by Material). So that the rows will be reduced while inserting into the database. I am attaching my dump, PHP, and Excel at the below link. https://drive.google.com/file/d/1fTS-mkTKe93ZtxMmn8afkK6L2lUSn7PH/view?usp=sharing Please suggest how to do this.z

Dear Barand, Please support

Thank for providing tutorials

Dear Barand, Please find the attached dump of both the tables userdetails.txt calibrationdata.txt

Dear Banrand, As per your instruction i am storing the id only for all tables. My question is if i want to display the name of that id how can i do this. if suppose there is a search query, then how can i search by name. Can you plese provide any sample for that.