By gettysburger on

I have a CCK field called "field_course_number". I wish to use it in a query that resides in a custom block on the same page as a node that contains the "field_course_number".

db_set_active('hcc');
$result = db_query("SELECT * FROM 09SP_COURSES WHERE courseNumber="field_course_number" ");
db_set_active('default');


while($value = db_fetch_array($result)){
print $value["title"] .' , ';
print '<br />';
}

I am sure I have the syntax wrong, but can't figure it out. Any thoughts would be appreciated. Thanks.

⋅ Categories: Drupal 6.x

Comments

alim418’s picture

alim418 commented

if you are on the node page you should be able to do

<?php
  $nid = arg(1);
  $node = node_load($nid);
  db_set_active('hcc');
  $result = db_query("SELECT * FROM 09SP_COURSES WHERE courseNumber=".$node->field_course_number[0]['value']);
db_set_active('default');


while($value = db_fetch_array($result)){
print $value["title"] .' , ';
print '<br />';
}
?>
blueflowers’s picture

blueflowers commented

There is input in this query, so this query should be:

$sql = "SELECT * FROM 09SP_COURSES WHERE courseNumber = %d";
$result = db_query($sql, $node->field_course_number[0]['value']);

http://xkcd.com/327/

Fourth Wall Media
Toronto, Canada

gettysburger’s picture

gettysburger commented

...now I see that you meant me to add this to the first reply.

gettysburger’s picture

gettysburger commented

Thanks a million. I put your two examples together and it works for me.


$nid = arg(1);
  $node = node_load($nid);
  db_set_active('hcc');
  $sql = "SELECT * FROM 09SP_COURSES WHERE courseNumbers = %d";
$result = db_query($sql, $node->field_course_numbers[0]['value']);

db_set_active('default');


while($value = db_fetch_array($result)){
print $value["title"] .' , ';
print '<br />';
}

I also need to compare 'courseLetters' to 'field_course_letters' in the same WHERE statement.


$nid = arg(1);
  $node = node_load($nid);
  db_set_active('hcc');
  $sql = "SELECT * FROM 09SP_COURSES WHERE courseNumbers = %d and courseLetters = %s";
$result = db_query($sql, $node->field_course_numbers[0]['value'],$node->field_course_letters[0]['value']);

db_set_active('default');


while($value = db_fetch_array($result)){
print $value["title"] .' , ';
print '<br />';
}

This breaks. Now that you kind folks have pointed me to db_query I am checking out the Pro Drupal Development book to see if I can figure out the syntax for multiple WHERE statements that include placeholders. Thanks again.

gettysburger’s picture

gettysburger commented

I figured it out. I was missing the single quotes around the string placeholder ('%s'). Here is the final code:

$nid = arg(1);
$node = node_load($nid);

db_set_active('hcc');
$sql = "SELECT * FROM 09SP_COURSES WHERE courseNumbers = %d and courseLetters = '%s' ";
$result = db_query($sql, $node->field_course_numbers[0]['value'],$node->field_course_letters[0]['value']);
db_set_active('default');

while($value = db_fetch_array($result)){
print $value["title"] .' , ';
print '<br />';
}
blueflowers’s picture

blueflowers commented

Great, glad this worked for you! :)

Fourth Wall Media
Toronto, Canada