Merge/Join Fields and Rows of Tables in MySQL

I was asked the other day by someone about some software that could Join/Merge two tables (fields and rows). I thought it’d be pretty easy with some MySQL query but couldn’t find any solution. Then I turned to google in search of any software or any other source that could help me out… but still couldn’t find one. The closest I could get was the case of MySQL Merge Table tool (that merges the records of two identical tables) or the UNION which also required for the number of columns and types to be identical. This wasn’t even remotely what I had desired. So I thought of writing my own script…. Here’s my meager effort.

mysql_connect("localhost", "root", "") ;
mysql_select_db("aaatest") ;

$newTable = "tbltest_12" ;
$newTablePK = "id" ;
$joinTable['left'] = "tbltest1" ; // this table will be on the left side of the join. As a rule, the table with most records whould be set here otherwise some records will be left out.
$joinField['left'] = "id" ; // field on the basis of which this table will be joined to the other table
$joinTable['right'] = "tbltest2" ;
$joinField['right'] = "id" ; // join field for the table on the right side.
$includePrevPK = true ; // if set to true, primary keys of previous tables will also be included in new table
$prefixTableName = true ; // if set to true, previous tablename will be prefixed to every fieldname

$strCreate = "CREATE TABLE `$newTable` (
`$newTablePK` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ," ; // a new primary key for the new table

foreach($joinTable as $side=>$table){
	$str = "SHOW COLUMNS FROM ".$table ;
	$rs = mysql_query($str) or die(mysql_error()) ;
	while($row = mysql_fetch_assoc($rs)){
		if($row['Key'] == "PRI"){ // incase of primary key
			if($includePrevPK){
				$arrFieldsSELECT[] = "`".$joinTable[$side]."`.`".$row['Field']."` AS `".$joinTable[$side]."_".$row['Field']."`" ;
				// Primary Key will be prefixed with tablename in any case
				$arrFieldsINSERT[] = "`".$joinTable[$side]."_".$row['Field']."`" ;
				$strCreate .= "`".$joinTable[$side]."_".$row['Field']."` " ;
			}else{
				// incase Primary Key is not to be included in the new table, skip the rest of the loop iteration
				continue;
			}
		}else{
			// prefix tablename to fieldfiend
			$arrFieldsSELECT[] = "`".$joinTable[$side]."`.`".$row['Field']."` AS `".($prefixTableName ? $joinTable[$side]."_" : "").$row['Field']."`" ;
			$arrFieldsINSERT[] = "`".($prefixTableName ? $joinTable[$side]."_" : "").$row['Field']."`" ;
			$strCreate .= "`".($prefixTableName ? $joinTable[$side]."_" : "").$row['Field']."` " ; 
		}
		$strCreate .= $row['Type']." " ;
		$strCreate .= ($row['Null']!="No" ? "NULL" : "")." " ;
		$strCreate .= ($row['Default']!="" ? "DEFAULT '".$row['Default']."'" : "") ;
		$strCreate .= " ," ;
	}
}
$strCreate = substr($strCreate, 0, strlen($strCreate)-1) ; // to remove a (,) comma from the end of string
$strCreate .= ")TYPE=MyISAM ;" ;
// a new table is created here
mysql_query($strCreate) or die(mysql_error());

// records from the two tables will be fetched here and a new INSERT query will be made for insertion in new table
$strFieldsSELECT = implode(",", $arrFieldsSELECT) ;
$strSelect = "
SELECT $strFieldsSELECT FROM `".$joinTable['left']."`
LEFT JOIN `".$joinTable['right']."` ON `".$joinTable['left']."`.`".$joinField['left']."` = `".$joinTable['right']."`.`".$joinField['left']."`" ;

$rs = mysql_unbuffered_query($strSelect) or die(mysql_error()) ;
while($row = mysql_fetch_assoc($rs)){
	$strVal = implode("','", $row) ;
	$strValue .= "('".$strVal."')," ;
}
$strInsert = implode(",", $arrFieldsINSERT) ;
$strInsert = "INSERT INTO `$newTable` (".$strInsert.") VALUES" ;
$strValue = substr($strValue, 0, strlen($strValue)-1) ; // to remove a (,) comma from the end of string
echo $strInsert .= $strValue ;
mysql_query($strInsert) or die(mysql_error()) ;

I’ve used mysql_unbuffered_query() in this code, because, due to JOIN, the resultset could get to enormous size. mysql_unbuffered_query() doesn’t keep the whole resultset of the query in the buffer. It fetches one row at a time, thus reducing the memory consumption. This function is very effective for large resultsets.

Hope it helps someone. Also please leave a comment as your feedback, so that I can further improve this code. Its improved version will be online as soon as I come up with any brilliant ideas.

Powered by ScribeFire.