I want to query my Drupal database to get some data to use in variables in a page template

Ahhhh, yes. You'll need to use Drupal's database abstraction layer. It's actually a bit easier than the standard php functions, and safer as well.

I'm not next to a desktop machine right now but I will be in a little bit. That should get you started, though. Google 'Drupal database abstraction' and you should get some quality hits to look at.

Cheers!

Mike

Man this object oriented crap is like greek to me.

I just want to be able to pull data from a specific node-field into a variable that I can use and do calculations on several times on a page template.

I just dont get what all the drupal object functions are calling, nor do i know where to put them if I did.

HELP?

bdgbrick, howdy! Please credit Mike, I am just helping to provide you with links to the API.
http://drupal.org/developing/api/database

The Database Abstraction Layer explanations are here:
http://api.drupal.org/api/drupal/includes--database.inc/group/database/6

Note the example shown for db_query_range(). If you scroll down and click on specific function names, you can get additional information on ones like db_query(). They are named similar to mysql_ functions in PHP, so hopefully that will help you adjust.

Kevin

Hi, sorry for the delay. Thanks mwvisa1! That's amazingly generous of you. :-)

I was really OOPed out too when I started Drupal. When coding from scratch I prefer procedural over objects. However, when using them, the objects become golden.

Here's an exact query I used recently. Note how simple it really is (second one is probably better):

$result = db_query('SELECT delta, COUNT(delta) frequency FROM pollfield_votes WHERE nid = %d GROUP BY delta ORDER BY frequency DESC LIMIT 1', $nid);
$row = db_fetch_object($result);
$winner_winner_chicken_dinner = $row->delta;

Select allOpen in new window

And here's a sort of templated breakdown:

$result = db_query("SELECT thisfreakinfield, anotherfreakinfield FROM thetable WHERE thevalue = %d", $theuserinput);
$row = db_fetch_object($result);
$thisfreakinfield = $row->thisfreakinfield;
$anotherfreakinfield = $row->anotherfreakinfield;

Select allOpen in new window

You can use while($row = db_fetch_object($result)) and all that normal PHP goodness. Now comes the object variable part (if that's what you're having issues with). I'm not sure how versed you are in the PHP object notation for variables, so this is it in a nutshell:

If it's an object, then its descendant is referenced by $object->value, and if it's an array then the descendant is referenced by $array['value']. That's really the only big difference for most stuff you're going to do in Drupal. It also goes along that same vein to say that $object->array['key1']['key2'] is also perfectly valid if a descendant of an object is a 2-dimensional array.

If you have the "devel" module installed, you can also do a dpm($row) or dsm($row), which should place the structure into a much more readable format in your messsages area. You can also use print_r($row) to see what the data structure is instead of just guessing that there is an object here, and an array there, etc.

I hope I was able to help (a bit late). Let me know if I missed the mark or if part of what I put down is unclear. It's late at night and I'm tirrrrreeddddd..... ;-)

Mike

Hey Mike,

I love your FREAKIN query.  And Im pretty sure I follow it ok.  One thing I dont know is the best place to put it.  I will explain what I have going on a little better.  Please no laughing at the way i chose to do this, lets just say Im developing for people that have elementary level computing skills so this is so they can make the changes without me.

I have a content type called [metals]
for that content type I have 3 custom fields [field_spot_value][field_percent_01_value][field_percent_02_value]

I made 2 nodes for that content type, one for Gold and one for Silver
Au= nid-136
Ag= nid-137

That is all for the input, and they will be updated everyday. Now the reason for that is so i can build a Dynamicly driven daily buy chart page.
So I have a content type daily_buying, which I will need something like this:

GOLD
$au_spot = [field_spot_value] WHERE nid=136
$au_percent01 =  [field_percent_01_value] WHERE nid=136
......etc for Silver(nid-137)  Im sure you get the idea.
I need those variable to exist so in my page I can do math on them in a table for the different forms we sell the metals in, for instance:

1 gram of 14k gold is worth: $25.31 and the variables are as such:
($au-spot/31.1)*.583)*$au_percent01)

And I have about 30 more figures that use those variables, so I was trying to do some kind of preprocess thing for the content type daily_buying so that those are available from the start of the page.
Any direction for implement would be greatly apreciated!

Thanks a lot Mike, EE has saved my bacon many times!

Yes, the freakin queries are the most fun. ;) I'm not going to laugh at any way you choose to do it. Aside from "best practice" advice there are so many ways in Drupal to accomplish the same thing that sometimes creativity in our implementations is just part of the game. Incidentally, I understand what you are doing, and why you're doing it.

The simple idea for this would look something like this:

// Gold -- single values
$result = db_query("SELECT field_spot_value FROM content_field_spot_value WHERE nid = %d", '136');
$row = db_fetch_object($result);
$au_spot = $row->field_spot_value;

$result = db_query("SELECT field_percent_01_value FROM content_field_percent_01_value WHERE nid = %d", '136');
$row = db_fetch_object($result);
$au_percent01 = $row->field_percent_01_value;

echo ($au_spot/31.1)*.583)*$au_percent01);
//or
$somefreakinvariable = ($au_spot/31.1)*.583)*$au_percent01);   // ;-)

Select allOpen in new window

Here it is with some context bolding:
$result = db_query("SELECT field_spot_value FROM content_field_spot_value WHERE nid = %d", '136');
$row = db_fetch_object($result);
$au_spot = $row->field_spot_value;


This way is obviously only good if you have a limited number of metals since otherwise you'd be hard-coding every single one. If you have a whole bunch of them, then I'd run through a loop with the data's location in an array (this also makes it easy to add, change, or remove metals at any time by just changing the array):

// $metals should be in the form 'element' => 'nid'
$metals = array( 
 'au' => '136',
 'ag' => '137',
 'u' => '236'
);

foreach($metals as $key => $value) {
 $result = db_query("SELECT field_spot_value FROM content_field_spot_value WHERE nid = %d", $value);
 $row = db_fetch_object($result);
 $spot[$key] = $row->field_spot_value;

 $result = db_query("SELECT field_percent_01_value FROM content_field_percent_01_value WHERE nid = %d", $value);
 $row = db_fetch_object($result);
 $percent01[$key] = $row->field_percent_01_value;
}
echo ($spot['au']/31.1)*.583)*$percent01['au']);
//or
$somefreakinvariable = ($spot['au']/31.1)*.583)*$percent01['au']);   // ;-)

Select allOpen in new window

Please note, however, that my syntax could be absolutely, amazingly, spectacularly far off -- I'm known for spewing code, then debugging the syntax. It's not really the best way, but hey, we all have our weaknesses and strengths when it comes to coding, right? The concept, though, is the same I think. Also, this is for Drupal 6 since I actually didn't have a D7 site's database handy to look at. The only differences should only be the MySQL queries themselves.

Let me know how this works out (or doesn't work out). I should be in my office pretty much all day today (Eastern time) and I'll be checking in throughout.

By the way, this is a fun question. Problem solving is one of my hobbies, I suppose. :)

Good luck!

Mike

Mike,

I hope Im not becoming a pest but, I have tried to implements some preprocess functions based on your query suggestions. I was trying to use my themes template.php to preprocess my variables.

see the code below.

However, Im not sure how to make the functions work, delivering me the separate variables you help me find in the database.

I wish I could give you more points for all your help.

//This is to get a preprocess fuction for my content type daily_buying

function tangible_blog_preprocess_node(&$vars, $hook) {
    $function = __FUNCTION__ . '_' . $vars['node']->type;
  if (function_exists($function)) {
    $function($vars);
  }
}

//This is how to add my variables to the node type

function tangible_blog_preprocess_node_buying(&$vars) {
//how do I extract out the variables with your queries here to use
//I think it would look something along these lines
$vars[au_spot] = db_query() etc...
$vars[au_percent01]
$vars[ag_spot]
$vars[ag_percent01]
$vars[ag_percent02]

}

Select allOpen in new window

YEEEEEEEEEEEEEEESSSSSSSSSSS!!!!!!!!!!

4 DAYS I HAVE BEEN WORKING ON THIS!!!!!

YEAH BABY!

Thanks Mike I posted my final code in case you wanted to see what I ended up with!

Thanks for all your help!  I could not have done it without you and EE!

Brandon

//Function to process a specific node type
//--
function tangible_blog_preprocess_node(&$vars, $hook) {
    $function = __FUNCTION__ . '_' . $vars['node']->type;
  if (function_exists($function)) {
    $function($vars);
  }
}
//Get data from table
//--
function get_metal_data($field, $nodeId) {
	$result = db_query("SELECT $field FROM content_type_metals_data WHERE nid = %d", $nodeId);
	$row = db_fetch_object($result);
	$vars = (float)$row->$field;
	return $vars;
}

//put data into variables
//--
function tangible_blog_preprocess_node_buying(&$vars) {
 $vars['au_spot'] = get_metal_data('field_spot_value', 136);
 $vars['au_per01'] = get_metal_data('field_percent_01_value', 136);
 $vars['au_per02'] = get_metal_data('field_percent_02_value', 136);
 $vars['ag_spot'] = get_metal_data('field_spot_value', 137);
 $vars['ag_per01'] = get_metal_data('field_percent_01_value', 137);
 $vars['ag_per02'] = get_metal_data('field_percent_02_value', 137);
}

Select allOpen in new window

Unbelievable how much this website and the Experts make our lives easier.  Over and above help from junipllc, in my opinion I would award 2000 points for this question.

Thanks Mike