nV News Forums - PHP/Mysql Multiple Questions

nV News Forums (http://www.nvnews.net/vbulletin/index.php)

- Software Development (http://www.nvnews.net/vbulletin/forumdisplay.php?f=53)

- - PHP/Mysql Multiple Questions (http://www.nvnews.net/vbulletin/showthread.php?t=100791)

PHP/Mysql Multiple Questions

Scenario: I have two databases located on two different servers. There are two options for the databases. 1. They contain a set of tables that are not in the other, but there would need to be a way to join those tables in queries. For example, database1 would contain tables a,b,c while database2 would have tables d,e,f. 2. They both are exact mirrors of each other with the same tables and data, but there would need to be a way to syncronize the data between the two. ie. (both databases have a,b,c,d,e,f tables)

1. How do you handle multiple mysql server connections on a single script/page? Is it even possible without having to use a "database.table.field" type queries? I would like to be able to open 2 connections to different databases located on different servers, then using joins on tables from both of them. Is that possible?

2. The two mysql servers can't be configured for Replication from Master to Slave. Is there a way to do replication type functionality with PHP? How would you compare 2 Mysql tables with PHP? What would be the simplest way?

Re: PHP/Mysql Multiple Questions

1) I don't have a 100% positive answer but I don't think this is possible since each SQL statement is executed in the context of the opened db connection pointer. Sure you can have multiple pointers open to different DB servers but I don't believe there is a way to join these two pointers in an INNER JOIN/LEFT JOIN style syntax. I believe you'd have to do recordset enumeration in PHP and possibly some additional nested queries.

2) How many records? I guess you could use two arrays and compare them, inserting objects not found in array1 to db2 and array2 to db1.

Re: PHP/Mysql Multiple Questions

Quote:

Originally Posted by evilghost

1. I've done a few searches on the web, and there were a few suggestions that using "database.table.field" in queries would work once you connected, but you can't use mysql_select_db and it seems to have only been tested on different databases on the same server. I need something similar but the databases located on different servers. I'm not sure nested queries would work in this case since mysql_select_db will lose the connection to the other database. I'm going to test out a few things in the mean time.

2. The tables in question are around 100 rows with about 4-10 fields. I'm thinking about making a change log table to record which tables were changed, then using that information to select the changed table/s and copying all those rows to the other table/s after those tables are emptied. I'm pondering this because the data in the tables can be deleted, new data be added, or the data could also be edited. And the extra logic needed to take these into account is something I don't quite have the time to develop.

The tables in question aren't very large now, but I do expect them to grow larger in the future. By then though, I'm hoping we'll have moved on to servers that do allow Mysql replication which will make things much simpler.

Re: PHP/Mysql Multiple Questions

Could you do something like this?

Code:

<?php



class db {

        

        public $db1_srvr                = 'mysql1.domain.com';

        public $db1_port                = 3306;

        public $db1_name                = 'user';

        public $db1_pswd                = 'password';

        public $db1_db                        = 'foo';



        public $db2_srvr                = 'mysql2.domain.com';

        public $db2_port                = 3306;

        public $db2_name                = 'user';

        public $db2_pswd                = 'password';

        public $db2_db                        = 'foo';

        

        public $cdb1;

        public $cdb2;

        

        public $qr1;

        public $qr2;

        

        

        function db_conn ($server) {

                switch ($server) {

                        case 1:

                                $this->cdb1 = mysqli_connect($this->db1_srvr, $this->db1_name, $this->db1_pswd, $this->db1_db) or die(mysql_error());

                        break;

                        case 2:

                                $this->cdb2 = mysqli_connect($this->db2_srvr, $this->db2_name, $this->db2_pswd, $this->db2_db) or die(mysql_error());

                        break;

                }

                return true;

        }

        

        function GenericQuery ($c, $table) {

                $s = 'SELECT * FROM `'.$table.'` SORT BY `uid`';

                if ($c == 1) {

                        $q = mysqli_query($cdb1, $s);

                        $r = mysqli_fetch_array($q, MYSQL_ASSOC);

                        $this->qr1 = $r;

                } elseif ($c == 2) {

                        $q = mysqli_query($cdb2, $s);

                        $r = mysqli_fetch_array($q, MYSQL_ASSOC);

                        $this->qr2 = $r;

                } else {

                        die("Invalid Server ID spec");

                }

                

        }

        

        

}

        

        

$dbX = new db();





?>

Re: PHP/Mysql Multiple Questions

Quote:

Originally Posted by t3hl33td4rg0n

Could you do something like this?

Code:

<?php



class db {

        

        public $db1_srvr                = 'mysql1.domain.com';

        public $db1_port                = 3306;

        public $db1_name                = 'user';

        public $db1_pswd                = 'password';

        public $db1_db                        = 'foo';



        public $db2_srvr                = 'mysql2.domain.com';

        public $db2_port                = 3306;

        public $db2_name                = 'user';

        public $db2_pswd                = 'password';

        public $db2_db                        = 'foo';

        

        public $cdb1;

        public $cdb2;

        

        public $qr1;

        public $qr2;

        

        

        function db_conn ($server) {

                switch ($server) {

                        case 1:

                                $this->cdb1 = mysqli_connect($this->db1_srvr, $this->db1_name, $this->db1_pswd, $this->db1_db) or die(mysql_error());

                        break;

                        case 2:

                                $this->cdb2 = mysqli_connect($this->db2_srvr, $this->db2_name, $this->db2_pswd, $this->db2_db) or die(mysql_error());

                        break;

                }

                return true;

        }

        

        function GenericQuery ($c, $table) {

                $s = 'SELECT * FROM `'.$table.'` SORT BY `uid`';

                if ($c == 1) {

                        $q = mysqli_query($cdb1, $s);

                        $r = mysqli_fetch_array($q, MYSQL_ASSOC);

                        $this->qr1 = $r;

                } elseif ($c == 2) {

                        $q = mysqli_query($cdb2, $s);

                        $r = mysqli_fetch_array($q, MYSQL_ASSOC);

                        $this->qr2 = $r;

                } else {

                        die("Invalid Server ID spec");

                }

                

        }

        

        

}

        

        

$dbX = new db();





?>

That's similar to what I ended up doing. It took a bit more planning and re-arranging of the various tables, but the 2 databases in question are partitioned and working like I wanted. Of course I also needed to plan the queries to take this into account.

I'm using php5 objects and global defines to make the connection to both databases. The base parent object contains the connect/select_db functions which gets inherited by all other objects which need to connect to the 2 databases to make queries. Pretty much all queries are done in sub classes. I actually ended up with more simpler queries than less complex large queries, but this seems to work to my advantage. ;)

Re: PHP/Mysql Multiple Questions

Are you using ?

Code:

class foo { }

Code:

class bar extends foo { }

Re: PHP/Mysql Multiple Questions

Quote:

Originally Posted by t3hl33td4rg0n

Are you using ?

Code:

class foo { }

Code:

class bar extends foo { }

Pretty much. I have one base class with 2 database connection functions each of which use global defines to connect to the databases and creates 2 link resources. Depending on which database/table you need to query, you use one or the other(link resource).