...

View Full Version : multiple databases 1 page?



sitNsmile
02-09-2010, 10:40 PM
Trying to make this possible....

I have a central database which is included 5 websites (with there user,host,pass) to each of there databases.

I want to array a list of the 5 websites, so that I can connect to multiple databases on the same page. .. below is my idea of how I wanted to do it..

PAGE I WANT ALL DATABASES ARRAY ON!
-the main database is already included in header

<?
$sql_query = "SELECT * FROM sites";
$result = mysql_query($sql_query);
if(mysql_num_rows($result)){
while($row = mysql_fetch_array($result))
{
$site_id = $row['site_id'];
$site_url = $row['site_url'];

include "sandbox/db.php?site_id=".$site_id."";

$query1 = "select * from siteconfig";
$result1 = mysql_query($query1);
while($row1 = mysql_fetch_array($result1)) {
$site_title = $row1['sitetitle'];
?>
<tr>
<td><? echo $site_url; ?></td>
<td><? echo $site_title; ?></td>
</tr>
<?
}
}
?>

on the db.php page where I want to use to include all the databases using like a string of "site_id" so that it knows what database info it needs to use in the array. Hoping all this makes sense...


$site_id = $_GET['site_id'];

$sql_query = "SELECT * FROM sites WHERE site_id = $site_id";
$result = mysql_query($sql_query);
if(mysql_num_rows($result)){
while($row = mysql_fetch_array($result))
{
$site_id = $row['site_id'];
$site_url = $row['site_url'];
$db_host = $row['db_host'];
$db_name = $row['db_name'];
$db_user = $row['db_user'];
$db_pass = $row['db_pass'];
}
}

/**
/* DATABSE INFORMATION --------------
/*
/* MySQL Host
/*/
/* */ $mysql_host = $db_host;
/* *
/* MySQL User Name
/*/
/* */ $mysql_user = $db_user;
/* *
/* MySQL User Password
/*/
/* */ $mysql_password = $db_pass;
/* *
/* MySQL Database Name
/*/
/* */ $database_name = $db_name;
/* *
/* ----------------------------------
/*/

/* CONNECT TO SERVER */
$connection = mysql_connect ($mysql_host,$mysql_user, $mysql_password)
or die ("Cannot make connection to server!");

/* CONNECT TO DATABASE */
$db = mysql_select_db ($database_name,$connection)
or die ("Cannot make connection to database!");


anyone have a way to do this.. either alter to my context or maybe some easier way to array them? thanks

sitNsmile
02-09-2010, 11:14 PM
Then I thought.. how simple could it really be. I was trying to do something the hardddd wayy... this should make things much simpler.. how well did I do?


$db = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name);

Fou-Lu
02-09-2010, 11:32 PM
Its not quite so easy.
With mysql (the actual extension, not the database itself), you'll need to do a couple of things. First, if you're username and password are valid across the board, you're good with just a single connection. If you need to say query a table from one database and query from another, you'll want to establish more than one connection. Note that in the mysql_query (and related functions) you can pass a link identifier. This will be used for any specific connection you are using, otherwise it uses the last open:


$con1 = mysql_connect('host', 'user1', 'pass1');
$con2 = mysql_connect('host', 'user2', 'pass2');

mysql_select_db('database1', $con1);
mysql_select_db('database2', $con2);

$qry = mysql_query("SELECT * FROM tableInDB1", $con1);
$qry2 = mysql_query("SELECT * FROM tableInDB2", $con2);


Keep the connections separate and always refer the connection inside of the mysql query call.
Upgrading to MySQLi on the otherhand should prevent this issue since the connections are encapsulated within a specific object.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum