CODEWITHSUNDEEP
phpmysqldatabase
oquiroz
oquiroz

Reputation: 95

How to copy the tables of an existing MySQL database to a new database without the records?

I'm new to MySQL. Right now I'm trying to replicate an already existing PHP website with a SQL database both of my property in a new web hosting. I wish to create a new SQL database with the same structure of the old website database and connect the new PHP website with the new database.

I already know how to connect the PHP file with the database but I don't know how to copy a database tables to another.

Upvotes: 0

Views: 1313

Answers (2)

Tomasz Kapłoński
Tomasz Kapłoński

Reputation: 1378

If you want to do this without use of tools like phpMyAdmin you can use SHOW TABLES; and SHOW CREATE TABLE table_name; queries to iterate through the db you want to copy. So if you put this into PHP script you get something like that:

<?php 

$host_name = "localhost";
$user_name = "xfiddlec_user";
$pass_word = "public";
$database_name = "xfiddlec_max";
$port = "3306";

$first_connect = new mysqli($host_name, $user_name, $pass_word, $database_name, $port);
//$second_connect = new mysqli($host_name2, $user_name2, $pass_word2, $database_name2, $port2);
$sql = "SHOW TABLES";

$result = $first_connect->query($sql);
if (($result) && ($result->num_rows > 0))
{
//convert query result into an associative array
while ($row = $result->fetch_row())
{
    $sql2 = "SHOW CREATE TABLE {$row[0]}";
    $res = $first_connect->query($sql2)->fetch_row();
    //echo "<pre>"; print_r($res); echo "</pre>";
    $createSQL = $res[1];
    $second_connect->query($createSQL);
    echo $createSQL;
}

}

I have commented some parts so that you can try it on PHP Fiddle here. However though it's better to use some tool if you can. :)

Upvotes: 0

Nick
Nick

Reputation: 6025

You want to use a utility like phpMyAdmin to copy the database directly. You don't want to do it programmatically using PHP. Have a look at this.

Upvotes: 2

Related Questions