Welcome to Particletree!

A common piece of functionality found in web apps is the ability to duplicate an object. By duplicate, I mean the user clicks on a button, and a copy of the item is made in their account. By object, I mean a post, form, report, page, etc. It’s usually a pretty easy process that usually requires the duplication of some rows in a database.

Looking through source code, I have noticed a few times when developers loop through each row, and duplicate them individually. For example:

$sql = 'SELECT * FROM Comments WHERE PostId = 1';
$rs = $db->query($sql);
$while($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
    // reinsert into table with a new query per iteration
}

If there are 10 comments, the loop will go through 10 queries. I wanted to make note to anyone who wasn’t aware that this can be done entirely with SQL without the need for logic in the server side code.

$sql = 'INSERT INTO Comments(Message, Typeof, PostId) ' .
       'SELECT Message, Typeof, '.$dynamic.' FROM Comments WHERE PostId = 1';

If there are 100 records with a PostId of 1, then 100 new records will be inserted. I also placed a variable named dynamic in there to show that you can also modify the data that gets copied into the new records. In this example, you can imagine that we are copying all comments from one post to another. The first Post has a PostId of 1, and the second of 2. If we set dynamic to 2, we will achieve what we’re looking for.