php to merge mysql tables

[compudude86]

hello,
i have a web interface built to show the output of several tables that distributors can edit, now i need to find a script that can take all those tables, and merge/update them into a single table on a second database. any ideas?

 

[compudude86]

sorry, i must add that this is a mysql database, and there are give or take 10 tables on database "dist" and one public book on database "book

 

[jpadie]

since you are posting in the php forum, I assume you want a php solution.

do all the tables have identical columns?

 

[compudude86]

yes, they are all identical columns in each table.

 

[jpadie]

use with care. i have not tested this.

do this for the first database and then for the second separately. i don't think you can merge across databases using more or less pure sql like this but you can ask in the mysql forum.

if no joy then open two db connections to each of the two merged tables. use a select * query on one to get the full recordset and then iterate that recordset doing an insert into the other.

$tables = array("table1","table2","table3"); //etc
$pk = "";//name of autoincrementing primary key [assumption]
mysql_query("Create table newtable like table1");
foreach ($tables as $table){
 mysql_query("Insert into newtable select * from $table on duplicate key update $pk = last_insert_id()+1");
}

 

[compudude86]

see, i dont want to create a new table, i want it to take all the tables on dist, and apply an update to database/table "bookroducts". basically i need a php app that i can click an update button that will run all this "behind the scenes

 

[jpadie]

i'd still do what i said.

use mysql to aggregate all the tables that are on dist,
use php to grab the recordset in the new table and bung it into bookroducts.

then just add a drop table to kill the newly aggregated version.

the php bit might look something like this (minus the drop table):

<?
mysql_connect(	$databaseserver, 
				$dbUsername, 
				$dbPassword	);

$client = mysql_select_db("dist");
$host = mysql_select_db("book");

$clientquery = mysql_query($client, $client);
$inserts = array();
while ($row = mysql_fetch_assoc($clientquery)){
	$insert = "";
	foreach ($row as $col=>$val){
		$insert .= " $col='$val', ";
	}
	$insert = "Insert 
				into products 
			   set
			". rtrim($insert, ",") . 
			" on duplicate update $pk = last_insert_id()+1 ";
	mysql_query($insert, $host);
}
?>