Click here to Skip to main content
16,017,638 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

I want to make one script which executes on daily bases and checks each single table of the database called DB1 and then match with the other database DB2's tables (Both database have similar tables and table fields.)

So is there any simple way to do so?

What I have tried:

PHP
$copiedProducts = array();
foreach ($erpXML->store as $mainstore) {
	$mainLocation = $mainstore->attributes()->id;
	$mainLocation1 = (array)$mainLocation;
	$dbConn = $mainstore->db;
	$dbcon = new MeekroDB(CFG::$host, CFG::$user, CFG::$password, $dbConn);

	$syd_products = $dbcon->query("SELECT * FROM " . CFG::$tblPrefix . "product");
	foreach ($erpXML->store as $tostore) {
		$toLocation = $tostore->attributes()->id;
		$toDBConn = $tostore->db;
		if(strtolower($mainLocation) != strtolower($toLocation)) {
			$newLac = (array)$toLocation;
			//$copiedProducts[$mainLocation1[0]] = $mainLocation1[0];
			copy_product($syd_products, $toDBConn, $mainLocation1[0], $newLac[0]);
		}
	}//exit;
}

//pre($copiedProducts,1);
//exit;
function copy_product($products, $db, $fromdb, $newLaca) {
	global $copiedProducts, $cnt;
	$cnt = 0;
	$dbObj = (array)$db;

	$dbCon = new MeekroDB(CFG::$host, CFG::$user, CFG::$password, $db);
	foreach($products as $key=>$val) {
		$store_prd = $dbCon->queryFirstRow("SELECT id, name FROM " . CFG::$tblPrefix . "product WHERE pid = '" . $val['pid'] . "'");
		$newArrPrdQty = $newArr = array();

		if(empty($store_prd) && !preg_match("/ST-(.*)/", $val["pid"])) {
			$copiedProducts[$fromdb][$cnt]["todb"] = $newLaca; 
			$copiedProducts[$fromdb][$cnt]["pid"] = $val["pid"]; 
			$cnt++;
			//return true;

//			$sortOrder = $dbCon->queryFirstRow("SELECT sort_order FROM " . CFG::$tblPrefix . "product order by sort_order desc ");
			$sortOrder = $dbCon->queryFirstRow("SELECT MAX(sort_order) as sort_order FROM " . CFG::$tblPrefix . "product");
			
			$newArr["pid"] = $val["pid"];
			$newArr["name"] = $val["name"];
			$newArr["description"] = $val["description"];
			$newArr["weight"] = $val["weight"];
			$newArr["sort_order"] = $sortOrder["sort_order"] + 1;
			$newArr["is_deleted"] = $val["is_deleted"];
			$newArr["created_date"] = date("Y-m-d H:i:s");
			$newArr["ignore_customer_app"] = $val["ignore_customer_app"];
			$newArr["featured_prod"] = $val["featured_prod"];
			//pre($newArr,1);

			$dbCon->insert(CFG::$tblPrefix . "product", String::processString($newArr));
			$insertedId = $dbCon->insertId();
			$newArrPrdQty["pid"] = $val["pid"];
			$newArrPrdQty["product_id"] = $insertedId;
			$newArrPrdQty["qty"] = "0";
			$newArrPrdQty["price"] = "0.000000";
			$newArrPrdQty["is_deleted"] = "";
			$newArrPrdQty["created_date"] = date("Y-m-d H:i:s");
			$newArrPrdQty["updatable"] = "1";
			$dbCon->insert(CFG::$tblPrefix . "product_qty", String::processString($newArrPrdQty));
		}
	}//exit;
}
exit;
Posted
Updated 30-May-16 15:01pm
v3
Comments
CHill60 4-May-16 7:47am    
What do you mean by Match? Are you trying to synchronise the databases? Are you trying to backup DB1?
What are you actually trying to achieve - we may be able to help you with your search
Member 10268466 4-May-16 8:10am    
Hello, Thanks for the reply, actually I have multiple locations, and all locations have different database named DB1 & DB2 etc... Now the issue is that admin enters some data in only one location, and now main concern is that, So i need to create one script which just fetch the data from the DB1 database table and compare it to the other database DB2 and if the record is new then and only then it will add to new DB2. I have coded it well. But it I want to fix is very optimise way, or say if its possible by mysql quesries or any other optimised way

Based on your comment then you are trying to synchronize the databases.

Documentation - MySQL :: MySQL Workbench Manual :: 9.5.1 Database Synchronization[^]

Good answer from Matt Healy on this post - data synchronization - Sync two MySQL databases in two different locations - Database Administrators Stack Exchange[^]

And some more suggestions here - php - How to keep two MySQL databases in sync? - Stack Overflow[^]

All of the offerings consider "optimisation". As this is dependent on your setup, volumes of data etc what is optimal for you may not be optimal for someone else. This is a non-trivial task so I advise reading up on all the options before making a decision.
 
Share this answer
 
use can use mysql. it has master -client
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900