0

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!

0

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));
  • As I mentioned I am using drupal 6 and if my understanding is correct, db_select() is not available in D6? Also, my sql knowledge is very limited, so if someone can give a better explained query, it would be really helpful. – Imran Khan Oct 21 '13 at 17:55
  • Looks to be same query as mine, except single quote added around $uid? Still returns blank column. – Imran Khan Oct 21 '13 at 18:01
  • @ImranKhan Check my updated answer – Ankit Agrawal Oct 21 '13 at 18:05
  • Sorry, but still blank :( – Imran Khan Oct 21 '13 at 18:07
  • @ImranKhan Can you try the new updated answer, assuming the field field_host_university_value is in the mcu table – Ankit Agrawal Oct 21 '13 at 18:21
0

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.

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.