How to create a new ID if the ID already exists in contacts table?

Question

I have been trying to create a script to copy over data from the contacts table on dababase 1 to database 2.

I have finally come up with the below script that allows me to copy over the data.

The issue that I am facing is that if the ID already exists in database 2 which prevents avoid over writing data, it gives me a warning that the ID already exists.

This is where I am stuck, is there anyway to tell the script to create a new ID if the ID already exists please?

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



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

          $res=mysqli_query($mysqli,$sql);
    
          if (mysqli_num_rows($res) > 0) {
            // output data of each row
            $row = mysqli_fetch_assoc($res);
            if($id==$row['id'])
            {
    echo "Already copied"; //error message if already copied 
                    
            }
    
           } else{
    
     
       
    $query=mysqli_query($mysqli,"INSERT INTO exdigita_vadercic.contacts SELECT * FROM  contacts WHERE id =$id");
    
    echo "Successfully copied"; 
    }

Do the IDs of the records in the new table matter at all? If not, and the column is auto-increment - then you can simply pass NULL for the ID column value, or leave that column out of the list of columns you want to insert into. (Of course with SELECT * this won't work, you will have to list all the other columns explicitly.) — CBroe, Jul 29 '22 at 11:23
**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, Jul 29 '22 at 11:25
https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use it again. — ADyson, Jul 29 '22 at 11:25
Honestly I look at the question and I wonder how much sense make all those recommendation. I really hope that php script won't be sitting as an url waiting for a request. Secondly the practice of managing ids like that is pretty bad. Thirdly it would be handled much better if performed bulk... but who am I to judge the scenario. But in terms of solutions.. CBroe gave a viable suggestion: since it's mysql there's a chance the PK is as autoincremented value so just write the INSERT INTO statement containing the list of columns explicitely (except the id) both in the INTO clause and SELECT — Diego D, Jul 29 '22 at 11:27

score 1 · Answer 1 · answered Jul 29 '22 at 12:05

An easy solution

A very easy solution to this problem is to define a numeric id as primary key and specify it to be auto_increment and never specify it in your insert command, so you will always (except overflows) get an unused value for the field automatically.

Problems with your query

It is a very bad practice for the select clause to use

select *

because you might select unnecessary columns or columns in the wrong order.

Always try to specify the columns you intend to specify in your target table in the order you intend to specify them and write them explicitly in your select clause.

The part of id =$id is prone to SQL injection, which is a serious vulnerability. It is advisable to read about protecting yourself against this vulnerability by using parameterized queries. However, in your specific case, if $id is expected to be an integer number, then you can just do

$id = intval($id);

before you use it in your queries and that protects you against injecting scripts into the value of $id. So, here protecting yourself is easier than in general, but nevertheless, look into this topic, so in the future you will write safer code.

A slightly more complicated solution

You can do the equivalent of:

INSERT INTO exdigita_vadercic.contacts(id, col1, col2, col3)
SELECT
CASE
    WHEN c.id IS NULL THEN 1234
    ELSE NULL
END AS id, 'col1value', 'col2value', 'col3value'
FROM (SELECT 1 AS dummyvalue) dummytable
LEFT JOIN exdigita_vadercic.contacts c
ON c.id = 1234;

The above assumes that you have auto_increment on your id. If you do not have this specification, then you can join with the maximum id, like this:

INSERT INTO exdigita_vadercic.contacts(id, col1, col2, col3)
SELECT
CASE
    WHEN c.id IS NULL THEN 1234
    ELSE max_record.max_id + 1
END AS id, 'col1value', 'col2value', 'col3value'
FROM (SELECT 1 AS dummyvalue) dummytable
LEFT JOIN exdigita_vadercic.contacts c
ON c.id = 1234
LEFT JOIN (SELECT MAX(ID) AS max_id FROM exdigita_vadercic.contacts) max_record
ON 1 = 1;

How to create a new ID if the ID already exists in contacts table?

1 Answers1

An easy solution

Problems with your query

A slightly more complicated solution