I want to select all the nodes from the database where a specific taxonomy term is defined (in Drupal 7).
I already know the taxonomy term (IDs). So I want to use a join to get the latest node thats created for that taxonomy.
For the moment I have this:
$query = db_select("node","n");
$query->join('taxonomy_index', 'i', 'n.nid = i.nid');
$query->fields("n",array('nid','title','created'))
->orderby("created","desc")
->range(0,1);
drupal_set_message($id);
$query->where('(i.tid = :type)', array(':type' => $id));
drupal_set_message($query);
$query->execute();
$array = array();
foreach ($query as $record) {
$array[] = $record;
}
return $array;
This is (some of ) the output of the messages:
Id: 1
SELECT n.nid AS nid, n.title AS title, n.created AS created FROM {node} n INNER JOIN {taxonomy_index} i ON n.nid = i.nid WHERE ((i.tid = :type)) ORDER BY created DESC LIMIT 1 OFFSET 0
If I use the following string directly in PHPMYADMIN, I have results:
SELECT n.nid AS nid, n.title AS title, n.created AS created FROM drpl_node n INNER JOIN drpl_taxonomy_index i ON n.nid = i.nid WHERE ((i.tid = 1)) ORDER BY created DESC LIMIT 1 OFFSET 0
The result directly in Drupal returns an empty array.. What am I doing wrong?