Trying to make prepared statements work in a loop

[Ciaran Byrne]

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);
}

?>

[Toby A Inkster]

Ciaran Byrne wrote:

$objStatement->execute()

You need to pass some parameters to execute().

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux

[Ciaran Byrne]

On May 17, 5:13 pm, Toby A Inkster <[e-mail]>
wrote:

Ciaran Byrne wrote:

$objStatement->execute()

You need to pass some parameters to execute().

--
Toby A Inkster BSc (Hons) ARCShttp://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux

http://uk.php.net/manual/en/function...mt-execute.php

says:

Object oriented style (method):
class mysqli_stmt {
bool execute ( void )
}

i.e. $objStatement->execute() has no parameters.

[Toby A Inkster]

Ciaran Byrne wrote:

http://uk.php.net/manual/en/function...mt-execute.php

Oh yes -- I missed that you were using MySQLi -- had assumed PDO.

In which case, the problem is probably with call_user_func_array passing
its array elements by value, but bind_param expecting to receive its
arguments passed by reference.

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux