By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,983 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,983 IT Pros & Developers. It's quick & easy.

Trying to make prepared statements work in a loop

P: n/a
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);
}

?>

May 17 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
May 17 '07 #2

P: n/a
On May 17, 5:13 pm, Toby A Inkster <usenet200...@tobyinkster.co.uk>
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.

May 18 '07 #3

P: n/a
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
May 18 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.