using SELECT * INTO to duplicate a record into another database

I am trying to duplicate a table from one database into another database. The original table contains static data. I need to be able to manipulate the data in the 'copied table'. This process needs to be dynamic as I will be producing multiple copies, one for each user.

At present I am using this code on a PHP call. It is meant to load the relevant data table, select all the data in the table and then create a new table in the player_missions database using the $Player variable as a new table name.

define('HOSTNAME','localhost:3306');  
define('DB_USERNAME','root');  define('DB_PASSWORD','');
define('DB_NAME','geo_locations');  global $conn;
$conn = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
  
$Player=$_SESSION["Player_ID"]; $geo_loc='C3B3';

$sql="SELECT * INTO $Player IN 'player_missions.mdb' FROM $geo_loc";

if(mysqli_query($conn, $sql)){} else{echo "ERROR: Was not able to execute $sql. " . mysqli_error($conn);}

As I am sure you can tell, my SQL knowledge is pretty poor but my extended efforts to find a solution have not been fruitful.

This is the error message:

ERROR: Was not able to execute SELECT * INTO 
805361725171703141241123 IN 'player_missions.mdb' FROM C3B3. You
have an error in your SQL syntax; check the manual that 
corresponds to your MariaDB server version for the right syntax 
to use near '805361725171703141241123 IN 'player_missions.mdb' 
FROM C3B3' at line 1

I have visited W3 and other sites but not been able to resolve.

Solution

To copy data from one table to another, MySQL syntax doesn’t support the SELECT * INTO statement with an external database in the way it's currently structured. Instead, you need to use CREATE TABLE with SELECT or INSERT INTO for the data transfer. Below is the revised code:

<?php
// Database connection settings
define('HOSTNAME', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'geo_locations');

// Connect to the primary database
$conn = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die("Connection error: " . mysqli_connect_error());

// Get player ID from session and original table name
$Player = $_SESSION["Player_ID"];
$geo_loc = 'C3B3';

// Set the new database and table name dynamically
$new_db = 'player_missions';
$new_table = $Player;

// Step 1: Create the new table in the target database
$createTableSQL = "CREATE TABLE `$new_db`.`$new_table` LIKE `$geo_loc`";

if (mysqli_query($conn, $createTableSQL)) {
    echo "Table created successfully. ";
} else {
    echo "Error creating table: " . mysqli_error($conn);
    exit;
}

// Step 2: Insert data from the original table into the new table
$insertDataSQL = "INSERT INTO `$new_db`.`$new_table` SELECT * FROM `$geo_loc`";

if (mysqli_query($conn, $insertDataSQL)) {
    echo "Data copied successfully!";
} else {
    echo "Error copying data: " . mysqli_error($conn);
}

mysqli_close($conn);
?>


Table Creation: We dynamically set the table name with the $Player variable and use the SQL statement:

CREATE TABLE `new_db`.`new_table` LIKE `geo_loc`

This creates a new table in the player_missions database with the same structure as the geo_loc table but does not copy data.