Single value “db_query”: How to SELECT value that related to the node

Question

From this page, if what we need is SELECT single value, we can use fetchField method like below:

$nbr_records = db_query("SELECT count(nid) FROM {joke}")->fetchField();

I've created single value Integer field type with machine name field_subscription_period attached to node type yearly_rent.

I'm using this in Rules Action.

I've tested many db_query example with no luck.

Below is what I'm using, which it will get value of entity_id 2:

$result = db_query("SELECT field_subscription_period_value FROM {field_data_field_subscription_period} WHERE entity_id = 2")->fetchField();

Above query will get value of entity_id 2... but I want to SELECT field_subscription_period value that related to the node. So, how to get value that related to the node?

-newly added-

I still don't understand how to use $node = node_load($nid);. Lets say I want to use the value as variable to be calculated in date field.

I've tried below with no luck:

$node = node_load($nid);
$values = field_get_items('node', $node, 'field_subscription_period');
$newdate = strtotime("+$values year","$value");
return $newdate;

..and also I got error:

Notice: Undefined variable: nid in eval() (line 1 of /var/www/testd7/sites/all/modules/rules/modules/php.eval.inc(146) : eval()'d code).
EntityMalformedException: Missing bundle property on entity of type node. in entity_extract_ids() (line 7539 of /var/www/testd7/includes/common.inc).

Answer 1

You should not use direct queries to the database tables used for fields, for the simple fact the fields could not use database tables to store their value; this is what happens by default, but if a different storage implementation is exposed by a module, the information will be saved in another place. That is actually what happens with the Mongodb module.

If you know the node ID, and you want to get the value of a field associated to it, you can use the following code.

$node = node_load($nid);
$values = field_get_items('node', $node, 'field_subscription_period');

$values is an array whose structure depends from the field. It contains at least an item, but the exact number depends from how many values the field can have. (It is normally set in the setting page for the field.)
To see the structure of that array, you should install the Devel module, and use test code similar to the following one.

$node = node_load($nid);
$values = field_get_items('node', $node, 'field_subscription_period');
dsm($values);

For example, the first screenshot shows the structure used for a node body, while the second one shows the structure used for an image field.

If you need to find all the nodes of a specific content type, and filter out the list basing on the value set for a field, you should use the EntityFieldQuery class, as in the following code.

$query = new EntityFieldQuery();

$query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'yearly_rent')
  ->propertyCondition('status', 1)
  ->fieldCondition('field_subscription_period', 'value', $minimum_value, '>')
  ->addMetaData('account', user_load(1)); // Run the query as user 1.

$result = $query->execute();

if (!empty($result['node'])) {
  $nodes = node_load_multiple(array_keys($result['node']));
}

addMetaData('account', user_load(1)) is necessary because the EntityFieldQuery class respects the access permission associated with fields; if the current user doesn't have access to the field, fieldCondition() would fail.
Once you have the node object, you can use field_get_items(). If you want to obtain a value to render, you should call field_view_field().