How can I save arrays of values in the database?

Question

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?

Answer 1

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

• Insert queries

Answer 2

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