douhuo1738 2013-02-12 06:04
浏览 28
已采纳

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

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.

  • 写回答

1条回答 默认 最新

  • douzhun5971 2013-02-12 06:12
    关注

    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);
    }
    ?>
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择
  • ¥15 这款软件是什么?需要能满足我的需求
  • ¥15 SpringSecurityOauth2登陆前后request不一致
  • ¥15 禅道二次开发编辑版本,上传不了发行包