?
Solved

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

Posted on 2011-10-14
11
Medium Priority
?
307 Views
Last Modified: 2012-06-27
I have data stored in a content type in my drupal database

I can access my data outside drupal with a query no problem:
$metal_set = "SELECT *
FROM content_type_metals_data
WHERE nid = 136
LIMIT 1");
$metal = mysql_fetch_array($metal_set);
$gold =  $metal['field_spot_value']

But how do I get that into a variable to use with preprocess?
I need to do some calculations to the value in $gold
0
Comment
Question by:bdgbrick
  • 5
  • 4
10 Comments
 
LVL 12

Expert Comment

by:junipllc
ID: 36971494
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
0
 

Author Comment

by:bdgbrick
ID: 36971649
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?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36974788
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 12

Expert Comment

by:junipllc
ID: 36974889
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;

Open 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;

Open 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
0
 

Author Comment

by:bdgbrick
ID: 36980056
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!



0
 
LVL 12

Expert Comment

by:junipllc
ID: 36980484
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);   // ;-)

Open 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']);   // ;-)

Open 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
0
 

Author Comment

by:bdgbrick
ID: 36981453
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]

}

Open in new window

0
 
LVL 12

Accepted Solution

by:
junipllc earned 2000 total points
ID: 36982623
No need to worry! This is what EE is all about. Not to mention, I totally forgot to answer the other part of your question...where to put all of it.

You definitely are going in the right direction. I'm not completely sure what the exact function names are, but they definitely belong in template.php. You can also create a page.tpl.php just for that content type and put the queries and such at the top, but that's not really best practice. So again, you're ahead of the game.

One thing I notice, though, is that you're putting the variables as $vars[au_spot] instead of $vars['au_spot'] -- note the single quotes. Believe it or not, when I first started Drupal I completely forgot all of my PHP knowledge and did that exact thing -- and no errors were thrown so I didn't know anything was wrong. So after hours and hours of banging my head on my desk, and several gallons of coffee, I realized my mistake.

If putting the single quotes in doesn't work then it may be dependent on your theme. I typically use Zen as my base theme, and with that theme the vars are translated into named variables in the .tpl.php files. For example, $vars['au_spot'] would become $au_spot in the .tpl.php.

My suggestion is to take each piece a step at a time and use the dsm() function (if you have devel installed) to pretty-print the variables throughout the steps. For example, you could try dsm($vars['au_spot']) after the query (before line 16 in the code you put above). If the correct value is being returned, then go to the next step and put a dsm($vars) at the end of your function (e.g. line 20) to display all of the variables post-processing.

If you do that then you can systematically rule out each processing step...such as "well, it's not the query that's the problem, maybe it's the variable's scope, or maybe I made a typo" and move down the line like that.

dsm() (and dpm()) are your best friends now, at least they are mine. ;-) Provided you don't have too much data defined you can also do a "dsm(get_defined_vars());" to see all of the variables that exist where you're putting your code (a.k.a. the scope of your variables)

The other thing you may need to check is that your function names are correct. The devel_themer module is great for that...but it can break certain theme layouts temporarily so it shouldn't ever be enabled on a site that's not strictly a development site.

Try those things and let me know if they work or not. It's tough to debug from where I am, but that's where I would definitely start. 95% of the problems I find in my code are solved by using the techniques above.

Have fun, and good luck!

Mike
0
 

Author Comment

by:bdgbrick
ID: 36982972
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);
}

Open in new window

0
 

Author Closing Comment

by:bdgbrick
ID: 36982981
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month17 days, 8 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question