0

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)
  • 1
    Do you receive any error after running these queries, either in Drupal Logs or in Apache, MySQL Logs? – Yogesh Dec 10 '16 at 6:30
  • No! Please see my answer below. It makes sense why no error was thrown. – Josh Ginn Dec 13 '16 at 23:33
0

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

0

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.

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.