CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
mauzilla
mauzilla

Reputation: 3592

PDO Move row from one database to another

I have 2 seperate databases with the same structure. I want to move a selected set of rows from one table to another. I am using PDO to select all the rows, then in a while loop I am assuming that I can insert each row into the new table then delete it from the old table.

With normal mysql this is quite a simple task, but I want to use namespaces so that I can easily modify the table structure ect. I have the following working but it does not move the entry to the new table:

 try {
        $sql = "SELECT * FROM `calls` WHERE `calls`.`status`=0 AND `calls`.`stage` < 4 AND `calls`.`answer` < (NOW() - INTERVAL 10 MINUTE)";
        $query = $this->staging->query($sql);
        while($row = $query->fetch(PDO::FETCH_ASSOC)) {
            $sql = "INSERT INTO `table` (`field1`,`field2`) VALUES (?,?)";
            $query = $this->production->prepare($sql);
            $query->execute($array);
        }
    }
    catch(PDOException $e) {
        $this->informer("FATAL", "Unable to process broken IVR surveys. Error: ".$e->getMessage());
    }

Upvotes: 0

Views: 1739

Answers (3)

Brian
Brian

Reputation: 8616

Another approach is using federated tables in MySQL to do what you're trying to do. There are some limitations.... but here are the docs to get you going...

http://dev.mysql.com/doc/refman/5.0/en/federated-use.html http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html

Upvotes: 0

deceze
deceze

Reputation: 522085

It likely does not work because you are using the question mark placeholder style in the prepared statement, but are passing a named array to execute.

If you name the parameters instead, it should work:

$sql = "INSERT INTO `table` (`field1`,`field2`) VALUES (:field1, :field2)";

Upvotes: 0

dynamic
dynamic

Reputation: 48091

You can do it without a while:

INSERT INTO db1.table (fields) SELECT fields FROM db2.table

Of course both databases must be on the same machine

Upvotes: 4

Related Questions