Website cannot be accessed while adding data to mysql database

Question

I am working on a project on my laptop using wamp. I have about 17000 rows and 6 columns of data to add into 4 different tables of the database. This is done via uploading an excel file and clicking an import button. NB! I am using an excel to mysql plugin, this is necessary for the project as it is one of the functions of the program. Normally there will not be as much data inserted into the tables. Everything works fine although it takes long. I am just worried that during the upload the whole site is inaccessible. Below is the upload script. Any tips to improve the below script to speed up the insert and make the site accessible while the insert is busy?

<?php
$conn = mysqli_connect("localhost","root","","eftposcentral");
require_once('vendor/php-excel-reader/excel_reader2.php');
require_once('vendor/SpreadsheetReader.php');

    if (isset($_POST["import"]))
        {     
        $allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];

            if(in_array($_FILES["file"]["type"],$allowedFileType)){
                $targetPath = 'uploads/'.$_FILES['file']['name'];
                move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

                $Reader = new SpreadsheetReader($targetPath);

                $sheetCount = count($Reader->sheets());
                    for($i=0;$i<$sheetCount;$i++)
                        {                                        
                            $Reader->ChangeSheet($i);

                                foreach ($Reader as $Row)
                                    {                                    
                                        $po = "";
                                        if(isset($Row[0])) {
                                            $po = mysqli_real_escape_string($conn,$Row[0]);
                                        }
                                        $business = "";
                                        if(isset($Row[1])) {
                                            $business = mysqli_real_escape_string($conn,$Row[1]);                                                
                                        }                                            
                                        $model = "";
                                        if(isset($Row[2])) {
                                            $model = mysqli_real_escape_string($conn,$Row[2]);                                                
                                        }
                                        $serial = "";
                                        if(isset($Row[3])) {
                                            $serial = mysqli_real_escape_string($conn,$Row[3]);
                                        }
                                        $freight = "";
                                        if(isset($Row[4])) {
                                            $freight = mysqli_real_escape_string($conn,$Row[4]);
                                        }
                                        $depreciation = "";
                                        if(isset($Row[5])) {
                                            $depreciation = mysqli_real_escape_string($conn,$Row[5]);
                                        }
                                        $date_rec = "";
                                        if(isset($Row[6])) {
                                            $date_rec = mysqli_real_escape_string($conn,$Row[6]);
                                        }

                                        $raw_results = mysqli_query($conn,"SELECT serial FROM device_current_info
                                            WHERE (`serial` = $serial)");

                                            $results = mysqli_fetch_array($conn,$raw_results);

                                                if($results > 0){
                                                    $type = "error";
                                                    $message = "Problem in Importing assets into the Database" .mysqli_error($conn);
                                                }
                                                else{
                                                        if (!empty($po) || !empty($model) || !empty($serial) || !empty($freight) || !empty($depreciation)|| !empty($date_rec) ) {
                                                            //Adds assets to the terminal_info table. 
                                                            $query = "insert IGNORE into device_info(po,business,model,serial,freight,depreciation,date_rec) values('".$po."','".$business."','".$model."'
                                                            ,'".$serial."','".$freight."','".$depreciation."','".$date_rec."')";
                                                            $result = mysqli_query($conn, $query)or die(mysqli_error($conn));

                                                            if (! empty($result)) {
                                                                $type = "success";
                                                                $message = "Assets added into the Database";
                                                            } else {
                                                                $type = "error";
                                                                $message = "Problem in Importing assets into the Database" .mysqli_error($conn);
                                                            }
                                                        }
                                                        if (!empty($po) || !empty($model) || !empty($serial) || !empty($freight) || !empty($depreciation)|| !empty($date_rec) ) {


                                                            //Adds terminals to the terminal_current_info table. Terminals will be linked on this table and only current info will be stored.
                                                            $currenLocation ="Stores"; //Default location for all new assets.
                                                            $newComment ="New asset"; //Default comments for all new assets.
                                                            $currentStatus = "Available";//Default status for all new assets.
                                                            $query2 = "insert IGNORE into device_current_info(business,model,serial,current_status,current_location,comments) values('".$business."','".$model."'
                                                            ,'".$serial."','".$currentStatus."','".$currenLocation."','".$newComment."')";
                                                            $result2 = mysqli_query($conn, $query2) or die(mysqli_error($conn));

                                                            if (! empty($result)) {
                                                                $type = "success";
                                                                $message = "Assets added into the Database";
                                                            } else {
                                                                $type = "error";
                                                                $message = "Problem in Importing assets into the Database" .mysqli_error($conn);
                                                            }
                                                        }
                                                        if (!empty($po) || !empty($model) || !empty($serial) || !empty($freight) || !empty($depreciation)|| !empty($date_rec) ) {


                                                            //Creates first terminal movement. Every time a terminal is moved it this table will be updated.
                                                            $user = $_SESSION['login_user'];
                                                            $previousLocation ="Vendor"; //Default previoius location for all new assets.
                                                            $movementLocation ="Stores"; //Default location for all new assets.
                                                            $movementComment ="New asset"; //Default comments for all new assets.
                                                            $movementStatus ="Available"; //Default status for all new assets.
                                                            $query3 = "insert IGNORE into device_movements(serial,previous_location,movement_location,user,status,comments) values(
                                                            '".$serial."','".$previousLocation."','".$movementLocation."','".$user."','".$movementStatus."','".$movementComment."')";
                                                            $result3 = mysqli_query($conn, $query3) or die(mysqli_error($conn));

                                                            $query4 = "insert into activity_log(user_name,activity,old_value,new_value) values(
                                                                '".$user."','Added','','".$serial."')";
                                                                $result4 = mysqli_query($conn, $query4) or die(mysqli_error($conn));


                                                            if (! empty($result)) {
                                                                $type = "success";
                                                                $message = "Assets added into the Database";
                                                            } else {
                                                                $type = "error";
                                                                $message = "Problem in Importing assets into the Database" .mysqli_error($conn);
                                                            }
                                                        }

                                                    }


                                            }

                                        }


                                    }


                        }
                        else
                        { 
                                $type = "error";
                                $message = "Invalid File Type. Upload Excel File.";
                        }

?>

Answer 1

There is a function within PHP called Gearman that allows you to wrap these sorts of tasks into a parallel thread, so they can run alongside other tasks.

Maybe, you could look into incorporating this feature around your importer, since the code looks sound on its own! The issue here is running another task parallel, right?

Answer 2

Maybe you gain speed, if you use the SpreadSheet-Plugin to transfer Data into a csv File. You might then import the csv File with mysql with LOAD DATA LOCAL INFILE. (See MySQL Doku)

This MySQL Function ist really, really (yes, really) fast, so the rest would depent on the speed of your Spreadsheet Plugin.

But of course, this more a new way than a solution.