How to use mysql queries in Action in a Triggered Rule?

Question

I created a triggered rule. The action will 'fill a field'. To get the value for this field I need to get a value saved in sql database. Then I check to set the value using php and create this code:

$result = mysql_query("SELECT field_codigo_value FROM content_type_inmuebles_en_venta SORT BY field_codigo_value LIMIT 1");
$value = mysql_fetch_array($result);

return array(
  0 => array('value' => $value )
); 

But I get this error message:

warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /Applications/MAMP/htdocs/drupal6multilingual/sites/all/modules/rules/rules/modules/php.rules.inc(107) : eval()'d code on line 2.

Answer 1

First off, you should be using the drupal SQL coding conventions:

db_query instead of mysql_query
db_fetch_array instead of mysql_fetch_array

I would structure the query like so:

$query = "SELECT field_codigo_value
          FROM content_type_inmuebles_en_venta 
          SORT BY field_codigo_value 
          LIMIT 1";

$result = db_query($query);

return $result; 

You should know that triggered rules allows you to pull a value out of the database without writing a custom query. We need a bit more context as to how this node is related to your triggered rule, but I would guess that you can load the node that you just created or load a referenced node to the node that you just created and then use the available tokens to fill in the CCK field that you need filled in.