Duplicate ID issue when trying to copy table row

[EarthDay]

Hello all,

I am trying to create a script to allow me to copy a table row from one database to another but I am having an issue creating new ID's when a duplicate is found. It just tells me that the ID has already been copied if found in the second database. This is also a safety feature to avoid data been over written.

 

URL is page.php?id=12

The column for ID is set to AI. 

$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName); 
 
$id = $_GET['id'];

$sql="select * from db2.contacts where ($id = intval($id));";//  check id is already copied 

      $res=mysqli_query($mysqli,$sql);

      if (mysqli_num_rows($res) > 0) {
        $row = mysqli_fetch_assoc($res);
        if($id==$row['id'])
        {
echo "Already copied"; //error message if already copied 
                
        }

       } else{

    $query=mysqli_query($mysqli,"INSERT INTO db2.contacts SELECT * FROM  db1.contacts WHERE id =$id");

echo "Successfully copied"; 
}

Any help would be gratefully appreciated as I am banging my head against the table lol

Cheers.

Edited July 30, 2022 by EarthDay

[Barand]

If you never want any data to be overwritten then you need always to insert a new record every time with a new id.

Ensure your table in the destination db has an auto_incrementing primary key, for example

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `studentname` varchar(20) DEFAULT NULL,
  `class_id` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Let's start with db1.student and db2.student tables

db1.student                                db2.student
+----+-------------+----------+            +----+-------------+----------+
| id | studentname | class_id |            | id | studentname | class_id |
+----+-------------+----------+            +----+-------------+----------+
|  1 | Peter       | A1       |            |  1 | Curly       | A1       |
|  2 | Paul        | A2       |            |  2 | Larry       | A2       |
|  3 | Mary        | A3       |            |  3 | Mo          | A3       |
+----+-------------+----------+            +----+-------------+----------+

To auto-generate a new key, the insert needs to exclude the id column. This means you cannot use "SELECT * ", you must define the column excepting the id. Similarly, the query must define the destination columns in the receiving table. So we have this (assuming we are connected to db1 as default db) ...
 

INSERT INTO db2.student (studentname, class_id)
SELECT studentname, class_id FROM student
WHERE id = 1

which, when executed, gives this in db2.student table

+----+-------------+----------+
| id | studentname | class_id |
+----+-------------+----------+
|  1 | Curly       | A1       |
|  2 | Larry       | A2       |
|  3 | Mo          | A3       |
|  4 | Peter       | A1       |
+----+-------------+----------+

 

[EarthDay]

Hi Barand,

Sorry for the late reply to this message.

Thank you so much, this has help me massively and got the script working.

🙂