Hi,
I have 2 content types: one is project the other is task.
Both contain a cck-Field "status"
If the status field in "project" is changed, I also need to change it to the same value in the tasks.

Task contains a nodereference to project.

What I have in database:

table node
table content_field_projectlink // created by cck-field in task as nodereference to project
--> field field_projectlink_nid // field in this table containing the nid of the referenced project

table content_type_task // table containing all text-CCK-fields of content-type task
--> field field_status // field in task which has to be updated

I created a new rule (conditions: content type project is updated + field status has changed)
Action: php code 

$var_new_service = '[node:field_status-raw]'; // value of status in updated project
$var_projectid = [node:nid]; // nid of current updated project

$sql = "
  SELECT n.nid
  FROM {node} AS n
  INNER JOIN {content_field_projectlink} AS c ON n.vid = c.vid
  INNER JOIN {content_type_task} AS d ON n.vid = d.vid
  WHERE c.field_projectlink_nid = %d";
$var_list = db_query($sql, $var_projectid);

$result = array();

$result = db_query("UPDATE $var_list SET d.field_ueberwachungbezug_value = %s, $var_service); 

return $result;

This does not work. I allways receive the error

Parse error: syntax error, unexpected $end in /var/www/web1/web/partner/sites/all/modules/rules/rules/modules/php.rules.inc(107) : eval()'d code on line 16

Can anyone help me? I don't know how to perform the update correct. Maybe my innerjons are wrong?? Do I hav to include the fields in the sql-select?? Or is the $result wrong?

Kind regards

vistree

Comments

vistree’s picture

vistree commented
Title: How to use rules to update field directly in database » Tried in mysql - works - but not with rules
Component: Rules Core » Rules Engine

Hi, I tried out a lot now - but I can't perform a database update from rules.
Found out the correct sql, which works fine in mysql itself. Tried to modify it in a drupal way - no errors but also no database updates.

What is wrong here?

$var_new_service = '[node:field_service-raw]'; 
$var_projectid = [node:nid]; 
$result = array();
$result = db_query("UPDATE {content_type_nwbehandlungsdoku} AS n, {content_field_anfragebezug} AS c SET n.field_ueberwachungbezug_value = '%s' WHERE c.vid = n.vid AND c.field_anfragebezug_nid = %d, $var_new_service, $var_projectid");
return $result;
vistree’s picture

vistree commented
Status: Active » Fixed

Hi, I found the problems on my own:
The correct working code in the Rules-custom-PHP has to be the following:


$var_new_service = '[node:field_service-raw]'; // us a string, so quotes are needed
$var_projektid = [node:nid];
$result = db_query("UPDATE {content_type_nwbehandlungsdoku} AS n
INNER JOIN {content_field_anfragebezug} AS c ON n.vid = c.vid
SET n.field_ueberwachungsbezug_value = '%s'
WHERE c.field_anfragebezug_nid = %d
", $var_new_service, $var_projektid);
return $result;

Have a nice evening

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.