Copying a database "test1" and making another database "test2"

PHP
1

I have a database which name is “test1”.
The database has two tables i.e, “person” and “city”.

The table “person” has some data like the following.

Ant the table “city” has some data like the following.

I can make a new database “test2” by copying the database “test1” with the tables and records using phpMyadmin.

I like to make a new database “test2” by copying the database “test1” in php without phpMyadmin.

In order to do that I made the code below and it works fine.

<?php
$dbName = 'test1' ; 
$options = [
    PDO:: ATTR_ERRMODE                                        => PDO:: ERRMODE_EXCEPTION,
    PDO:: ATTR_DEFAULT_FETCH_MODE          => PDO:: FETCH_ASSOC,
    PDO:: ATTR_EMULATE_PREPARES                 => false,
]; 

try { 
$dbc = new PDO("mysql:host=localhost;dbname=$dbName;charset=utf8", "root", "**********", $options); 
} catch(PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getcode()); 
} 

$oldDBname = 'test1' ;
$newDBname = 'test2' ; 

$dropDB=$dbc-> query("DROP DATABASE IF EXISTS $newDBname"); 
$createDB=$dbc-> query("CREATE DATABASE $newDBname"); 

$tbName = 'person' ; 
$create_table=$dbc -> query("CREATE TABLE if not exists 
$newDBname.$tbName LIKE $oldDBname.$tbName"); 
$inn_table=$dbc -> query("INSERT INTO $newDBname.$tbName SELECT * FROM $oldDBname.$tbName");
$tbName = 'city' ; 
$create_table=$dbc -> query("CREATE TABLE if not exists 
$newDBname.$tbName LIKE $oldDBname.$tbName"); 
$inn_table=$dbc -> query("INSERT INTO $newDBname.$tbName SELECT * FROM $oldDBname.$tbName") ;

However the code above should copy each table, the table “person” and the table “city” here, per table.
I like to directly copy the database “test1” as “test2” without copying each table per table.

The code below doesn’t work correctly but It help you understand what I want.

createDB=$dbc-> query("CREATE DATABASE $newDBname LIKE $oldDBname")

Is what I want possible with your help?

2

No, there is no such command in MySQL.

There are two ways to accomplish your request.

Put the table copying function in your already working script in a loop. Use a

SHOW TABLES

Query to get all tables in the database and loop through the result array.

Create a database as you do in your script. Then use the PHP exec command to run mysqldump to backup the db1 and restore it to db2

3

Have you considered using the mysqldump/mysqlimport commands? No real need for PHP here.

If you really want to do something under PHP then take a look at Doctrine’s Database Abstraction Layer Schema functionality.