visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Deliberately duplicate records
By: Squibi U.S.A.  Date: 27/02/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
Hi,
I'm trying to work out the best way to do this.

We are developing an application that uses a Mysql back end to store persistant data. We want to implement a "Save As" function, which requires duplication of data in the main projects table, and also the data in one-to-many related subtables.

So I want to duplicate the data in one record of the main table, with a new auto ID field, and then duplicate all the rows of the sub table with the foreign key reffering to the new project ID. And then perform the same operation on the table that stands in a one-to-many relationship to the just altered records.

We can do it programatically within the program, but that generates a lot of code, and it seems to me there may be a simpler SQL way of doing it. I looked at multiline insert statements of the order "Insert into <table> Select * from <table> where ID = <desired ID>" and of couse we ended up with both duplicate primary key issues and referential integrety issues.

My questions - can I do this without creating a temp table to hold the interim query data? A more basic and perhaps exposing my newbie status question, can I reference columns by number rather than by name - so I can create functions that will work for all my table updates?

Any help appreciated.
By: VGR Date: 28/02/2003 07:45:00 English  Type : Answer
ok
then for your last question, no, you can't directly use fields by number (would be bad practice), but you can in fact workaround elegantly (elegantlier) by first using a query "DESCRIBE tablename;", getting fields and types and sizes, and memorizing this in an array $fields[0..x]["name"|"type"|"maxlength"] (matrix)

Now as for your problem, it's solvable two way :
-a- programmatically using a sequence of SQL statements
-b- in SQL by using a distinct "savedas" table

-b- : just INSERT INTO savedas SELECT * FROM originaltables WHERE ...;

-a- : something like this in PHP :
$linkID=mysql_connect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
mysql_select_db("$dbDatabase",$linkID) or die ("bad select DB $dbDatabase : ".mysql_error());
// select original unique dataset
$query="select * from $dbTableName where id='1'"; // adapt
$result=mysql_query($query,$linkID) or die ("bad query via '$query' . ".mysql_error());
$res=mysql_fetch_array($result);
// if you did the DESCRIBE $dbTableName first, you know which fields' names to memorize using this kind of lines : ${loc$fieldname[$i]}=$res[$fieldname[$i]];
$locmotd=$res["motd"]; // example with static field name
$locID=$res["id"]; // given you always have an "id integer unique auto_increment" field in every table ;-)
// now duplicate data
$query="INSERT INTO $dbTableName VALUES(0,looped_fieldnames[]_values_except_id);"; // adapt, write a for() loop
$result=mysql_query($query,$linkID) or die ("bad query via '$query' . ".mysql_error());
// get new ID $locID2;
$query="select id from $dbTableName where some_subset_of_the_data ORDER BY ID DESC LIMIT 1"; // take the last ID inserted (the new one)
$result=mysql_query($query,$linkID) or die ("bad query via '$query' . ".mysql_error());
$res=mysql_fetch_array($result);
$locID2=$res["id"];
// access linked tables to the original on $locID;
explicit
// insert in same tables as linked to record $locID2;
explicit
// done.
By: Squibi Date: 28/02/2003 08:20:00 English  Type : Comment
Great- but we're using Cold Fusion not PERL, so we'll have to adapt.

Thanks, Keith
By: Squibi Date: 28/02/2003 08:23:00 English  Type : Comment
I mean not PHP --- (: [
By: VGR Date: 28/02/2003 09:08:00 English  Type : Comment
yes, CF -> PHP is not trivial but easy. Good luck :D

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 353.585960 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page