External Database Query

I do this a lot, and it's rather simple. Set the configuration in your settings.php file for the database:

$db_url[{a name for the connection}]="{drupal styled connection string}";

Then in your module before you query the database just do a:

db_set_active({the name from above});

After the query return Drupal back the the default database:

db_set_active();

apparently views 2 also has ability to connect to an external db, but not through UI

Have you looked at table wizard?

http://drupal.org/node/452374

I tried table wizard but does not allow relation between two external tables. Data module create the table in drupal i do not want to create the table as well.

I have the database external name : Mydbase

Following table are there.

Tables Fields

Company maincode, subcode, company name

Product product code, product name

Capacity maincode, subcode, product code, capacity, since.

one to many relation of company and capacity

one to many relation of product and capacity.

want to display in view

maincode, subcode,product code, company name,product name, capacity,since.

want to expose the product code filter as well

I have setup the setting.php db_url of external database.

Then i create the form in page for select the company. Once the user select the company i store the company code in session variable for reference. This variable i will use for fetch information in view. I will enter its value in default argument php code and view will provide me the capacity information of selected company. This page auto redirect the user to view page of capacity. by using drupal_got0() function. the code of the page is attached below.. In code i have one problem.I can not store the company name in variable,only able to fetch the information maincode and subcode. How the fetch the display value of drop down instead of value tag value...

I do not know how to use view for external database without the content type define in drupal, and do not have any cck field as well. If i can i want to expose the product code as well in the view.so user can select and view one product of select company.

+++++++++++++++++ code start +++++++++++++++++++++

<?php
if(isset($_SESSION['ses_maincode'])){
echo "Company Main code Selected = " . $_SESSION['ses_maincode'] . "";
echo "Company Sub code Selected = " . $_SESSION['ses_subcode'] . "";
echo "
";
}
$output .= drupal_get_form('formexample_testform');

echo $output;

function formexample_testform() {
$myvalue=array();
$count = 0;
db_set_active('mydb');
$result = db_query('SELECT maincode,subcode,cname FROM {company}');
while ($row = db_fetch_array($result)) {
$arrayvalue=$row[maincode] . '+' . $row[subcode];
$myvalue[$arrayvalue]=$row['cname'];
$count = $count +1;
}
db_set_active('default');
$form['select_company'] = array(
'#title' => t('Select Company'),
'#type' => 'select',
'#description' => t('Pick the company '),
'#options' => $myvalue,
'#multiple' => FALSE,
);
$form['submit'] = array(
'#type' => 'submit',
'#value' => t('Submit')
);
return $form;
}

function formexample_testform_submit($form_id, $form_state) {
$name=$form_state['values']['select_company'];
drupal_set_message(t(' You have select the company %name',
array('%name' => $name)));

$mystring = $form_state['values']['select_company'];
$pos = strrpos($mystring, "+");
$endpoint = strlen($mystring) - $pos;
$_SESSION['ses_maincode']=substr($mystring,0,$pos);
$_SESSION['ses_subcode']=substr($mystring,$pos+1,$endpoint);

$urlstring='node/7';
/** . $_SESSION['ses_maincode']; */
drupal_goto($urlstring);

}
?>

+++++ code end here

It is good, i found this article before, but the big question: how can i use this database connection?
I made a module to use a hook_views with this database, but the view always want to use the default database :(

global $db_url,$user;
$connstr = "mysql://".$mysqluser.":".$pass."@localhost/$dbname";
$default = $db_url;
$db_url['default'] = $default;//keep the default
$db_url[$user->name] = $connstr;//add new
db_set_active($user->name);//set it to new