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.

Chuck Burgess · Accepted Answer · 2010-03-06 16:32:57Z

1

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

answered Mar 6, 2010 at 16:32

Chuck Burgess

11.6k5 gold badges44 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PeterC Over a year ago

Thank you much! The INSERT INTO ... SELECT statement is obviously something I need to learn about! I will give this a try.

Marcx · Accepted Answer · 2010-03-06 16:13:33Z

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

answered Mar 6, 2010 at 16:13

Marcx

6,8365 gold badges50 silver badges69 bronze badges

2 Comments

PeterC Over a year ago

Thanks, but that was one thing I had stripped out of the code above for discussion purposes - it's in the actual code's error checking.

PeterC Over a year ago

I've been trying to use foreach() but can't figure out the right syntax: e.g. foreach($rooms as $room => $reservation_id) throws an error...

Decent Dabbler · Accepted Answer · 2010-03-06 16:33:22Z

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
    )

Collectives™ on Stack Overflow

php/mysql creating duplicate records with multiple tables

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related