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

Question

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?

Nothing wrong with NOW(). If $id is constant and doesn't come from user input, you might as well hard-code it into the query too — Phil
– Phil, Commented Jun 4, 2014 at 4:28
@Phil NOW() will insert different values very likely. To make sure, one should provide it as a variable as well. Oh. and this thing with "user input" and hardcode. No variable should be ever hardcoded in a query. Why not to learn this simple rule once for all? — Your Common Sense
– Your Common Sense, Commented Jun 4, 2014 at 6:08
@YourCommonSense That's true regarding NOW() but it depends on what value OP actually wants. If they want it to reflect the time when the record was inserted, then NOW() is accurate. As for hard-coding, I meant if OP had something like $id = 1;. Might as well have VALUES (1, ...) — Phil
– Phil, Commented Jun 4, 2014 at 6:13

Phil · Accepted Answer · 2014-06-04 06:06:40Z

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

edited Jun 4, 2014 at 6:06

answered Jun 4, 2014 at 4:30

Phil

166k25 gold badges265 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Wombat Over a year ago

Thanks. I tried this - it still only inserts one row. $id comes from a SELECT query (selecting the user id from another table) on the same page, before the code snippet included above.

Phil Over a year ago

@Wombat Have you tried debugging $_POST['alt_ord'] as that is the key to the number of loop iterations?

Wombat Over a year ago

$_POST['alt_ord'] has been set to always be (1, 2, 3, 4). I realise this might seem redundant, but it's helpful for data analysis further down the track. The multiple-row insert worked until I added $id and NOW().

Phil Over a year ago

@Wombat You checking for errors (see updated answer)?

Wombat Over a year ago

I now get this error after adding your updated code: "PHP Fatal error: Uncaught exception 'Exception' with message 'Duplicate entry '14' for key 'PRIMARY''". So I think I understand - the table should have a separate primary key with unique values. In my case, id has defaulted to the primary key.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related