CODEWITHSUNDEEP
phpmysqlsqlmysqliprepared-statement
Wombat
Wombat

Reputation: 3

PHP/mysql: mysqli prepared statement insert same value multiple times within for loop

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?

Upvotes: 0

Views: 1294

Answers (1)

Phil
Phil

Reputation: 164798

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();
}

Upvotes: 1

Related Questions