php - Mysql 将多个插入语句转换为一个

Question

我有一个脚本来上传 excel 文件并将数据从 xlsx 文件插入到 mysql 表中。是这样的

<?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";
}



?>

这里对 excel 中的每一行执行一个插入语句。然后我使用迭代变量作为插入的记录数发送响应。有两个问题，一个，我想使用一个插入语句，它可以用于插入 excel 中的所有行。第二个问题是使用迭代变量值作为否。记录可能是一个问题，因为如果数据中有任何错误，查询可能不会执行。有人可以建议解决这个问题吗？

Answer 1

创建一个语句:

$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);

要获得插入的记录的实际数量(我从 here 复制示例并更改它):

$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();
?>