MySQL allows you to copy data across tables and databases, using SELECT and INSERT statements. In some cases, you may need to copy a row and insert into the same table in MySQL. This is often required to create test data for quality checks or duplication of data. In this article, we will learn how to do this.
On a recent large-scale web application we were developing, I came across a need to clone complete rows from a MySQL table to the same table. Initially, we were having 2.5 million product rows in the table. Our requirement was to test our application performance to load it with 1 million products.
There were suggestions around the web to copy the rows into a new table and then copy them back to the original table to avoid duplicate key issues for indexed ID ( primary key ). But I just have a simple idea. Just the below query will do the job of cloning entire rows into the same table without conflicting primary key
There are many code snippets available online or on many other blogs and websites, but everyone cannot optimize your blog or website, so you need some optimized code snippets. So now checkout out the code snippet for your blog and website that will give you all features for your desired code. Now grab the ready-to-use code and paste it where you want.
Features:
- Light Weight.
- Pure PHP.
- Cross Browser.
- MySQLi Structure.
- Fully Customizable.
- Compitable WIth All PHP Versions.
How To Duplicate Or Copy/Past Entire Row in MySQL Same Table Using PHP?
There are a few easy and understandable steps to achieve your desired functionality that we are gonna share below. Follow each step perfectly.
PHP:
function DuplicateMySQLRecord($connection, $table, $id_field, $id) { $newid = 0; // load the original record into an array $result = mysqli_query($connection, "SELECT * FROM {$table} WHERE {$id_field}={$id}"); if(!$result){ echo "ERROR-DuplicateMySQLRecord: '".mysqli_error($connection)."'"; return $newid; } else { // echo "Success"; $original_record = mysqli_fetch_assoc($result); // insert the new record and get the new auto_increment id $garb = mysqli_query($connection, "INSERT INTO {$table} (`{$id_field}`) VALUES (NULL)"); if(!$garb){ echo "ERROR-DuplicateMySQLRecord: '".mysqli_error($connection)."'"; return $newid; } else { // echo "Success"; $newid = mysqli_insert_id($connection); // generate the query to update the new record with the previous values $query = "UPDATE {$table} SET "; foreach ($original_record as $key => $value) { if ($key != $id_field) { $query .= '`'.$key.'` = "'.str_replace('"','\"',$value).'", '; } } $query = substr($query,0,strlen($query)-2); # lop off the extra trailing comma $query .= " WHERE {$id_field}={$newid}"; if(!mysqli_query($connection, $query)){ echo "ERROR-DuplicateMySQLRecord: '".mysqli_error($connection)."'"; return $newid; } else { // echo "Success"; // return the new id return $newid; } } } }
Customization:
No need to customize it. Just copy-paste. Rest edit the code as per comments and need.
Troubleshooting the Errors:
Do it with concentration and patience. Check your all steps again and all codes or scripts. If you find any error you can contact us anytime via comment or better via email, We are always here to help you.
Final Words:
That’s all we have. We hope that you liked this article. If you have any problem with this code in your template then feel free to contact us with a full explanation of your problem. We will reply to you as time allows us If you have any doubts or problems please comment below. We are happy to help you! If you liked this article, Don’t forget to share this with your friends so they can also take benefit from it and leave.
Be the first to write a comment.