How can i get the value from the Dynamic query

Question

$query=db_select('node','n');
$query->join('field_data_field_cmp_playerid','b','n.nid=b.entity_id');
$query->join('field_data_field_cmp_id','c','n.nid=c.entity_id');
$query->join('field_data_field_cmp_userscore','d','n.nid=d.entity_id');
$query->join('field_data_field_cmp_usersupport','e','n.nid=e.entity_id');
$query->join('field_data_field_cmp_status','f','f.entity_id=c.field_cmp_id_nid');
$query->join('field_data_field_cmp_attend_time','g',' g.entity_id = n.nid');
$query->join('field_data_field_cmp_end_date','h',' h.entity_id = c.field_cmp_id_nid');
$query->fields('n',array('title','nid'),'d',array('field_cmp_userscore_value'),'e',array('field_cmp_usersupport_value'),'f',array('feld_cmp_status_tid'),'g',array('.field_cmp_attend_time_value'),'h',array('field_cmp_end_date_value'));
$query->condition('b.field_cmp_playerid_uid',$userid,'=');
$query->condition('n.type','Campaign_PlayerList','=');
$result=$query->execute()->fetchAll();

i use foreach loop to get the value of the query

foreach($result as $value)
{
    drupal_set_message(t('The variable is %va',array('%va'=>$value->title)));
    drupal_set_message(t('The variable is %va1',array('%va2'=>$value->field_cmp_userscore_value)));
    drupal_set_message(t('The variable is %va2',array('%va2'=>$value->field_cmp_end_date_value)));
}

the value i get is "title" and i can't get the field_cmp_userscore_value and field_cmp_end_date_value value.

what could the problem be?

Thank you

Answer 1

The fields() member function of a SelectQuery class only take two arguments: An alias and an array of fields. At the moment you're trying to add the fields from all tables in one go, when you need to split them up into separate calls to fields():

$query->fields('n',array('title','nid'));
$query->fields('d',array('field_cmp_userscore_value'));
$query->fields('e',array('field_cmp_usersupport_value'));
$query->fields('f',array('field_cmp_status_tid'));
$query->fields('g',array('field_cmp_attend_time_value'));
$query->fields('h',array('field_cmp_end_date_value'));

There are also a couple of typos in your fields names:

1. .field_cmp_attend_time_value should be field_cmp_attend_time_value
2. feld_cmp_status_tid should be field_cmp_status_id

Answer 2

The problem is that the following line is wrong:

$query->fields('n',array('title','nid'),'d',array('field_cmp_userscore_value'),'e',array('field_cmp_usersupport_value'),'f',array('feld_cmp_status_tid'),'g',array('.field_cmp_attend_time_value'),'h',array('field_cmp_end_date_value'));

You need to call fields() separately for each table. Right now, the additional arguments are ignored.

Also note that it is strongly recommended to use EntityFieldQuery to query field tables, that however has the limitation that you need to load the node object to get the field values.