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, Jun 04 '14 at 04: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, Jun 04 '14 at 06: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, Jun 04 '14 at 06:13

Phil · Accepted Answer · 2014-06-04T06:06:40.167

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 04 '14 at 06:06

answered Jun 04 '14 at 04:30

Phil

157,677
23
242
245

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. – Wombat Jun 04 '14 at 04:51
@Wombat Have you tried debugging `$_POST['alt_ord']` as that is the key to the number of loop iterations? – Phil Jun 04 '14 at 04:56
`$_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()`. – Wombat Jun 04 '14 at 05:10
@Wombat You checking for errors (see updated answer)? – Phil Jun 04 '14 at 05:14
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](http://stackoverflow.com/a/840182/3588207). In my case, `id` has defaulted to the primary key. – Wombat Jun 04 '14 at 05:52
I altered the table as per my last comment and that solved the problem. Thanks for your help :-) – Wombat Jun 04 '14 at 06:04

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

1 Answers1

Linked