Page 1 of 1

Connecting to two databases using PHP

Posted: Fri Aug 01, 2008 6:42 am
by Hillu
I was trying to connect to two databases which are on the same host using the same user name and password. I wanted to establish the connnection to read the data from one of the tables in the first database and insert it in one of the tables in the second database. I wrote 2 PHP pages to connect to the two databases and included them in the file which contains the select and insert statment.But it is selecting the records from the first table and appending the results in that same table. Here is my code which includes the database connection pages.. Can u help me?

<?php
session_start();
include_once 'cls_connect_dbtest.php';
$dbtest = new connectTest_db;

include_once 'cls_connect_dbtestrms.php';
$dbtestrms = new connectTestrms_db;

//get all the data from the first table of the first database

$query = "SELECT * FROM batch ";
$result = @mysql_query($query,$dbtestrms->linktestrms) or die("Error cccc: ".mysql_error());
$nbrows_first = @mysql_num_rows($result);

while($rowdata = @mysql_fetch_array($result)){
$value1=$rowdata['batch_id'];
$value2=$rowdata['batch_code'];

$query_insert = "INSERT INTO batch(batch_id,batch_code) VALUES($value1, $value2) ";
$result_insert = @mysql_query($query_insert,$dbtest->linktest) or die("Error ddddd: ".mysql_error());
$nbrows = @mysql_num_rows($result_insert);
} //while($rowdata = @mysql_fetch_array($result))

print"Number of rows in the test table:$nbrows<hr>";
print "Query executed successfully!!<hr>";
?>

Re: Connecting to two databases using PHP

Posted: Fri Aug 01, 2008 8:16 am
by WebbieDave
I don't think the problem is in the code you posted. It may be located in cls_connect_dbtest.php or cls_connect_dbtestrms.php

Please review the posting guidelines, especially the portions about posting code with php tags.

Re: Connecting to two databases using PHP

Posted: Fri Aug 01, 2008 8:41 am
by Hillu
Okay. Thes are the two files I used. At first the two connections were defined in one file but the second connection kept overriding the second so I had to have 2 separate files. Can I make these two in one file.

cls_connect_dbtestrms.php

<?php
class connectTestrms_db {
private $MySQLDatabaseName='testrms';
private $MySQLDatabaseLogin='staff';
private $MySQLDatabasePassword='staff';
private $HostServerName="localhost";
public $linktestrms;

// Constructor
function __construct(){
//Connecting to the first database
$this->linktestrms=@mysql_connect($this->HostServerName,$this->MySQLDatabaseLogin,
$this->MySQLDatabasePassword) or die("Couldn't connect to MySQL!");
@mysql_select_db($this->MySQLDatabaseName,$this->linktestrms) or die("Error : ".mysql_error().'<br>Error No: '. mysql_errno());

print "Connection was successful!!!<hr>";
} // function __construct()

function closedb(){
@mysql_close($this->$linktestrms) or die("Error: ".mysql_error());
} //function closedb()
} // class connectTestrms_db

?>

cls_connect_dbtest.php
<?php
class connectTest_db {
private $MySQLNewDatabaseName='test';
private $MySQLDatabaseLogin='staff';
private $MySQLDatabasePassword='staff';
private $HostServerName="localhost";
public $linktest;

// Constructor
function __construct(){
//Connecting to the second database
$this->linktest=@mysql_connect($this->HostServerName,$this->MySQLDatabaseLogin,
$this->MySQLDatabasePassword) or die("Couldn't connect to MySQL!");
@mysql_select_db($this->MySQLNewDatabaseName,$this->linktest) or die("Error: ".mysql_error().'<br>Error No: '. mysql_errno());
} // function __construct()

function closedb(){
@mysql_close($this->$linktest) or die("Error: ".mysql_error());
} //function closedb()
} // class connectTest_db

?>

Re: Connecting to two databases using PHP

Posted: Fri Aug 01, 2008 9:21 am
by Rovas
It' s hard doing a class that connects to a database using mysql extension and from what I read some of the code is wrong. Use the mysqli extension to make a class or use this class (registration needed).
If you want to connect to 2 databases don' t use require_once or in the __autoload function something similar sequential initialisation of the class for the 2 and close the connection after use.