8

I am trying to save multiple values from a textarea to a database table.

I use the following code, but I feel it is the wrong way.

foreach ($user_emails as $key => $value) {
  $insert_banned_emails = db_insert('banned_users');
  $insert_banned_emails
    ->fields(array(
      'email' => $value,
    ))
    ->execute();
}

Is there a different way for achieving the same result?

15

I would use the following code.

foreach ($user_emails as $value) {
  $query = db_insert('banned_users');
  $query->fields(array('email' => $value))->execute();
}

Alternatively, you could use the following code.

$query = db_insert('banned_users')->fields(array('email'));

foreach ($user_emails as $value) {
  $query->values(array('email' => $value));
}

$query->execute();

With MySQL, the query uses the multi-value syntax.

With other databases, the executed queries will be one for each call to $query->values(), wrapped in a transaction. This means the queries will be rolled back when one of them fails. In fact, the code executed from InsertQuery::execute() is the following one.

  // Each insert happens in its own query in the degenerate case. However,
  // we wrap it in a transaction so that it is atomic where possible. On many
  // databases, such as SQLite, this is also a notable performance boost.
  $transaction = $this->connection->startTransaction();

  try {
    $sql = (string) $this;
    foreach ($this->insertValues as $insert_values) {
      $last_insert_id = $this->connection->query($sql, $insert_values, $this->queryOptions);
    }
  }
  catch (Exception $e) {
    // One of the INSERTs failed, rollback the whole batch.
    $transaction->rollback();
    // Rethrow the exception for the calling code.
    throw $e;
  }

In short, I would use the code you are using if the inserted values are independent from each other; I would use the code I shown when the values depend from each other.

In your case, the emails are independent from each other. If you would use the second snippet I shown, the database table will contains all the values, when the sub-query don't fail, or none when a single sub-query fails.

You could also use drupal_write_record(), even though I much prefer the other snippets.

foreach ($user_emails as $value) {
  drupal_write_record('banned_users', array('email' => $value));
}

I don't see any pro in using this snippet, though.

Reference

  • 1
    I'm sorry what about the "Multi-Insert Form" shown on this documentation page. Give 1 array of $values and only call 1 execute(). drupal.org/node/310079 This is used for instance in the standard profiles default blocks creation. – tenken Dec 16 '12 at 0:47
  • 2
    'Tis true, you can call ->values(...) as many times as you want on an InsertQuery and it will prepare a query like INSERT INTO x (field1, field2) VALUES ('val1', 'val2'), ('val3', 'val4'), etc – Clive Dec 16 '12 at 1:18
  • 2
    Alright, now I remembered why I discarded using $query->values(): In most of my cases, the values my code is inserting are independent from each other, and I don't want that an error with a value causes a rollback of the other values. – kiamlaluno Dec 16 '12 at 5:12
3

This is a similar version to your code, but better performance. You really don't want to call execute() a thousand times, you only need to call it once.

Reference

$insert_banned_emails = db_insert('banned_users')->fields(array('email'));
foreach ($user_emails as $key => $value) {
  $insert_banned_emails->values(array(
    'email' => $value,
  ));               
}
$insert_banned_emails->execute();
  • Worked for me except that it would throw OOM errors when the number of lines is too large (10,000) in my case. So I split that into batches of 1000 or less to solve that problem. – Eduardo Chongkan Oct 5 '16 at 4:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.