Go Back   nV News Forums > Software Forums > Software Development

Newegg Daily Deals

Reply
 
Thread Tools
Old 10-22-07, 02:04 PM   #1
fivefeet8
Ngemu Mod
 
Join Date: Jul 2003
Location: Fresno, CA
Posts: 1,886
Default 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?
__________________
[i7 2600k @4.4ghertz][2x4 GB DDR3 1600][EVGA GTX570 1.280GB SC][EVGA GTX460 physx][Asrock Extreme7 Gen3 Z68][2xSeagate 160 Gb SATA HD raid0][Seagate 250 GB SATA2 HD][Sony Bravia 40' 1080p LCD HDTV][NEC 3520a DVD+-DLw][Windows 7 Ultimate x64][Rosewill 1000w]
fivefeet8 is offline   Reply With Quote
Old 10-22-07, 02:12 PM   #2
evilghost
Registered User
 
Join Date: Jul 2005
Posts: 3,606
Default 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.
evilghost is offline   Reply With Quote
Old 10-22-07, 03:17 PM   #3
fivefeet8
Ngemu Mod
 
Join Date: Jul 2003
Location: Fresno, CA
Posts: 1,886
Default Re: PHP/Mysql Multiple Questions

Quote:
Originally Posted by evilghost
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.
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.
__________________
[i7 2600k @4.4ghertz][2x4 GB DDR3 1600][EVGA GTX570 1.280GB SC][EVGA GTX460 physx][Asrock Extreme7 Gen3 Z68][2xSeagate 160 Gb SATA HD raid0][Seagate 250 GB SATA2 HD][Sony Bravia 40' 1080p LCD HDTV][NEC 3520a DVD+-DLw][Windows 7 Ultimate x64][Rosewill 1000w]
fivefeet8 is offline   Reply With Quote
Old 12-24-07, 03:24 PM   #4
t3hl33td4rg0n
Dethklok Returns!
 
t3hl33td4rg0n's Avatar
 
Join Date: Dec 2004
Location: Kent, OH
Posts: 2,142
Send a message via AIM to t3hl33td4rg0n Send a message via MSN to t3hl33td4rg0n Send a message via Yahoo to t3hl33td4rg0n
Default 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();


?>
__________________


Intel QX9650 @ 3.83GHzXFX 780ieVGA GTX 560Ti8GB GSkill DDR800Creative Sound Blaster X-Fi Titanium PCI-ESeagate 1.5TB . 4x Samsung 2TB

Quote:
Originally Posted by MowTin View Post
....it's so clear how government makes things worse not better...
t3hl33td4rg0n is offline   Reply With Quote
Old 12-26-07, 11:08 PM   #5
fivefeet8
Ngemu Mod
 
Join Date: Jul 2003
Location: Fresno, CA
Posts: 1,886
Default 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.
__________________
[i7 2600k @4.4ghertz][2x4 GB DDR3 1600][EVGA GTX570 1.280GB SC][EVGA GTX460 physx][Asrock Extreme7 Gen3 Z68][2xSeagate 160 Gb SATA HD raid0][Seagate 250 GB SATA2 HD][Sony Bravia 40' 1080p LCD HDTV][NEC 3520a DVD+-DLw][Windows 7 Ultimate x64][Rosewill 1000w]

Last edited by fivefeet8; 12-31-07 at 12:03 PM.
fivefeet8 is offline   Reply With Quote
Old 12-29-07, 08:32 PM   #6
t3hl33td4rg0n
Dethklok Returns!
 
t3hl33td4rg0n's Avatar
 
Join Date: Dec 2004
Location: Kent, OH
Posts: 2,142
Send a message via AIM to t3hl33td4rg0n Send a message via MSN to t3hl33td4rg0n Send a message via Yahoo to t3hl33td4rg0n
Default Re: PHP/Mysql Multiple Questions

Are you using ?

Code:
class foo { }
Code:
class bar extends foo { }
__________________


Intel QX9650 @ 3.83GHzXFX 780ieVGA GTX 560Ti8GB GSkill DDR800Creative Sound Blaster X-Fi Titanium PCI-ESeagate 1.5TB . 4x Samsung 2TB

Quote:
Originally Posted by MowTin View Post
....it's so clear how government makes things worse not better...
t3hl33td4rg0n is offline   Reply With Quote
Old 12-31-07, 12:03 PM   #7
fivefeet8
Ngemu Mod
 
Join Date: Jul 2003
Location: Fresno, CA
Posts: 1,886
Thumbs down 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).
__________________
[i7 2600k @4.4ghertz][2x4 GB DDR3 1600][EVGA GTX570 1.280GB SC][EVGA GTX460 physx][Asrock Extreme7 Gen3 Z68][2xSeagate 160 Gb SATA HD raid0][Seagate 250 GB SATA2 HD][Sony Bravia 40' 1080p LCD HDTV][NEC 3520a DVD+-DLw][Windows 7 Ultimate x64][Rosewill 1000w]
fivefeet8 is offline   Reply With Quote
Reply


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


Similar Threads
Thread Thread Starter Forum Replies Last Post
Arma 3 E3 hands-on preview: 8 important questions answered News Archived News Items 0 06-08-12 05:20 AM
Enhancing Windows 8 for multiple monitors News Archived News Items 0 05-16-12 01:40 AM
FastM, AdrianHall, Questions About Leadtek TV 2000 Essense General Hardware 3 09-29-02 11:15 PM
multiple cards, insmod, modules.conf gseidman NVIDIA Linux 3 09-27-02 01:07 PM
Multiple OpenGL applications and NVidia gbrauer NVIDIA Linux 7 09-01-02 04:47 PM

All times are GMT -5. The time now is 04:08 PM.


Powered by vBulletin® Version 3.7.1
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 1998 - 2014, nV News.