Drupal Tip #1 - Embedding A Database Query Into Page Or Story Content
Sat, 05/17/2008 - 16:31 — mosmith3
I have been busy setting up a few websites based on the Drupal CMS. I thought it might be a good idea to post a few tips on things I have figured out along the way.
One of the most helpful things I have figured out is how to include output from SQL queries into page content. This is very powerful as you can add almost any dynamic content you can think of. For instance, lets say you wanted to display the number of stories you have added to your site. You want to display this number on your front page. You can accomplish this by using embedded php to query your database. When you are editing the node that you will be adding the query to, make sure you select the "PHP Code" input format. This keeps Drupal from filtering out your PHP code.
*** Just a quick warning - you DO NOT want to allow just anyone the ability to use php scripts on your pages. By default only the "administrator" user has the ability to select the "PHP Code" input format. It is possible for malicious users to do some really nasty things to your site if you allow just anyone PHP access.
Here is the snippet that does the work:
<?php
$story_query = db_query(db_rewrite_sql("SELECT count(node.nid) AS count FROM node WHERE (node.type = 'story' OR node.type = 'page') AND node.status = 1"));
$story_count = db_fetch_object($story_query);
print "<div align=center><b>" . $story_count->count . "</b> articles and counting . . .</div>";
?>
and here are the results:
The query above is a very simple example. It just gives you a count of all nodes of type 'page' or 'story' that are published. Using this method you can extract any information you like and display it inside your nodes.
One of the most helpful things I have figured out is how to include output from SQL queries into page content. This is very powerful as you can add almost any dynamic content you can think of. For instance, lets say you wanted to display the number of stories you have added to your site. You want to display this number on your front page. You can accomplish this by using embedded php to query your database. When you are editing the node that you will be adding the query to, make sure you select the "PHP Code" input format. This keeps Drupal from filtering out your PHP code.
*** Just a quick warning - you DO NOT want to allow just anyone the ability to use php scripts on your pages. By default only the "administrator" user has the ability to select the "PHP Code" input format. It is possible for malicious users to do some really nasty things to your site if you allow just anyone PHP access.
Here is the snippet that does the work:
<?php
$story_query = db_query(db_rewrite_sql("SELECT count(node.nid) AS count FROM node WHERE (node.type = 'story' OR node.type = 'page') AND node.status = 1"));
$story_count = db_fetch_object($story_query);
print "<div align=center><b>" . $story_count->count . "</b> articles and counting . . .</div>";
?>
and here are the results:
7 articles and counting . . .
The query above is a very simple example. It just gives you a count of all nodes of type 'page' or 'story' that are published. Using this method you can extract any information you like and display it inside your nodes.
