Connect to Multiple Databases

Hi Perchpole,

CMS Builder only has support for a connection to one database at a time. 

I guess that CMS Builder is connected to one of the databases? If so you can use its functionality to carry out any database requests (using getRecords, mysql_update, etc). Then you only need to create a manual connection for one database. 

I would do something like this:

<?php
/* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */

// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('C:/wamp/www/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

// load records from 'blog'
list($blogs, $blogMetaData) = getRecords(array(
'tableName' => 'blog',
'loadUploads' => true,
'allowSearch' => false,
));

$username = "your_name";
$password = "your_password";
$hostname = "localhost";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

//select a database to work with
$selected = mysql_select_db("examples",$dbhandle)
or die("Could not select examples");


//execute the SQL query and return records
$result = mysql_query("SELECT id, model,year FROM cars");


//fetch tha data from the database
while ($row = mysql_fetch_array($result)) {
echo "ID:".$row{'id'}." Name:".$row{'model'}."Year: ". //display the results
$row{'year'}."<br>";
}


//Insert data into database
$car   = mysql_escape('Ford');
$model = mysql_escape('Escort');

mysql_query("INSERT INTO cars (model, year) VALUES ('$car', '$model')");


//close the connection
mysql_close($dbhandle);
connectToMySQL();


This is just an example, so you'll need to change the code to work with your tables and databases. 

With this method you can also use functions built into CMS Builder, for example mysql_escape could be used to validate the data before you insert it into your other database.

Finally, the final function I've called is connectToMySQL(), this makes CMS Builder reconnect to its database. You'll need to use this if you want to use any of CMS Builders MySQL functions after this point.

Let me know if you have any questions.

Thanks!

Greg

Hi Greg -

Thanks for this. I'd worked out most of the code but I hadn't realised I needed:

mysql_close($dbhandle);
connectToMySQL();

That made all the difference!

:0)

Perch