new dev q: are mysql queries possible within drupal?
Hi, How can add an item to the Primary links Menu that will do a basic select query within drupal? ie Lets say I have a mysql database "database1" and a table "table1" with the following fields (name, address, email etc) How can I add a menu item to the Primary Links menu that will do a select * from table1 and then print it out in the right hand panel? Is this possible? Does it have to be the same database drupal uses or can I query a different db? Paul ps. The result would be something like: Joe Blow | 123 abc avenue | joeblow@applecart.org Joe Blow1 | 12323 abc avenue | joeblow1@applecart.org Joe Blow2 | 12323 abc avenue | joeblow2@applecart.org
Spartaguy wrote: > How can I add a menu item to the Primary Links menu that will do a select * from table1 and then print it out in the right hand panel? Your information is less than complete. Select on what? Is there a dialog of some sort? I'm guessing what you really want is a block. Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
I want to do a select on a table and just display the results in main window pane. ie header is standard logo, left hand side is navigation menu. One of the navigation items should be able to do a query on a table in the local drupal database or ideally in a separate database. the mysql syntax is the easy part. its the creating a menu item that will do the mysql select statement and display the results in the main window pane. Does this make sense? Paul On Mon, Nov 16, 2009 at 10:37 AM, Nancy Wichmann <nan_wich@bellsouth.net>wrote:
Spartaguy wrote:
How can I add a menu item to the Primary Links menu that will do a select * from table1 and then print it out in the right hand panel? Your information is less than complete.
Select on what? Is there a dialog of some sort? I’m guessing what you really want is a block.
Nancy E. Wichmann, PMP
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
I don't understand what you mean regarding the menu. But regarding you question about if its possible to use a second database - yes it is, provididing they are both the same type (ie, two Mysql databases). Change settings.php to read: $db_url['default'] = 'mysql://username1:password1@localhost/drupal'; $db_url['otherdb'] = 'mysql://username2:password2@localhost/otherdb'; And to query it: db_set_active('otherdb'); //Run queries as normal db_set_active('default'); Don't forget to set the active database back to default when you are done, or interesting things happen. 2009/11/16 spartaguy spartaguy <spartaguy300@gmail.com>
I want to do a select on a table and just display the results in main window pane.
ie header is standard logo, left hand side is navigation menu. One of the navigation items should be able to do a query on a table in the local drupal database or ideally in a separate database.
the mysql syntax is the easy part. its the creating a menu item that will do the mysql select statement and display the results in the main window pane.
Does this make sense?
Paul
On Mon, Nov 16, 2009 at 10:37 AM, Nancy Wichmann <nan_wich@bellsouth.net>wrote:
Spartaguy wrote:
How can I add a menu item to the Primary Links menu that will do a select * from table1 and then print it out in the right hand panel? Your information is less than complete.
Select on what? Is there a dialog of some sort? I’m guessing what you really want is a block.
Nancy E. Wichmann, PMP
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
I have two databases - one is for drupal data. The other db is for customer data. Inside the customer data db is a table - customer_data. I want to do a select * on customer_data in the customer database and show the result on screen. Can someone tell me how to do this? On Mon, Nov 16, 2009 at 7:36 PM, Frederik Grunta <fgrunta@gmail.com> wrote:
I don't understand what you mean regarding the menu. But regarding you question about if its possible to use a second database - yes it is, provididing they are both the same type (ie, two Mysql databases). Change settings.php to read:
$db_url['default'] = 'mysql://username1:password1@localhost/drupal'; $db_url['otherdb'] = 'mysql://username2:password2@localhost/otherdb';
And to query it:
db_set_active('otherdb');
//Run queries as normal
db_set_active('default');
Don't forget to set the active database back to default when you are done, or interesting things happen.
2009/11/16 spartaguy spartaguy <spartaguy300@gmail.com>
I want to do a select on a table and just display the results in main window pane.
ie header is standard logo, left hand side is navigation menu. One of the navigation items should be able to do a query on a table in the local drupal database or ideally in a separate database.
the mysql syntax is the easy part. its the creating a menu item that will do the mysql select statement and display the results in the main window pane.
Does this make sense?
Paul
On Mon, Nov 16, 2009 at 10:37 AM, Nancy Wichmann <nan_wich@bellsouth.net>wrote:
Spartaguy wrote:
How can I add a menu item to the Primary Links menu that will do a select * from table1 and then print it out in the right hand panel? Your information is less than complete.
Select on what? Is there a dialog of some sort? I’m guessing what you really want is a block.
Nancy E. Wichmann, PMP
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
This should give you a start. Don't forget to edit settings.php and change 1st line to you database name db_set_active('customerdb'); $result = db_query("SELECT * FROM {customer_data}"); db_set_active('default'); while ($row = db_fetch_object($result)) { print_r($row); } 2009/11/16 spartaguy spartaguy <spartaguy300@gmail.com>
I have two databases - one is for drupal data. The other db is for customer data. Inside the customer data db is a table - customer_data.
I want to do a select * on customer_data in the customer database and show the result on screen.
Can someone tell me how to do this?
On Mon, Nov 16, 2009 at 7:36 PM, Frederik Grunta <fgrunta@gmail.com>wrote:
I don't understand what you mean regarding the menu. But regarding you question about if its possible to use a second database - yes it is, provididing they are both the same type (ie, two Mysql databases). Change settings.php to read:
$db_url['default'] = 'mysql://username1:password1@localhost/drupal'; $db_url['otherdb'] = 'mysql://username2:password2@localhost/otherdb';
And to query it:
db_set_active('otherdb');
//Run queries as normal
db_set_active('default');
Don't forget to set the active database back to default when you are done, or interesting things happen.
2009/11/16 spartaguy spartaguy <spartaguy300@gmail.com>
I want to do a select on a table and just display the results in main window pane.
ie header is standard logo, left hand side is navigation menu. One of the navigation items should be able to do a query on a table in the local drupal database or ideally in a separate database.
the mysql syntax is the easy part. its the creating a menu item that will do the mysql select statement and display the results in the main window pane.
Does this make sense?
Paul
On Mon, Nov 16, 2009 at 10:37 AM, Nancy Wichmann <nan_wich@bellsouth.net
wrote:
Spartaguy wrote:
How can I add a menu item to the Primary Links menu that will do a select * from table1 and then print it out in the right hand panel? Your information is less than complete.
Select on what? Is there a dialog of some sort? I’m guessing what you really want is a block.
Nancy E. Wichmann, PMP
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
On Mon, Nov 16, 2009 at 7:55 AM, spartaguy spartaguy <spartaguy300@gmail.com
wrote:
I have two databases - one is for drupal data. The other db is for customer data. Inside the customer data db is a table - customer_data.
I want to do a select * on customer_data in the customer database and show the result on screen.
Can someone tell me how to do this?
Your bare bones menu item is something like this: $items['mypath/%object'] = array( 'title' => 'Page title', 'description' => 'Your description goes here.', 'access arguments' => array('permission string'), 'page callback' => 'customer_display', ); Then write a function named customer_display() that runs queries the way Frederik showed you and returns a string containing rendered HTML.
Thanks to everyone that replied. To summarise then: 1) I need to make a custom module. 2) The module should have a block for displaying the query and a menu hook 3) I can add the module to either primary links or navigation as a menu item 4) Rendered HTML using mysql commands is quite straight forward 5) The tables being queried are easier to access within the main drupal d/b but i can use a different db Regarding a different db, does this command: db_set_active('customerdb'); make *all* db queries go through customerdb or just the ones for my module? ie will drupal have any issues accessing the data it needs from say "drupaldb" if I dont change the active db back again? On Tue, Nov 17, 2009 at 9:27 AM, Earl Dunovant <prometheus6@gmail.com>wrote:
On Mon, Nov 16, 2009 at 7:55 AM, spartaguy spartaguy < spartaguy300@gmail.com> wrote:
I have two databases - one is for drupal data. The other db is for customer data. Inside the customer data db is a table - customer_data.
I want to do a select * on customer_data in the customer database and show the result on screen.
Can someone tell me how to do this?
Your bare bones menu item is something like this:
$items['mypath/%object'] = array( 'title' => 'Page title', 'description' => 'Your description goes here.', 'access arguments' => array('permission string'), 'page callback' => 'customer_display', );
Then write a function named customer_display() that runs queries the way Frederik showed you and returns a string containing rendered HTML.
db_set_active('customerdb'); // Make your custom queries db_set_active(); // Returns back to the Drupal database Jamie Holly http://www.intoxination.net http://www.hollyit.net spartaguy spartaguy wrote:
Thanks to everyone that replied.
To summarise then:
1) I need to make a custom module. 2) The module should have a block for displaying the query and a menu hook 3) I can add the module to either primary links or navigation as a menu item 4) Rendered HTML using mysql commands is quite straight forward 5) The tables being queried are easier to access within the main drupal d/b but i can use a different db
Regarding a different db, does this command:
db_set_active('customerdb');
make *all* db queries go through customerdb or just the ones for my module? ie will drupal have any issues accessing the data it needs from say "drupaldb" if I dont change the active db back again?
On Tue, Nov 17, 2009 at 9:27 AM, Earl Dunovant <prometheus6@gmail.com <mailto:prometheus6@gmail.com>> wrote:
On Mon, Nov 16, 2009 at 7:55 AM, spartaguy spartaguy <spartaguy300@gmail.com <mailto:spartaguy300@gmail.com>> wrote:
I have two databases - one is for drupal data. The other db is for customer data. Inside the customer data db is a table - customer_data.
I want to do a select * on customer_data in the customer database and show the result on screen.
Can someone tell me how to do this?
Your bare bones menu item is something like this:
| $items['mypath/%object'] = array( 'title' => 'Page title', 'description' => 'Your description goes here.', 'access arguments' => array('permission string'), 'page callback' => 'customer_display', );|
Then write a function named customer_display() that runs queries the way Frederik showed you and returns a string containing rendered HTML.
spartaguy spartaguy wrote:
Thanks to everyone that replied.
To summarise then:
1) I need to make a custom module. 2) The module should have a block for displaying the query and a menu hook 3) I can add the module to either primary links or navigation as a menu item 4) Rendered HTML using mysql commands is quite straight forward 5) The tables being queried are easier to access within the main drupal d/b but i can use a different db
Regarding a different db, does this command:
db_set_active('customerdb');
make *all* db queries go through customerdb or just the ones for my module? ie will drupal have any issues accessing the data it needs from say "drupaldb" if I dont change the active db back again?
db_set_active('customdb'); will make *all* queries run against customdb, until you call db_set_active(); to set it back to the default DB. For that reason, you should minimize the amount of work you do while using that DB. For instance: function dostuff() { $old_db = db_set_active('custom'); $data = db_fetch_object(db_query("SELECT ...")); db_set_active($old_db); // Do some manipulation of $data here. return theme('mytheme_key', $data, $something_else); } Note that in the above, you put all of your DB interaction together in one place, switch the DB just long enough to run that, and then go back to normal. That ensures that once you get into theme calls or calling other Drupal functions you're back on the main Drupal database so that other code doesn't get confused. --Larry Garfield
On Thu, Nov 19, 2009 at 8:34 AM, larry@garfieldtech.com < larry@garfieldtech.com> wrote:
spartaguy spartaguy wrote:
Thanks to everyone that replied.
To summarise then:
1) I need to make a custom module. 2) The module should have a block for displaying the query and a menu hook 3) I can add the module to either primary links or navigation as a menu item 4) Rendered HTML using mysql commands is quite straight forward 5) The tables being queried are easier to access within the main drupal d/b but i can use a different db
Regarding a different db, does this command:
db_set_active('customerdb');
make *all* db queries go through customerdb or just the ones for my module? ie will drupal have any issues accessing the data it needs from say "drupaldb" if I dont change the active db back again?
db_set_active('customdb'); will make *all* queries run against customdb, until you call db_set_active(); to set it back to the default DB. For that reason, you should minimize the amount of work you do while using that DB. For instance:
function dostuff() {
$old_db = db_set_active('custom');
$data = db_fetch_object(db_query("SELECT ..."));
db_set_active($old_db);
// Do some manipulation of $data here.
return theme('mytheme_key', $data, $something_else); }
Note that in the above, you put all of your DB interaction together in one place, switch the DB just long enough to run that, and then go back to normal. That ensures that once you get into theme calls or calling other Drupal functions you're back on the main Drupal database so that other code doesn't get confused.
--Larry Garfield
Sounds a lot easier to just query it from dedicated tables within the main drupal db.
spartaguy spartaguy wrote:
I want to do a select on a table and just display the results in main window pane.
ie header is standard logo, left hand side is navigation menu. One of the navigation items should be able to do a query on a table in the local drupal database or ideally in a separate database.
the mysql syntax is the easy part. its the creating a menu item that will do the mysql select statement and display the results in the main window pane.
Are you really talking about a menu item or a block here? If you just want to add a section to the left sidebar that displays the results of your query, then in Drupal terms, you need to create a "block" in a module, which an administrator can then activate and display in the left sidebar on the Blocks admin page. This section of the Module Tutorial on drupal.org will get you started with block programming: http://drupal.org/node/206758 If you need the left sidebar section to appear inside of an existing Drupal Menu (Navigation, Primary Links, or another one defined on the Menus admin page) that is being displayed on the left sidebar, then I think your best bet would be: a) Add a "dummy" placeholder menu item to the menu, within the admin interface, where you want your query results to appear. b) In your theme, make a custom theme function for theme('menu_item') that will detect the dummy item and do your query, and otherwise just call theme_menu_item(). There is information on how to override theme functions here: http://drupal.org/node/341628 Good luck! Jennifer -- Jennifer Hodgdon * Poplar ProductivityWare www.poplarware.com Drupal, WordPress, and custom Web programming
On 16/11/2009 10:29 AM, Jennifer Hodgdon wrote:
a) Add a "dummy" placeholder menu item to the menu, within the admin interface, where you want your query results to appear. b) In your theme, make a custom theme function for theme('menu_item') that will detect the dummy item and do your query, and otherwise just call theme_menu_item(). I don't understand this approach. Why can't hook_menu and a custom load function be used to do this, while separating the theme from the data?
function mymodule_menu() { $items['customers/%customer'] = array( // Insert items here as per hook_menu docs. ); } function customer_load($customer_id) { db_set_active('customerdb'); // It's probably better to select fields, not * - there was discussion about this // previously I think. $customer = db_fetch_object("SELECT * FROM {customer_data} WHERE customer_id = %d", $customer_id); db_set_active('default'); return $customer; }
participants (8)
-
Andrew Berry -
Earl Dunovant -
Frederik Grunta -
Jamie Holly -
Jennifer Hodgdon -
larry＠garfieldtech.com -
Nancy Wichmann -
spartaguy spartaguy