Copy MySQL Table Rows in PHP

This little chunk of PHP grabs all the rows from the source table and inserts them into an (identical) destination table. It's useful for backups or an automated process that you don't want to always be logging into phpMyAdmin to do. If you don't want the new table to be cleared, you can remove the TRUNCATE TABLE query at the beginning.

<?php

$dbaddress = "localhost" ;
$dbname = "db1" ;
$dbuser = "username" ;
$dbpw = "password" ;
$oldtable = "members" ;
$newtable = "members_copy" ;

$link = mysql_connect($dbaddress, $dbuser, $dbpw);
mysql_select_db($dbname, $link) or die("Error: Could not connect!\n");

$q = array();
$columns = array();

$resultc = mysql_query("SHOW COLUMNS FROM `$oldtable`");
while ($rowc = mysql_fetch_array($resultc)) {
    $columns[] = $rowc[0];
}

$q[] = "TRUNCATE TABLE `$newtable`" ;
$result = mysql_query("SELECT * FROM `$oldtable`");
while ($row = mysql_fetch_array($result)) {
    $query = "INSERT INTO `$newtable` (" ;
    $comma = "" ;
    foreach ($columns as $column) { $query .= $comma."`".$column."`"; $comma = ","; }
    $query .= ") VALUES (" ;
    $comma = "" ;
    foreach ($columns as $column) { $query .= $comma."'".addslashes($row[$column])."'"; $comma = ","; }
    $query .= ")" ;
    $q[] = $query;
}

foreach ($q as $query) {
    echo "$query\n" ;
    mysql_query($query,$link) or die(mysql_error($link));
}

mysql_close($link);

?>

Comments

Loading…

This post was published on March 1st, 2009 by Robert James Reese in the following categories: MySQL and PHP. Before using any of the code or other content in this post, you must read and agree to our terms of use.