1

I have the following code:

$my_result = db_query("SELECT nid FROM content_field_taxo_listing WHERE field_taxo_listing_value = '%s'", $tid);

while ($my_row = db_fetch_array($my_result)) {
  $nodeids[] = $my_row['nid'];
}

What I want to do is to join (it might be a union, but I am not sure) the "content_field_taxo_listing" table with the "node" table, and order the "nids" field by title in the "node" table.

What is the Drupal way for doing this?

1

Try this one:

$my_result = db_query("
  SELECT ctl.nid 
  FROM {content_field_taxo_listing} ctl 
  LEFT JOIN {node} n ON ctl.nid = n.nid
  WHERE ctl.field_taxo_listing_value = :tid 
  ORDER BY n.title ASC",
  array(":tid"=>$tid)
);
  • What does ctl do? Never seen that before. The code from nourcy works perfectly. – user4560 Dec 23 '11 at 10:29
  • 1
    @user4560 - It's just an alias for the actual tablename. Have a look at dev.mysql.com/doc/refman/5.0/en/select.html – Cyclonecode Dec 23 '11 at 10:37
  • Tried reading the link, however my brain got fried! Do you really read stuff like this? – user4560 Dec 23 '11 at 23:33
  • @user4560 - Haha, yes sometimes=) – Cyclonecode Dec 23 '11 at 23:47
1
$my_result = db_query("
  SELECT {content_field_taxo_listing}.nid 
  FROM {content_field_taxo_listing} LEFT JOIN {node} ON content_field_taxo_listing.nid = node.nid
  WHERE content_field_taxo_listing.field_taxo_listing_value = '%s'
  ORDER BY {node}.title", $tid);
  • Hey thanks for answer, but it still does not SORT BY title from the node table – user4560 Dec 23 '11 at 8:08
  • Sorry I forgot :) SELECT node.title, content_field_taxo_listing.nid FROM {content_field_taxo_listing} LEFT JOIN {node} ON content_field_taxo_listing.nid = node.nid WHERE content_field_taxo_listing.field_taxo_listing_value = '%s' ORDER BY node.title – nourcy Dec 23 '11 at 8:14
  • Perfect! Thank you very much, very helpful indeed merry christmas :) – user4560 Dec 23 '11 at 8:27
  • Merry Christmas :) – nourcy Dec 23 '11 at 8:48
  • @nourcy You should write what you wrote in your comment in your answer. – kiamlaluno Dec 23 '11 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.