Drupal 7 db_update with foreach loop

Question

I have Drupal 7 site with a table events_times. I need to check to see if any of the records exist in the database. If they do I want to do an update not an insert. I have been tinkering with the code but cannot get it working. I am getting the following error:

Fatal error: Call to undefined method UpdateQuery::values()

Here is my code:

$times_values= array();

foreach ($form_state['values']['times_fieldset']['registration_times'] as   $time) {
$times_values[] = array('event_id' => $eventId,
'registration_times' => $time,
'max_attendees' => 0
);
}
//Check to see if values exist in the table already
$exists = db_select('events_times','et')
->fields('et')
->condition('event_id',$event->id,'=')
->execute()
->fetchAll();

if ($exists == FALSE) {
$query = db_insert('events_times')->fields(array('event_id', 'registration_times', 'max_attendees'));
foreach ($times_values as $record) {
$query->values($record);
}
$query->execute();
}
else {
 $query = db_update('events_times')->fields(array('event_id', 'registration_times', 'max_attendees'));
foreach ($times_values as $record) {
$query->values($record);
}
$query->execute();
}

acrosman · Accepted Answer · 2016-05-06T16:13:19.577

1

Drupal's db_update() and db_insert() use the fields() method to define where your values should end up. To support multiple insert the insert object and also accept values through the values() method.

$query->fields(array(
  'registration_times' => $values['registration_times'],
);

edited May 06 '16 at 16:13

answered May 06 '16 at 16:04

acrosman

12,814
10
39
55

That is being set in my $times_values = array(); block? Insert worked prior to adding the call to check db and db_update statements – Johanna May 06 '16 at 16:08
I should clarify that the registration_times field is an ajax add/remove form object so it inserts multiple records at once in the db. – Johanna May 06 '16 at 16:16
You're right, insert also accepts values() as a method call (answer updated). But the update object does not have a values method (see: https://api.drupal.org/api/drupal/includes!database!query.inc/class/UpdateQuery/7.x), so yes you need to replace the fields in the loop (you may have to also replace the query object I haven't tried an update call in a loop recently. – acrosman May 06 '16 at 16:16
Ah ok, I get you. I will keep digging :) – Johanna May 06 '16 at 16:17
This talks about the code I am using... but it doesn't work http://stackoverflow.com/questions/25646525/drupal-7-update-multiple-rows-using-db-update – Johanna May 06 '16 at 16:19
For now I am going to just to a delete and re-add the data on insert for any changes. – Johanna May 06 '16 at 16:26
I'm pretty sure the answer on that question is just wrong. If you're updating multiple rows to the same value you can add conditions in the loop and execute once. If you need to update multiple rows to multiple values you need to run multiple update queries. – acrosman May 06 '16 at 16:26

Drupal 7 db_update with foreach loop

1 Answers1