PHP/mysql: mysqli prepared statement insert same v

问题:

I am using a prepared statement to insert multiple rows into a table using a for loop. What I require is for the same value ($id) to be inserted into all rows of the "id" column. Likewise, the timestamp should be inserted into the "submitted" column over all iterations.

My current code only inserts one column. Here is the code:

 if($stmt = $link->prepare("INSERT INTO table (id, alt_ord, alt_id, rank, submitted) VALUES ($id,?,?,?, NOW())")){
      $stmt->bind_param('iii', $q_ord, $q_ID, $rating);

      for($i=0; $i < count($_POST['alt_ord']); $i++){
           $q_ord = $_POST['alt_ord'][$i];
           $q_ID = $_POST['alt_id'][$i];
           $rating = $_POST['rank_'][$i];
           $stmt->execute();
      }
      $stmt->close();
 }

Using a combination of ?s with $id and NOW() in the INSERT statement is clearly incorrect. How would I repeat the ID and timestamp values in the insert as intended?

回答1:

Assuming $id is an unknown value (from user input, etc), simply bind it along with the others and don't forget to check for errors

// make mysqli trigger useful errors (exceptions)
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$stmt = $link->prepare('INSERT INTO table (id, alt_ord, alt_id, rank, submitted) VALUES (?, ?, ?, ?, NOW())');
$stmt->bind_param('iiii', $id, $q_ord, $q_ID, $rating);

for ($i = 0; $i < count($_POST['alt_ord']); $i++) {
    $q_ord = $_POST['alt_ord'][$i];
    $q_ID = $_POST['alt_id'][$i];
    $rating = $_POST['rank_'][$i]; // you sure about this one? "rank_"?

    $stmt->execute();
}