Copy/duplicate table row?

Hi,

I got a table for an online configurator with a lot of relevant information (30 or so columns). There might have been a better way to handle it, than creating so many columns, but new stuff is (and was) added all the time.

Now I have been requested to make a "copy" function, that will make a copy of an existing configuration (table row).
Is there an easy/smart way to do that using mysqli or something else? I have made something that works, but it's far from pretty.... there must be a better way to do this.

This is what I use today:
mysql_query("INSERT INTO configurations (id, userid, type, type_name, type_desc, stroke, rod_ext, distance_tube, distance_tube_table, distance_tube_id, tube, dim_inside, rod, rod_drawing, bottom, bottom_standard, connection_port_bot, connection_port_bot_standard, connection_port_bot_angle, connection_port_top, connection_port_top_standard, connection_port_top_angle, sealbox, sealbox_standard, piston, piston_standard, mount_tube, mount_tube_type, mount_tube_standard, mount_tube_allow, mount_rod, mount_rod_type, mount_rod_standard, bearing_tube, bearing_tube_standard, bearing_rod, bearing_rod_standard, GVi_B, GVi_G, GV_B, GV_G, CB_L_ROD, CB_L_TUBE, CPB_L, CPB_Lmin, CPB_W, CPB_Wmin, customer_id, customer_note, rev, rev_date, rev_hashkey, locked, deleted, userip, createdate)
SELECT '$cid', userid, type, type_name, type_desc, stroke, rod_ext, distance_tube, distance_tube_table, distance_tube_id, tube, dim_inside, rod, rod_drawing, bottom, bottom_standard, connection_port_bot, connection_port_bot_standard, connection_port_bot_angle, connection_port_top, connection_port_top_standard, connection_port_top_angle, sealbox, sealbox_standard, piston, piston_standard, mount_tube, mount_tube_type, mount_tube_standard, mount_tube_allow, mount_rod, mount_rod_type, mount_rod_standard, bearing_tube, bearing_tube_standard, bearing_rod, bearing_rod_standard, GVi_B, GVi_G, GV_B, GV_G, CB_L_ROD, CB_L_TUBE, CPB_L, CPB_Lmin, CPB_W, CPB_Wmin, '', customer_note, '1', now(), rev_hashkey, '0', '0', userip, now()
FROM configurations WHERE id='$old_cid' ") or die(mysql_error());

Open in new window




, Kenneth
kgp43Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
there must be a better way to do this.
That seems pretty standard - when you say better - what specifically are you thinking in terms of potential improvement?

If the insert uses all fields then you can do the following
INSERT INTO configurations SELECT <FIELDLIST> FROM configurations

Open in new window

All fields specified and in the same order

If you have autonumber fields then simply specify NULL in the field list for that field - for example if you have this
CREATE TABLE `configurations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` varchar(30) DEFAULT NULL,
  `type` char(30) DEFAULT NULL,
  PRIMARY KEY (`agent_id`)
) 

Open in new window

You could do this
$query = "INSERT INTO configurations SELECT null, userid, type FROM configurations WHERE id={$old_cid}";

Open in new window

Saves you having to type out the field list twice.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgp43Author Commented:
I don't have auto-increment fields in that table.
Row ID is unique, based on a random string.

So that last piece of code you wrote will work.
I still have to type all the column names i guess.
Ray PaseurCommented:
Any time the question involves duplicating data, my DRY antennae twitch.  You might want to look a little deeper into this request and see if there isn't a better way to solve the problem.  When you have made duplicate copies of atomic data, one of the things that always comes back to haunt you is the question of updates.  Do you update one or all of these copies?  If someone wants to delete a row, do you delete one or all of these rows?  Replicated data is almost always a recipe for confusion.  In my experience, the only useful place for data replication is in commodity inventory, and in that case, the tables that carry the replication only carry a single column - the key that points to the commodity.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Julian HansenCommented:
We actually have several instances where we use copy as a necessity.

For instance, customer has setup a product and wants to enter another similar product but does not want to have to retype in all the information. We provide them with a product copy button which creates a new product entry but with the same data as the original. They are then free to go in and change the attributes that are different on the new product.
Ray PaseurCommented:
Copying and changing some of the data makes perfect sense.  The resulting data elements (rows) would represent different commodities.
kgp43Author Commented:
That's exactly what we use it for as well.
Going to try it out when I get home from work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.