php/mysql creating duplicate records with multiple tables

Question

I'm building a database for making hotel reservations. One table called "reservations" holds the general details of the reservation, while another called "rooms" holds details about specific rooms (each reservation has many rooms, each room belongs to only one reservation).

I would like to be able to easily generate duplicate reservations records (except for the primary key, of course). My problem is in generating the rooms data as an array which is then inserted into the rooms table while being associated to its reservation.

I've come as far as the following trivial code (stripped down to the bare essentials for discussion purposes).

if (isset($_POST['action']) and $_POST['action'] == 'Duplicate')
{
    include $_SERVER['DOCUMENT_ROOT'] . '/includes/connect.inc.php';
    $id = mysqli_real_escape_string($link, $_POST['id']);

// retrieve reservation
$sql = "SELECT type_of_reservation FROM reservations WHERE id='$id'";
$result = mysqli_query($link, $sql);
$row = mysqli_fetch_array($result);
$type_of_reservation = $row['type_of_reservation'];
// create new reservation record
$sql = "INSERT INTO reservations SET type_of_reservation ='$type_of_reservation'";
$id = mysqli_insert_id($link);

// retrieve rooms
$sql = "SELECT reservation_id, in_date FROM rooms WHERE reservation_id='$id'";
$result = mysqli_query($link, $sql);
while ($row = mysqli_fetch_array($result))
{
    $rooms[] = array('reservation_id' => $row['reservation_id'], 'in_date' => $row['in_date']);
}

The big question is, now what? Everything I've tried either generates an error or no new entries, and I can't seem to find any discussion that addresses this specific need. Thanks for your help.

Answer 1

// create new reservation record
    $sql = "INSERT INTO reservations SET type_of_reservation ='$type_of_reservation'";
      //ADD HERE CODE BELOW
    $id = mysqli_insert_id($link);

with mysql_insert_id you get the inseted id, but you should insert it into db.. so add

mysqli_query($link, $sql);

before retrieving data

Answer 2

PeterC, there is no code listed that shows you inserting the ROOM record information. In the //retrieve room section of your code, you are pulling the data and putting it into an array. If you really want to create a duplicate records, I would use in insert inside the database, then you don't have to pull the records out just to put them back in.

The bit of code you want will be something like this. It will be in place of the //retrieve rooms code you have listed: (psuedo code) [note: $id represents the newly selected id from your sql insert for the duplicated reservation]

INSERT INTO rooms(res_id, other, data) SELECT $id, other, data FROM rooms WHERE id = $_POST['id'];

This will allow you to duplicate the room data, adding the new reservation_id right inside the database. No need to pull out the records, create inserts, and then put them back in. You can read more about INSERT INTO ... SELECT statements here: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

Answer 3

If you simply need to duplicate records, you can do it this way:

INSERT INTO 
   reservations 
   (
     SELECT 
         null, # assume first column is auto incrementing primary key, so leave null
         `all`, 
         `other`, 
         `column`,
         `names` 
     FROM 
         reservations 
     WHERE 
         reservation_id = $oldReservationId # id of reservation to duplicate
   )

Then for the rooms use the last inserted id (for instance retrieved with mysql_insert_id), like this:

INSERT INTO
    rooms
    (
        SELECT
           null, # assume first column is auto incrementing primary key, so leave null
           $newReservationId, # this is the new reservation id
           `all`,
           `other`,
           `column`,
           `names`
        FROM
           rooms
        WHERE
           reservation_id = $oldReservationId  # id of reservation to duplicate
    )