[FIXED] When i use a sql command in a for loop for uploading multiple rows in an excel file it doesnt work...it just uploads the first row

Issue

When i use a sql command in a for loop for uploading multiple rows in an excel file it doesnt work...it just uploads the first row This is my Code to save the entries into sql database...it is an old project where many functions were deprecated..i have replaced almost all of them but still it doesnt work..the upload file will be an excel file with multiple rows..only the first row data gets saved in DB

<?php

if ($_FILES['userfile']['size'] > 0) {
    $fileName = $_FILES['userfile']['name'];
    $tmpName = $_FILES['userfile']['tmp_name'];
    $fileSize = $_FILES['userfile']['size'];
    $fileType = $_FILES['userfile']['type'];

    $fp = fopen($tmpName, 'r');
    $content = fread($fp, filesize($tmpName));
    $content = addslashes($content);
    fclose($fp);

    $fileName = addslashes($fileName);


    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);

    define('EOL', (PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

    date_default_timezone_set('Europe/London');

    /** Include PHPExcel_IOFactory */
    require_once '../Classes/PHPExcel.php';
    require_once '../Classes/PHPExcel/IOFactory.php';
    require_once '../db.php';


    if (!file_exists("$tmpName")) {
        exit("Contacts File does not exist" . EOL);
    }
    $objReader = PHPExcel_IOFactory::createReaderForFile("$tmpName");
    $objReader->setReadDataOnly(false);
    $objPHPExcel  = $objReader->load("$tmpName");
    $objWorksheet = $objPHPExcel->getActiveSheet();
    $sql = '';
    $i = 0;
    $status = 0;
    foreach ($objWorksheet->getRowIterator() as $row) {
        if ($row->getRowIndex() > 1) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            $values = array();
            foreach ($cellIterator as $cell) {
                $cellval = trim(iconv("UTF-8", "ISO-8859-1", $cell->getValue()), " \t\n\r\0\x0B\xA0\xA0* ");
                if (empty($cellval)) {
                    $cellval = "NULL";
                    array_push($values, $cellval);
                } else {
                    array_push($values, '"' . $cellval . '"');
                } // if (is_numeric($cellval))
            } // foreach ($cellIterator as $cell)
            // build the insert statement from the values in the cells.
            $mysqli->set_charset("utf8");
            $sql = "Call Insert_Contact(" . $values[0] . "," . $values[1] . "," . $values[2] . "," . $values[3] . "," . $values[4] . "," . $values[5] . "," . $values[6] . "," . $values[7] . "," . $values[8] . "," . $values[9] . "," . $values[10] . "," . $values[11] . ",@inserted);";
            $qry1 = "SELECT @inserted AS status";
            $mysqli->query($sql);
            $stmt = $mysqli->query($qry1);
            $row1 = $stmt->fetch_assoc();
            $status = $row1['status'];
            $stmt->close();
            echo mysqli_error($mysqli);
        }
        $i = $i + $status;
    }
    if (!mysqli_error($mysqli)) {
        echo $i . " contacts Uploaded";
    } else {
        echo    mysqli_error($mysqli);
        //include 'library/closedb.php';
    }
}
?>

Solution

You can use "LOAD DATA Statement" bellow

LOAD DATA LOCAL INFILE '$tmp_file_path' IGNORE
                INTO TABLE tmp_table
                FIELDS TERMINATED BY ','
                ENCLOSED BY '\"'
                LINES TERMINATED BY '\n'
                IGNORE 1 ROWS

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

If you do not want all data from your file then create a temporary table and all data of the file in that table after that you can simply insert data from that temporary table like this

INSERT IGNORE INTO `tbl1` ( col1, col2, col3)   
SELECT col1, col2, col3 FROM tmp_table

Answered By - Parth Mangukiya

Answer Checked By - Cary Denson (PHPFixing Admin)