PHP：将MySQL数据库表复制到具有附加列的新数据库表中

Question

I am looking for a way to copy an existing database with multiple tables into a new database with the same tables and columns + some additional columns. So far so good. If I just copy the database to a new database with the same amount of tables and columns I am doing it like this:

+---------+---------+---------+
| TABLE 1 |         |         |
+---------+---------+---------+
| Col1    | Col2    | Col3    |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
+---------+---------+---------+

copy to:

+---------+---------+---------+
| TABLE 2 |         |         |
+---------+---------+---------+
| Col1    | Col2    | Col3    |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
+---------+---------+---------+

Code:

public function loadDB($db1,$db2){
    $this->db->prepare("use ".$db1."");
    $sqlshow = "SHOW TABLES ";
    $statement = $this->db->prepare($sqlshow);
    $statement->execute();
    $tables = $statement->fetchAll(PDO::FETCH_NUM);

    foreach($tables as $table){
        $sql[] = "INSERT INTO ".$db2.".".$table[0]." SELECT * FROM ".$db1.".".$table[0]."; ";
    }
    $sqlState = implode(' ', $sql);
    $insertStatement = $this->db->exec($sqlState);
    return $insertStatement?$insertStatement:false;
}

This code works and my database is copied successfully with all my tables and values inside my tables. What I need now is a working example of how am I able to copy the database to a new database where all tables have four additional columns like this:

+---------+---------+---------+
| TABLE 1 |         |         |
+---------+---------+---------+
| Col1    | Col2    | Col3    |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
+---------+---------+---------+

copying to:

+---------+---------+---------+-----------+-----------+-----------+-----------+
| TABLE 2 |         |         |           |           |           |           |
+---------+---------+---------+-----------+-----------+-----------+-----------+
| Col1    | Col2    | Col3    | Counter   | LoadDay   | User     | UserNew      |
| Value 1 | Value 2 | Value 3 | NEW VALUE | NEW VALUE | NEW VALUE | NEW VALUE |
| Value 1 | Value 2 | Value 3 | NEW VALUE | NEW VALUE | NEW VALUE | NEW VALUE |
| Value 1 | Value 2 | Value 3 | NEW VALUE | NEW VALUE | NEW VALUE | NEW VALUE |
+---------+---------+---------+-----------+-----------+-----------+-----------+

Code (what I´ve tried so far):

public function loadDB($db1,$db2,$condition){
    $this->db->prepare("use ".$db1."");
    $sqlshow = "SHOW TABLES ";
    $statement = $this->db->prepare($sqlshow);
    $statement->execute();
    $tables = $statement->fetchAll(PDO::FETCH_NUM);

    foreach($tables as $table){
        $sqlshow2 = "SHOW COLUMNS FROM ".$table[0]." ";
        $statement = $this->db->prepare($sqlshow2);
        $statement->execute();
        $columns = $statement->fetchAll(PDO::FETCH_NUM);

        foreach($columns as $column){
            $sql[] = "INSERT INTO ".$db2.".".$table[0]." SELECT ".$column[0]." FROM ".$db1.".".$table[0]."; ";
        }
        $sql[] .= "INSERT INTO ".$db2.".".$table[0]." (`Counter`, `LoadDay`, `User`, `UserNew`) VALUES ('1', '".date("Y-m-d H:i:s")."', '".$condition."', '".$condition."')";
    }
    $sqlState = implode(' ', $sql);
    var_dump($sqlState);
    $insertStatement = $this->db->exec($sqlState);
    return $insertStatement?$insertStatement:false;
}

The creation of the databases are working (not visible in my posted code here). I only get no values copied to my new tables inside my new database. What am I doing wrong here?

Answer 1

Your final SQL query comes out wrong. I suggest changing your code to somthing like this:

foreach($tables as $table){
    $sqlshow2 = "SHOW COLUMNS FROM ".$table[0]." ";
    $statement = $this->db->prepare($sqlshow2);
    $statement->execute();
    $columns = $statement->fetchAll(PDO::FETCH_NUM);

    $sql[] = "INSERT INTO ".$db2.".".$table[0]." SELECT * , '1', '".date("Y-m-d H:i:s")."', '".$condition."', '".$condition."'" . " FROM ".$db1.".".$table[0]."; ";

}