Integrating Drupal and existing database through web services

Let me give you an example how I connect to external databases.
This is quite some kind of hack and a lot of static code, but it works quite well. Also tested with distant mySQL on external Servers.

STEP 1 - 3RD PARTY DATABASE CREDENTIALS

////////////////////////////////////////////////////////////////////////
Content of file sites/default/settings.php
////////////////////////////////////////////////////////////////////////
// DRUPAL DATABASE
$db_url['default'] = 'mysql://drupal_database:password@localhost/drupal_database_user';
// REMARK !!! EACH DATABASE CONNECTION
// MUST USE ITS OWN USER !!!
// IF NOT YOU GET MYSQL ERRORS OR IT DOES NOT WORK.
// TOOK ME HOURS TO FIND THAT.
$db_url['other_database'] = 'mysql://other_database:password@localhost/other_user';
////////////////////////////////////////////////////////////////////////

STEP 2 - CONNECT TO THIRD PARTY DATABASE

     // get users profile values
     $profile = profile_load_profile($user);
     $customer_number = $user->profile_customer_number;
     $pin_code = $user->profile_pin_code;
     $output = 'Your customer number '.$customer_number;

/*  ANOTHER MYSQL THING WITH LOCALIZATION (EUROPEAN CHARACTERS)
     $query_charset  = "SET NAMES 'utf8';"; //only for mySQL 4.1
db_set_active($db_key);
     $result_charset = db_query($query_charset);
*/

// CONNECT TO DRUPAL DB FIRST. DO THIS EVERYTIME ON THE BEGINNING OF A SCRIPT.
db_set_active('default');

     $customer_found = 0;
     $customer_type = "company"; // projectroles.role_id = 6 'Kunde'
     //query customer name
     $query_comp = "SELECT id, name FROM companies";
     $query_comp .= " WHERE id = '".$customer_number."'";

///////////////////////////////////////////////////////////////
// NOW THE MAGIC HAPPENS
db_set_active('other_database');
// WE JUST FETCH THE RESULTS IN A VARIABLE ...
     $result_comp = db_query($query_comp);
//... AND SITCH BACK TO DRUPAL
db_set_active('default');
///////////////////////////////////////////////////////////////

     $num_comp = @mysql_numrows($result_comp);
     if ( $num_comp == 1 )
     {
        $customer_found = 1;
        $customer_name = utf8_encode(mysql_result($result_comp,0,"name"));
     }
     else
     {
        //query private customers 
     }
. . .
and so on ...

EXTENDED EXAMPLE (USING STEP 1 AND STEP2)

global $user;
$id = $_GET['id'];
$o = $_GET['o'];
$order = ' ORDER BY a.l_name ASC'; // default

// LINK CALLBACK FOR SORTING OF TABLE
if ($o == 'f'){$order = ' ORDER BY a.f_name ASC';}
if ($o == 'l'){$order = ' ORDER BY a.l_name ASC';}
if ($o == 'd'){$order = ' ORDER BY l.desc1 ASC';}
if ($o == 'p'){$order = ' ORDER BY l.phone_1 ASC';}
if ($o == 'e'){$order = ' ORDER BY l.email_1 ASC';}

// CHECK IF USER IS IN SOME roles GROUP
if (in_array('authenticated user', $user->roles))
    {

     //fetch addresses via adrloc for every location
     $query_addresses = "SELECT al.adr_id,al.loc_id,al.name,a.id,a.f_name,a.l_name";
     $query_addresses .= ",l.id,l.c_id,l.desc1,l.phone_1,l.phone_2,l.email_1,l.email_2 FROM adrloc al";
     $query_addresses .= " LEFT JOIN location l ON al.loc_id = l.id";
     $query_addresses .= " LEFT JOIN addresses a ON al.adr_id = a.id";
     $query_addresses .= " WHERE l.c_id='".$customer_number."'";
     $query_addresses .= " AND a.l_name NOT LIKE '#%'"; // only hardware items
     $query_addresses .= $order;
db_set_active($db_key);
     $result_addresses = db_query($query_addresses);
db_set_active('default');
     $num_addresses = @mysql_numrows($result_addresses);

     if ($num_addresses == 0)
      {
       $output .= "<br><br>No Persons found connected to this customer number.";
      }
     else
       {
       $output .= "<br><br>".$num_addresses." Person";
       if ($num_addresses > 1) {$output .= "en";}
       $output .= "<br>";
       $output .= '<table border=1>';
$output .= '<tr>';
$output .= '<td><a href="/personen?o=f">First Name</a></td>';
$output .= '<td><a href="/personen?o=l">Last Name</a></td>';
$output .= '<td><a href="/personen?o=d">Text</td>';
$output .= '<td><a href="/personen?o=p">Phone</a></td>';
$output .= '<td><a href="/personen?o=e">eMail</a></td>';
$output .= '</tr>';

       // go through locations list and return fields
       for( $k=0; $k<$num_addresses; $k++)
       {
$addresses_f_name = utf8_encode(trim(mysql_result($result_addresses,$k,"a.f_name")));
$addresses_l_name = utf8_encode(trim(mysql_result($result_addresses,$k,"a.l_name")));
$location_id =  mysql_result($result_addresses,$k,"l.id");
$location_c_id =  mysql_result($result_addresses,$k,"l.c_id");
$location_desc1 =  utf8_encode(trim(mysql_result($result_addresses,$k,"l.desc1")));
$location_phone_1 =  utf8_encode(trim(mysql_result($result_addresses,$k,"l.phone_1")));
$location_phone_2 =  utf8_encode(trim(mysql_result($result_addresses,$k,"l.phone_2")));
$location_email_1 =  utf8_encode(trim(mysql_result($result_addresses,$k,"l.email_1")));
$location_email_1 =  utf8_encode(trim(mysql_result($result_addresses,$k,"l.email_1")));

$output .= '<tr>';
$output .= '<td>'.$addresses_f_name.'</td>';
$output .= '<td>'.$addresses_l_name.'</td>';
$output .= '<td>'.$location_desc1.'</td>';
$output .= '<td>'.$location_phone_1;
$output .= ' <br>'.$location_phone_2.'</td>';
$output .= '<td>'.$location_email_1;
$output .= ' <br>'.$location_email_2.'</td>';
        $output .= '</tr>';
       }
       $output .= '</table>';
      }
     return $output;
    }
else
// SECTION IF USER IS NOT LOGGED IN

{
// DISPLAY SOME TEXT
echo 'You need to log in';
          if (empty($edit)) {
            $edit['destination'] = $_GET['q'];
          }
// THROW THE LOGIN FORM AGAINST THE USER
          // NOTE: special care needs to be taken because on pages with forms,
          // such as node and comment submission pages, the $edit variable
          // might already be set.
          $output .= form_hidden('destination', $edit['destination']);
          $output .= form_textfield(t('Kunden-ID'), 'name', $edit['name'], 15, 64);
          $output .= form_password(t('Kennwort'), 'pass', $pass, 15, 64);
          $output .= form_submit(t('Log in'));
          $output .= "</div>";
          $output = form($output, 'post', url('user/login'));
        return $output;
}

hope it helps

kr, Robert
http://divoky.com/drupal

Hello,
if you create webservice for existing recruitment/job database it can be used by other parties and makes future integration much easier. P2p integrations are difficult to maitain (and number of connections will grow to n! )
For information how to access webservice from PHP check:
http://www-128.ibm.com/developerworks/opensource/library/os-phpws/
In this article you will also find lot of usefull resources about webservices.
Check also 'amazone' module .
Regards
Jan