Strange timing using "Content is Updated" [#269870]

Gang,

I'm using custom php for everything in ng now. I've got my conditions straight, but here's a problem:
1. When a particular node is updated, if the user of type x has selected > 1 locations, I want to delete all the locations that don't belong to said user.
2. Rule - "Content is Updated"
3. Conditions = all good. Thoroughly tested!
4. custom php action:
db_query("DELETE FROM content_field_location_0 WHERE nid = '%n' AND field_location_0_nid NOT IN (SELECT value FROM profile_values WHERE uid = '%u')",$node->nid,$user->uid);

Now this runs just fine when content is updated; no errors. But when I look at the content_field_location AFTER an update has been submitted, there has been no deletion!

I've double-checked to make sure my SQL is good: when I insert real values and run it, it deletes the items from the table.

When does ng run, relative to the update? I think there could be a timing issue here.

Any diagnosis or workarounds (or heck, corrections to SQL) GREATLY appreciated!!

Comments

Comment #1

cyu commented 12 June 2008 at 17:20

http://api.drupal.org/api/function/db_query/5

%n and %u are not valid %-modifiers. In this case I believe you want to use %d...and you also shouldn't need the single quotes around them. I think that might be the root of your problem.

Comment #2

amitaibu

Hebrew

Israel

commented 12 June 2008 at 17:27

Status:

Active

» Postponed (maintainer needs more info)

also don't forget to write your db inside brackets {db_name}

Comment #3

ifbyphone1 commented 12 June 2008 at 18:40

Amitaibu & CYU: THANKS for the tips! BUT I regret....

1. This cleaner version didn't work:
db_query("DELETE FROM {content_field_location_0} WHERE nid = %d AND field_location_0_nid != (SELECT value FROM {profile_values} WHERE uid = %d)",$node->nid,$user->uid);

2. This change in query format didn't either:
db_query("DELETE FROM {content_field_location_0} WHERE nid = %d AND field_location_0_nid NOT IN (SELECT value FROM {profile_values} WHERE uid = %d)",$node->nid,$user->uid);

3. I tried %s instead and tried one instance each of %s and %d (can't find what those signify; my PHP is soooo rusty).

If you want a minimally invasive way to test syntax similarly at home, you could use:
db_query("DELETE FROM {cache} WHERE cid = %d AND expire NOT IN (SELECT aid FROM {accesslog} WHERE uid = %d)",$node->nid,$user->uid);
AND
replace $node->nid with 'variables'?

Appreciate any other tips / tests / etc you can offer -- many thanks!!!

Comment #4

cyu commented 12 June 2008 at 18:58

tc60045,
You can get the definitions of those %-modifiers here...might be nice for these to be on the api page as well
http://php.net/manual/en/function.sprintf.php

Not sure why things still aren't working for you, but it looks like this is probably not related to Workflow-ng....right? If you have the devel module installed you can switch db_query() to db_queryd() and see exactly what query is being run and then try running that directly in mysql to see what is going wrong.