在php脚本中创建数据库表的克隆

Question

I have to create a clone of database table. I worked with wordpress database and i want to create a clone of WP-options table. How can i create a clone of a particular table in database. I found some code

set_time_limit(0);

$username = 'XXXXXX';
$password = 'YYYYYY';
$hostname = 'ZZZZZZ';
$database = 'AAAAAA';

try {
    $pdo = new PDO("mysql:host={$hostname};dbname={$database}", $username, $password);
}
catch(PDOException $e) {
    die("Could not connect to the database
");
}

echo '<pre>';
$stmt1 = $pdo->query('SHOW TABLES', PDO::FETCH_NUM);
foreach($stmt1->fetchAll() as $row) {
    $stmt2 = $pdo->query("SHOW CREATE TABLE `$row[0]`", PDO::FETCH_ASSOC);
    $table = $stmt2->fetch();
    echo "{$table['Create Table']};

";
}
echo '</pre>';

But i cant understand how can i get the database in a folder which path i given.

Answer 1

I had same issue before and from googling of couple of hours i end up with below code.

Hope that will help.

Well geeks sorry for using mysql_** as its deprecated.

<?php
$DBIUser = 'someuser';
$DBIPass = 'thepassword';

$NewUser = 'someloser';
$NewPass = 'thepassword';

$oldServer = 'my crappy old mysql server domain';
$newServer = 'localhost';

if ($argv[0] > " ")
{
    $dbname = $argv[1];
    echo "Starting copy of the $argv[1] database.
";
    $dbpre = mysql_connect($oldServer, $DBIUser, $DBIPass);
    mysql_select_db($dbname, $dbpre);
    $sql = "SHOW TABLES FROM $dbname";
    echo $sql."
";
    $result = mysql_query($sql);

    if (!$result)
    {
        echo "DB Error, could not list tables
";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $dbtbl = mysql_connect($oldServer, $DBIUser, $DBIPass);
    mysql_select_db($dbname, $dbpre);
    $dbnew = mysql_connect($newServer, $NewUser, $NewPass);
    mysql_select_db("mysql", $dbnew);

    $res2 = mysql_query("CREATE DATABASE IF NOT EXISTS ".$dbname,$dbnew);
    if (!$res2)
    {
            echo "DB Error, could not create database
";
            echo 'MySQL Error: ' . mysql_error();
            exit;
    }
    mysql_select_db($dbname, $dbnew);


    if($result === FALSE)
    {
        die(mysql_error());
    }

    $f = fopen($dbname.'.log', 'w');
    fwrite($f, "Copy all tables in database $dbname on server $oldServer to new database on server $newServer.

");
    while ($row = mysql_fetch_row($result))
    {
        echo "Table: {$row[0]}
";
        fwrite($f, "Table ".$row[0]."
");
        $tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $row[0]  ",$dbtbl));
        $createsyntax = "CREATE TABLE IF NOT EXISTS ";
        $createsyntax .= substr($tableinfo[1], 13);

        mysql_query(" $createsyntax ",$dbnew);

        $res = mysql_query("SELECT * FROM $row[0]  ",$dbpre); // select all rows
        $oldcnt = mysql_num_rows($res);
        echo "Count: ".$oldcnt." - ";

        $errors = 0;
        while ($roz = mysql_fetch_array($res, MYSQL_ASSOC) )
        {
          $query =  "INSERT INTO $dbname.$row[0] (".implode(", ",array_keys($roz)).") VALUES (";
          $cnt = 0;
          foreach (array_values($roz) as $value)
          {
            if ($cnt == 0)
            {
              $cnt++;
            } else
            {
              $query .= ",";
            }
            $query .= "'";
            $query .= mysql_real_escape_string($value);
            $query .= "'";

          }
          $query .= ")";

          $look = mysql_query($query,$dbnew);
          if ($look === false)
          {
            // write insert to log on error
            $errors = $errors + 1;
            fwrite($f, mysql_error()." - ".$query."
");
          }

        }
        $sql = "select count(*) as cnt from $dbname.$row[0] ";
        $res = mysql_query($sql, $dbnew);
        $roz = mysql_fetch_array($res);
        echo $roz['cnt']." - Errors: ".$errors."
";
        fwrite($f, "Old Record Count: ".$oldcnt." - New Record Count: ".$roz['cnt']." - Errors: ".$errors."
");
        fwrite($f,"End table copy for table $row[0].

");

    }
    fclose($f);
}
else
{
    var_dump($argv);
}
?>