SQL Query to display taxonomy terms

Question

I am working on a Drupal 6 site and have very limited knowledge of writing sql queries.

I have a content type manage_current_university (which is a content profile) and in that use a taxonomy cck field field_host_university. I am generating a table of users who have signed for a content.

If I write this query in phpMyAdmin, I get the correct tid for that user

SELECT field_host_university_value FROM node n INNER JOIN content_type_manage_current_university mcu ON n.nid = mcu.nid WHERE n.uid =120;

Replicating the same query inside a module:

$currentuniversity_query = db_query("SELECT field_host_university_value FROM node n INNER JOIN content_type_manage_current_university mcu ON n.nid = mcu.nid WHERE n.uid =".$uid.""); while($currentuniversity_result = db_fetch_object($currentuniversity_query)){ $currentuniversity = $currentuniversity_result->value; print $currentuniversity; }

Where $uid fetches $user->uid of signed up users.

But the table column is blank for this one. Using similar query to fetch other profile values of the signed up user works.

Please help!

Answer 1

Try using db_select something like below to build the query for your above condition:

$currentuniversity_query = db_select('field_host_university_value', 'fhuv');

//query of left join with 'content_type_manage_current_university' table.
$currentuniversity_query->leftJoin('content_type_manage_current_university', 'ctmcu',
  '(fhuv.nid = ctmcu.nid)');

//query of conditions and fetching the result
$currentuniversity_query = $currentuniversity_query->condition('fhuv.uid', $uid, '=')
  ->fields('fhuv');
  ->execute();

You may have to modify a query little bit, I just provided what you can try. Also have a look at: https://api.drupal.org/api/drupal/includes!database!database.inc/function/db_select/7

---

For Drupal 6 try the following:

$currentuniversity_query = db_result(db_query("SELECT mcu.field_host_university_value FROM node n INNER JOIN content_type_manage_current_university mcu ON n.nid = mcu.nid
WHERE n.uid =%d", $uid));

Answer 2

Ok. My query was correct. The issue was in the php variable call. I changed $currentuniversity = $currentuniversity_result->value; to $currentuniversity = $currentuniversity_result->field_host_university_value; and now its working fine. Thanks for your help.