1

I have a script to upoad an excel file and insert data from the xlsx file to a mysql table . It is like this 

<?php
require_once('Connections/met.php');
$file = './uploads/windrose_data.xlsx'; 


if (move_uploaded_file($_FILES['uploadfile']['tmp_name'], $file)) { 

$msg="File upload successful";
$db=mysql_select_db($database_met,$met);
set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
include 'PHPExcel/IOFactory.php';

// This is the file path to be uploaded.
$inputFileName = $file; 

try {
    $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}


$allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
$arrayCount = count($allDataInSheet);  // Here get total count of row in that Excel sheet


for($i=2;$i<=$arrayCount;$i++){
$date = trim($allDataInSheet[$i]["A"]);
$time = trim($allDataInSheet[$i]["B"]);
$dir = trim($allDataInSheet[$i]["C"]);
$spd = trim($allDataInSheet[$i]["D"]);




$insertTable= mysql_query("insert into wr_copy (date,time,dir,spd) values('$date', '$time',$dir,$spd)") or die(mysql_error());



$msg=$i-1." records inserted into the table";



}







  echo $msg; 
} else {
    echo "Upload Failed";
}



?>

here for each row in excel one insert statement is executed., then I am sending a response using the iteration variable as the number of records inserted. There are two issues, one, I want to use a single insert statement which can be used for inserting all the rows in excel. second issue is using iterating variable values as no. of records can be a problem because, the query may not execute if there is any error in data. Can anybody suggest a work around for this?

mansoondreamz
  • 493
  • 1
  • 4
  • 25
  • See : http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql – PaulF Jun 23 '15 at 09:16
  • 1
    As an aside, have a look into switching from `mysql_*` to `mysqli` or `PDO`. `mysql_*` is deprecated. Also, have a look into using prepared statements instead of string concatenation – Kevin Nagurski Jun 23 '15 at 09:19

1 Answers1

0

For creating the one statement:

$statement = 'insert into wr_copy (date,time,dir,spd) values';
$values = [];
for($i=2;$i<=$arrayCount;$i++){
  $date = trim($allDataInSheet[$i]["A"]);
  $time = trim($allDataInSheet[$i]["B"]);
  $dir = trim($allDataInSheet[$i]["C"]);
  $spd = trim($allDataInSheet[$i]["D"]);
  $values[] = "('$date', '$time',$dir,$spd)";
}

$statement .= implode(',',$values);

To get the real numbers of records that are inserted (i copied the example from here and change it):

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}


/* prepare statement */
if ($stmt = $mysqli->prepare($statement)) {

    /* execute statement */
    $stmt->execute();

    printf("rows inserted: %d\n", $stmt->affected_rows);

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();
?>
codeneuss
  • 905
  • 4
  • 12