By designerdre101 on

I have experience with other CMS's but i'm new to Drupal devel. I need to pull the body content of a certain unpublished page for output on another page. Do I have to query the db, select the file, specify the field which would be body in this case, then output the results wherever i need them?

If this method is correct could someone lead me to a resource on writing db queries in drupal....i have no idea how drupal stores data in the db and am therefore have no clue what conventions to use in the query.

I would appreciate any resources or examples I can get to get, thanks!

⋅ Categories: Drupal 5.x

Comments

khanz’s picture

khanz commented

views module might help you..

------------
Volvo, Video, Velcro. (I came, I saw, I stuck around.)

cpc’s picture

cpc commented

Views might work, but could be overkill for your situation.

Your method would most likely work fine. You'll probably just want to query the node and node_revisions tables. The node table stores whether or not something is published (in the status column) while node_revisions stores the body content of the node under its body column.

Here's an example of outputting the bodies of all your unpublished nodes:

$result = db_query("SELECT * FROM {node} n, {node_revisions} r WHERE n.nid=r.nid AND n.status=0");
while ($myNode = db_fetch_object($result)) {
  echo $myNode->body;
  echo "<p></p><p></p>"; // put in some spacing
}

Since you're new to Drupal, you'll probably notice the squiggly braces around the table names. The db_query function uses that to automatically translate the table names if your database has a prefix for all the tables. For example, you may only have one database on your host, but want 2 drupal installations. So your node tables could each be drupal1_node and drupal2_node.

Anyways, you can look up the db_query function for more examples:
http://api.drupal.org/api/function/db_query/

It's a handy function because it also helps you filter your input to prevent SQL injection and quote escaping. For example:
$res = db_query("SELECT * FROM people WHERE name = '%s'", $name);

You may want to note that in Drupal 7, there is the whole new database abstraction layer, which will change things quite a bit, so don't get too used to this method I guess.

And don't forget - Google is your friend...

- Cheers

designerdre101’s picture

designerdre101 commented

Very helpful info!

Following http://api.drupal.org/api/function/db_query/, I was unable to find information breaking down the anatomy and conventions of the drupal db_query. For example, I need to know how to specify file name and the format file names can be referenced by. Without documentation i'm fairly lost.

I have not been able to find much on google either. Im eager to get a handle on these concepts so if you know of any helpful documentation it would be appreciated!

Your code gave me the start i needed. I won't be needing the while loop since I only need the body content from one specific page. I either need to know how to find the "nid" of a page or how to specify a file name.

Ultimately finding good documentation or examples to learn from would be ideal. I REALLY appreciate the help and would like to learn how to fish rather than always being given my fish if that makes sense :-).

$result = db_query("SELECT * FROM {node} n, {node_revisions} r WHERE n.nid=r.nid AND n.status=0");
$rec_links = db_fetch_object($result);
echo $rec_links->body;

This works but gives all unpublished within the table.

Thanks again!

cpc’s picture

cpc commented

I'm not sure what you mean by file. Do you mean a file that has been attached to a node? Otherwise, all the data should just be in the database and not the file system.

When finding the 'nid' of a page, it all depends how you're going about it. Are you needing a specific nid as a one time thing on a certain page? For example, maybe you just want to pull in the node with nid=13. In which case, you could just manually look through the node or node_revisions tables and locate the nid there. Another hint is when you edit a node, you can see the node's nid in the URL for that page: www.yoursite.com/node/13/edit (where 13 is the nid)

However, I think you may be talking about something more dynamic, such as where a user has a select list of other pages you want to pull in, so you can't just manually plug the nid in.

I'd really have to know your scenario to suggest something though.

Regarding the db_query function, yeah, sometimes it can be difficult to get nice documentation. A lot of the time I'll just go through existing modules to see how other people use the functions.

One resource I found quite handy is the book, "Pro Drupal Development - Second Edition": www.drupalbook.com

It puts things together quite well and goes over all the main components of Drupal.

In short, don't feel bad, because Drupal is a difficult beast to get used to. I remember how lost I was, but it really is such a powerful system once you get the hang of it. And of course, nothing beats raw experience.

designerdre101’s picture

designerdre101 commented

I am needing the body content of a certain page as a one time thing. No looping needed or anything very dynamic.

My needs are to:

1. Successfully query the specific page with the content I need
2. Output the body content of that page

$result = db_query("SELECT * FROM {node} n, {node_revisions} r WHERE n.nid=745 AND n.status=0");
$rec_links = db_fetch_object($result);
echo $rec_links->body;

Shouldn't this work? When I use this I get all sorts of content that I'm not looking for as if it ignores the nid specification.

Ultimately i think i will order that book. Thanks for being willing to help.

cpc’s picture

cpc commented

I think you just have a problem with your SQL:
SELECT * FROM {node} n, {node_revisions} r WHERE n.nid=745 AND n.status=0

I think you're just forgetting to link the node table with the node_revisions table. I'd just try this:
SELECT * FROM {node} n, {node_revisions} r WHERE n.nid=745 AND n.status=0 AND r.nid=n.nid

That way, you're narrowing all the rows from node_revisions down to 1.

That should do what you need. If not, I'm a bit stumped and would probably need more information.

designerdre101’s picture

designerdre101 commented

Hey thanks a lot that worked! I had no idea how to narrow that down!

Do you know anything about Imagefield? Does it upload images to the db? If so how can i access them through a query?

cpc’s picture

cpc commented

The actual image content is not stored within the database. Instead, there are columns in the database that link to the file in the Drupal install.

It sounds like you are using CCK and you've created a field that uses Imagefield. Go into the table for your CCK content type. It should be something like:
content_type_xyz

In there, you should see a column something like:
field_xyz_file_fid

That column contains the file ID from another table called files. Go in there and you should see the connection, since the files table has a column called filepath. If it's not setup this way, I could be confusing Imagefield with something else, but I don't think so.

I hope that points you in the right direction...

designerdre101’s picture

designerdre101 commented

That sounds about right but the prob is that right now i can't get into the db so i have no idea of what the structure is.

Here is my scenario:

URL - http://www.integragroup.com/news/articles

Here is the code that generates that listing:

/**
* Creates a list of node titles from specific multiple categories
* with a link to each node.
*
* To change which taxonomies are listed, simply edit
* $taxo_id1 which is the first taxonomy term referenced
* $taxo_id2 which is the second taxonomy term referenced
* $taxo_id3 which is the third taxonomy term referenced
*
* To change the number of node titles listed, simply edit
* the $list_no number.
*
* This works with drupal 4.5 and drupal 4.6
*/
$taxo_id = 2;
$list_no =20;
$sql = "SELECT node.title, node.created, node.nid, content_field_excerpt.field_excerpt_value FROM node INNER JOIN term_node ON node.nid = term_node.nid INNER JOIN content_field_excerpt ON node.nid = content_field_excerpt.nid WHERE (term_node.tid = $taxo_id) ORDER BY node.created DESC LIMIT $list_no";
print "<table border='0' class='itemlisting'>";
$result = db_query($sql);
while ($anode = db_fetch_object($result)) {
print format_date($anode->created, 'custom', 'F d, Y');

print l($anode->title, "node/".$anode->nid);

print l($anode->field_excerpt_value, "node/".$anode->nid);

print l("Read More...", "node/".$anode->nid); 
}
print "</table>";

The goal is to have a thumbnail associated with each item in the listing. I have installed the imagefield module and added a image field called field_article_image_thumb to the news content type (those are news pages in the listing).
The field works fine...it uploads the file to the folder i specified successfully.

How do I now update the code above to not only pull node.created, node.nid, content_field_excerpt etc.. but also to include the image for each post?

I realize that the image is not stored in the db but the path to the image is. If i could even access the path and know how to print it that would work for me as well.

I realize how powerful drupal can be and as this is the first Drupal site i'm working on I want to learn the ropes rather than quit for an easier CMS...I think this is worth it.

Thanks you very much!

cpc’s picture

cpc commented

Hmmm, if you can't get into your DB to look at things, then that's a major hurdle! Why don't you just install phpmyadmin?

It's not the greatest idea to install it in a publicly accessible folder, but it should be fine. I would download it, and put it in a folder with a random name like this:
http://www.integragroup.com/phpmyadmin123456abcdefg

...just because there are bots out there that automatically look for PHPMyAdmin installations and they look in all the obvious folders like "phpmyadmin" (not that they can necessarily guess your password if they do find the installation).

Anyways, you can get the latest here:
http://sourceforge.net/projects/phpmyadmin/files%2FphpMyAdmin%2F3.2.5%2F...

Also, your PHPMyAdmin login should be the same as your database user/pass in sites/default/settings.php

To answer your question more directly, I would just do another query inside your loop. Something like this:

$path = db_fetch_result(db_query("SELECT f.filepath FROM {files} f, {content_type_xyz} c WHERE c.field_article_image_thumb_fid = f.fid"));

echo '<img src="' . $path . '" />';

Keep in mind that I just whipped that up, so it could very easily be wrong (especially the fields and definitely the content_type_xyz table will need to be your own).

But again, that's really why you need to install phpmyadmin. I can't imagine doing any programming like this without knowing what's in the DB!

cpc’s picture

cpc commented

I just realized that my query especially won't work because it doesn't take the node ID into consideration. It should be more like this:

$path = db_fetch_result(db_query("SELECT f.filepath FROM {files} f, {content_type_xyz} c WHERE c.field_article_image_thumb_fid = f.fid AND c.nid=%d", $anode->nid));

echo '<img src="' . $path . '" />';
designerdre101’s picture

designerdre101 commented

After doing a print_r(get_defined_vars()); on one of the pages with image field I've found the array containing the path to the image that I need. My only problem is I don't know how to select it. There is a already a variable, $anode, which contains the array of the results of the query: while ($anode = db_fetch_object($result))...

To get the tilte of a page I can simply say echo($anode->title);

However I cannot figure out how to select nested array items. PLEASE HELP!! I'm in urgent need. Really appreciate it. Drupal seems pretty powerful. Below is the dta structure of the field im needing the filepath from.

[field_article_image_thumb] => Array
(
[0] => Array
(
[fid] => 809
[title] => Picture 2.png
[alt] =>
[nid] => 749
[filename] => Picture 2.png
[filepath] => files/images/article_thumbs/Picture 2_0.png
[filemime] => image/png
[filesize] => 17559
)

)

cpc’s picture

cpc commented

I think you might be getting confused regarding selecting objects vs arrays.

Don't you just want this:

echo $anode->field_article_image_thumb[0][filepath];
designerdre101’s picture

designerdre101 commented

That seems to make sense but it still does not return anything. Im not sure if its because I don't know how to target nested arrays or if it's because of something else. I think you are on to something though. Here is the complete set up:

$taxo_id = 3;
$list_no =25;
$sql = "SELECT node.title, node.nid, node.created, content_field_excerpt.field_excerpt_value FROM node INNER JOIN term_node ON node.nid = term_node.nid INNER JOIN content_field_excerpt ON node.nid = content_field_excerpt.nid WHERE (term_node.tid = $taxo_id AND node.status = 1) ORDER BY node.created DESC LIMIT $list_no";
$result = db_query($sql);
print "

";
while ($anode = db_fetch_object($result)) { ?> 
}
print "</table>";

Do you think I need to add references to the field_article_image_thumb field in the sql query? Forgive me I am very new to Drupals way of things.

print format_date($anode->created, 'custom', 'F d, Y'); 
//Just to see if it would actually pull the data
echo($anode->field_article_image_thumb[0][filepath]);

print l($anode->title, "node/".$anode->nid);

print l($anode->field_excerpt_value, "node/".$anode->nid);

print l("Read More...", "node/".$anode->nid);
cpc’s picture

cpc commented

Okay I think two things are being confused here.

When Drupal builds a page (a node), usually it passes in a variable ($node) to your template files which contains all the information about the node currently being displayed.

When it builds this $node variable, it includes a lot more information than just a simple SQL query. It combines other data such as CCK fields and structures it into the $node variable (which is an object) by adding arrays to it and even other objects.

This is what I thought you were working with before, but you're actually just using the output from your SQL query, which won't contain nested arrays like the $node object on the page would.

So, to answer your question more simply, yeah, you'll need to SELECT that field in your SQL query. Otherwise it'll just be blank. Try this:

$sql = "SELECT node.title, node.nid, node.created, content_field_excerpt.field_excerpt_value, content_field_excerpt.field_article_image_thumb_value FROM node INNER JOIN term_node ON node.nid = term_node.nid INNER JOIN content_field_excerpt ON node.nid = content_field_excerpt.nid WHERE (term_node.tid = $taxo_id AND node.status = 1) ORDER BY node.created DESC LIMIT $list_no";

... and this ...

echo $anode->field_article_image_thumb_value;  // Notice there aren't the nested arrays
designerdre101’s picture

designerdre101 commented

That theoretically makes sense to me but does not work either. I'm getting thrown the following error:

user warning: Unknown column 'content_field_excerpt.field_article_image_thumb_value' in 'field list' query: SELECT node.title, node.nid, node.created, content_field_excerpt.field_excerpt_value, content_field_excerpt.field_article_image_thumb_value FROM node INNER JOIN term_node ON node.nid = term_node.nid INNER JOIN content_field_excerpt ON node.nid = content_field_excerpt.nid WHERE (term_node.tid = 3 AND node.status = 1) ORDER BY node.created DESC LIMIT 25 in /home/virtual/site66/fst/var/www/html/includes/database.mysql.inc on line 172.

What I may be able to do is use "$node" outside out the query to target and store what i need inside a variable that I can refer to inside of the the while loop.

working from the structure shown in the print_r could I use "$node" like this to grab the filepath?:

$node->field_article_image_thumb->0->filepath
or
$node[field_article_image_thumb][0]->filepath

here is the structure again:
[field_article_image_thumb] => Array
(
[0] => Array
(
[fid] => 809
[title] => Picture 2.png
[alt] =>
[nid] => 749
[filename] => Picture 2.png
[filepath] => files/images/article_thumbs/Picture 2_0.png
[filemime] => image/png
[filesize] => 17559
)

)

Your insight is very much valued and appreciated.