Posted by Ciaran Byrne on 05/17/07 10:39

I'm trying to move data from one or more tables to identical table(s)
in a different database, likely on a different server. This prevents
me from using INSERT..SELECT, so up until now I've done a select on
the source table and looped through the results inserting them
individually into the target table. I thought that a prepared
statement might improve the performance, but I can't get it to work.
Below is some sample code, and I imagine it's in the binding of
$arrRowData (using call_user_func_array) that's not working. Any ideas
welcome.

<?php

$strSourceDB = "dbSource";
$strTargetDB = "dbTarget";

$objSourceDB = mysqli_connect("", "", "", $strSourceDB);
$objTargetDB = mysqli_connect("", "", "", $strTargetDB);

$strSourceTable = "tblA";
$strTargetTable = "tblB";

$arrRowData = array();
$strColumnNames = "";
$strParamTypes = "";
$strPlaceholders = "";

// Get column names and their properties

$result = $objSourceDB->query("SHOW COLUMNS FROM $strSourceDB.
$strSourceTable") or die("Query: ".$objSourceDB->error);

while ($row = $result->fetch_row())
{
$arrRowData[] = "";

$strColumnNames .= $row[0].", ";

if (strpos($row[1], "int") || strpos($row[1], "bool"))
$strParamTypes .= "i";
elseif (strpos($row[1], "blob")) $strParamTypes .= "b";
elseif (strpos($row[1], "big") || strpos($row[1], "double"))
$strParamTypes .= "d";
else $strParamTypes .= "s";

$strPlaceholders .= "?, ";
}

$strColumnNames = substr($strColumnNames, 0, strlen($strColumnNames)
- 2);
$strPlaceholders = substr($strPlaceholders, 0,
strlen($strPlaceholders) - 2);

// Create prepared statement

$objStatement = $objTargetDB->stmt_init() or die($objStatement-
>error);

$objStatement->prepare("INSERT INTO $strTargetDB.$strTargetTable
($strColumnNames) VALUES ($strPlaceholders)") or die("Prepare: ".
$objStatement->error);

array_unshift($arrRowData, $strParamTypes);

if (!call_user_func_array(array(&$objStatement, "bind_param"),
$arrRowData)) die("Bind Param: ".$objStatement->error);

array_shift($arrRowData);

// Select and Insert rows

$objResult = $objSourceDB->query("SELECT $strColumnNames FROM
$strSourceDatabase.$strSourceTable WHERE DateTime <=
DATE_SUB(CURDATE(), INTERVAL 90 DAY)") or die("Query: ".$objSourceDB-
>error);

while ($arrRowData = $objResult->fetch_row())
{
$objStatement->execute() or die("Execute: ".$objStatement-error);
}

?>

[Back to original message]