Update query not working for db_update and db_query("update

Question

I'm trying to update a field using the Drupal's bootstrapped db_update or db_query library functions for PHP. However, neither of them are working. IF I RUN THE QUERYS MANUALLY, THEY WORK.

These two queries are the exact same. They are equivalent to doing

update field_data_field_department set delta = 4 where entity_id = 10568 and delta = 0;

Please tell me what I need to do to execute this query from PHP and make it work.

db_query("update field_data_field_department set delta = $highest_delta where entity_id = $entityID and delta = $i");

OR

            $num_affected = db_update('field_data_field_department')
              ->fields(array(
                'delta' => $highest_delta
              ))
              ->condition('entity_id', $entityID)
              ->condition('delta', $i)
              ->execute();
            echo "num_affected: $num_affected\n";

num_affected returns 1 every time.

Here is the state of the database before the query is executed.

MariaDB [db]> explain field_data_field_department;
+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| entity_type          | varchar(128)     | NO   | PRI |         |       |
| bundle               | varchar(128)     | NO   | MUL |         |       |
| deleted              | tinyint(4)       | NO   | PRI | 0       |       |
| entity_id            | int(10) unsigned | NO   | PRI | NULL    |       |
| revision_id          | int(10) unsigned | YES  | MUL | NULL    |       |
| language             | varchar(32)      | NO   | PRI |         |       |
| delta                | int(10) unsigned | NO   | PRI | NULL    |       |
| field_department_tid | int(10) unsigned | YES  | MUL | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+
8 rows in set (0.08 sec)

MariaDB [db]> select * from field_data_field_department where entity_id = 10568;
+-------------+--------+---------+-----------+-------------+----------+-------+----------------------+
| entity_type | bundle | deleted | entity_id | revision_id | language | delta | field_department_tid |
+-------------+--------+---------+-----------+-------------+----------+-------+----------------------+
| node        | person |       0 |     10568 |       25093 | und      |     0 |                   39 |
| node        | person |       0 |     10568 |       25093 | und      |     1 |                   30 |
| node        | person |       0 |     10568 |       25093 | und      |     2 |                   41 |
| node        | person |       0 |     10568 |       25093 | und      |     3 |                   38 |
+-------------+--------+---------+-----------+-------------+----------+-------+----------------------+
4 rows in set (0.00 sec)

Answer 1

Try something like this, It should work.

$num_affected = db_query("UPDATE {field_data_field_department}
SET delta=:highest_delta
WHERE entity_id=:entity_id AND delta=:delta
", array(":highest_delta" => $highest_delta,":entity_id" => $entityID,":delta" => $delta))->fetchAll();

Answer 2

The queries were written correctly. The problem was that there was a $node = node_load($entity_id) before the queries and that there was a node_save($node) after the queries. The node save overwrote the changes that the queries had made to the database.

$node = node_load($entity_id);
...
db_query("update field_data_field_department set delta = $highest_delta where entity_id = $entityID and delta = $i");
...
node_save($node)

When $node = node_load($entity_id) is called, the tables from Drupal pertaining to that entity are stored in an array in $node. As the php programmer changes items in $node (or node_wrapper) they are changed in the array. When node_save($node) is called, those array items are sent to the database so that the database will now match what was stored in the array $node.

To solve the problem, we moved node_save($node) before the queries so that our db_query("update...") changes wouldn't get overwritten.

Corrected Solution:

$node = node_load($entity_id);
...
node_save($node)
...
db_query("update field_data_field_department set delta = $highest_delta where entity_id = $entityID and delta = $i");
...

Don't call node_save($node) again unless you update $node using $node = node_load($entity_id) again to get a new copy of the database.