If you are a regular reader of this blog, you may want to just skip this entry. I am posting a php function I wrote this afternoon that solves a problem I could not find using Google. I often grab code off of other peoples posts or websites, so I thought I should "give back" so other people could benefit. I'm also curious if I get any hits from this....
The statement of the problem goes something like this:
Is there a quick and easy way to copy a whole row in a MYSQL table and copy it to the same table? obviously i want the ID to change to the next auto_increment value.
The solution is the following:
INSERT INTO the_table(column_list) SELECT column_list FROM the_table;
(ref: this link)
where column_list excludes the first "pk" element since we want it to autoincrement, The problem is that column_list could be quite long, and may not be fixed, so it needs to be created dynamically. Here's a PHP function to do the job:
// Makes a duplicate of a table row
// All fields are duplicated except the first slot pk field, which
// is auto-incremented to the next available value.
// You are probably going to have to "fix" the site_db.Conn stuff to match your setup.
function MysqlCopyRow2($TableName, $IDFieldName, $IDToDuplicate) {
global $site_db;
global ${database_.$site_db.Conn}, ${$site_db.Conn};
mysql_select_db(${database_.$site_db.Conn}, ${$site_db.Conn});
if ($TableName AND $IDFieldName AND $IDToDuplicate > 0) {
$selectSQL = "SELECT * FROM $TableName WHERE $IDFieldName = $IDToDuplicate";
$rstSelect = mysql_query($selectSQL, ${$site_db.Conn}) or die(mysql_error());
if ($rstSelect) {
$column_list = "";
$row = mysql_fetch_array($rstSelect);
$RowKeys = array_keys($row);
for ($i=3;$i<count($RowKeys);$i+=2) {
if ($i!=3) { $column_list .= ", "; }
$column_list .= $RowKeys[$i];
}
$insertSQL = "INSERT INTO $TableName($column_list) SELECT $column_list FROM $TableName WHERE $IDFieldName = $IDToDuplicate";
$rstInsert = mysql_query($insertSQL, ${$site_db.Conn}) or die(mysql_error());
}
}
}
Here's a line of code to call this function (property is the table name, property_pk is the name of the first autoincrement field in my property table, and $oldPK is an integer that is the specific row you want copied.)
MysqlCopyRow2("property","property_pk",$oldPK);
Good luck! It works for me.
Recent Comments