使用 mysqli 时，这种插入方法是否正确 - Is this method of insert correct when using mysqli

我只想知道，在使用mysqli将数据插入数据库时，这是下面最好的方法还是有更好的方法？

 $insert = array();
  for ($i = 1, $n = $_SESSION['sessionNum']; $i <= $n; ++$i)
  {
    $insert[] = "'". mysqli_real_escape_string( $_SESSION['id'] ) . ($n == 1 ? '' : $i) . "','". mysqli_real_escape_string( $_SESSION['timeChosen'] ) ."','". mysqli_real_escape_string( date("Y-m-d", strtotime( $_SESSION['dateChosen'] ) ) ) ."'
,'". mysqli_real_escape_string( $_SESSION['textWeight'] ) ."','". mysqli_real_escape_string( $time ) ."','". mysqli_real_escape_string( $_SESSION['textMarks'] ) ."'
,'". mysqli_real_escape_string( $_SESSION['module'] ) ."','". mysqli_real_escape_string( $teacherid ) ."','". mysqli_real_escape_string( $_SESSION['rooms'] ) ."'";
  }
  $insertsql = "INSERT INTO Session (SessionId, SessionTime, SessionDate, SessionWeight, SessionDuration, TotalMarks, ModuleId, TeacherId, Room) 
  VALUES (" . implode('), (', $insert) . ")";
 $sqlstmt=$mysqli->prepare($insertsql);
 $sqlstmt->execute();

使用预准备语句的全部意义在于避免完成您正在做的一半工作。基本思想是，您只需创建基本查询，准备它并告诉它您将使用的变量名称而不是占位符，然后对变量执行所有工作。

您的代码应如下所示：

$insertsql = "INSERT INTO Session (SessionId, SessionTime, SessionDate, SessionWeight, SessionDuration, TotalMarks, ModuleId, TeacherId, Room) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
$insert = $mysqli->prepare($insertsql);
$insert->bind_params("sssssssss", $sessid, $_SESSION['timeChosen'], $sessdate,
                     $_SESSION['textWeight'], $time, $_SESSION['textMarks'],
                     $_SESSION['module'], $teacherid, $_SESSION['rooms']);
for ($i = 1, $n = $_SESSION['sessionNum']; $i <= $n; ++$i) {
    $sessid = $_SESSION['id'] . ($n == 1 ? '' : $i);
    $sessdate = date("Y-m-d", strtotime($_SESSION['dateChosen']));
    $insert->execute();
}
$insert->close(); /* free resources */

最好使用 PDO，并使用将值绑定到的预准备查询。如果要填写很多值，则可以只使用基于位置的占位符而不是基于名称的占位符。

<?php
// connect to your database
$pdo = new PDO('mysql:host=localhost;dbname=mydatabase', "username", "password");
// fill in the array, just one after the other since the placeholders will be numbered from 1 to ->count($insert)
$insert = array();
for ($i = 1, $n = $_SESSION['sessionNum']; $i <= $n; ++$i){
    $insert[] = $_SESSION['id'] . ($n == 1 ? '' : $i); 
    $insert[] = $_SESSION['timeChosen'];
    $insert[] = date("Y-m-d", strtotime( $_SESSION['dateChosen'])); 
    $insert[] = $_SESSION['textWeight']; 
    $insert[] = $time;
    $insert[] = $_SESSION['textMarks']; 
    $insert[] = $_SESSION['module']; 
    $insert[] = $teacherid;
    $insert[] = $_SESSION['rooms'];
}

$query = $pdo->prepare('INSERT INTO Session 
         (SessionId, SessionTime, SessionDate, SessionWeight, 
          SessionDuration, TotalMarks, ModuleId, TeacherId, Room) 
          VALUES' . 
          //repeat the 9 placeholders for $_SESSION['sessionNum'] and cut of the trailing ,
          substr(str_repeat('(?,?,?,?,?,?,?,?,?),',$_SESSION['sessionNum']),0,-1));
// now we bind the placeholder with the corresponding value in the array
for($i = 0;$i < count($insert);$i++){
    $query->bindParam(($i + 1),$insert[$i]);
}
// finally we execute the query
$query->execute();

真的

就是这样。（我凭记忆写了这个，还没有测试过，但对于 php -l，但你得到了大致的想法，我很确定它会起作用;)。