Copying related MySQL Tables. I have a table, with related tables, and I'd like to have a function to copy the lot

OK here's the thing.

I have a MySQL Database, and in it there are these tables

Cruises
Fares
Itinerary
Supplements

In the admin panel of the 开发者_StackOverflow社区website, I am adding a feature to copy an entire cruise, and the related fares, itinerary, and supplements data

So the first table 'Cruises' has a primary key (ID) and that's picked up in each of the other tables as 'cruise_id'

E.g.

Cruises
-------
id
destination
departs
returns
nights

Fares
-----
id
cruise_id
fare
offer
active

Itinerary
---------
id
cruise_id
port
type

Supplements
-----------
id
cruise_id
sharing
deposit

So when a user is in the admin panel, they see a list of the existing cruises, and a link to copy

When a user clicks on copy cruise I want to add a new 'cruise', copying the data of the cruise they selected

Now that I can do, using INSERT SELECT. However, I also want to make copies of the Fares, Itinerary and Supplements as well, so the whole set of data is copied, so minor changes can then be made later.

Logically (this is not MySQL syntax obviously) I want to do this

Select everything from the table Cruises, Fares, Itinerary, Supplements

Where Cruise (id) is equal to the selected cruise (url parameter passed on the link)

And Fares, Itinerary, Supplements (cruise_id) is equal to the selected cruise (url parameter passed on the link)

take that data and insert the cruise fields into cruises, fares into fares, itinerary into itinerary and supplements into supplements

I know what I want to do, question is can I, and how?

I think you can use INSERT SELECT for inserting into fares, itinerary, and supplements. Something like:

INSERT INTO `Fares` (`cruise_id`, `fare`, `offer`, `active`)
SELECT `cruise_id`, `fare`, `offer`, `active`
FROM `Fares`
WHERE `cruise_id` = xx;

INSERT INTO `Itinerary` (`cruise_id`, `port`, `type`)
SELECT `cruise_id`, `port`, `type`
FROM `Itinerary`
WHERE `cruise_id` = xx;

INSERT INTO `Supplements` (`cruise_id`, `sharing`, `deposit`)
SELECT `cruise_id`, `sharing`, `deposit`
FROM `Supplements`
WHERE `cruise_id` = xx;

where, xx should be replaced with the id of the selected cruise.

Hope this helps.