Page 1 of 1

Need 2 auto increment columns?

Posted: Sun Feb 13, 2005 11:09 am
by Timmymna
I am new to PHP and MySQL so there maybe a simple solution to this but I dont know :?:

I am designing a hotel booking system and I have 3 tables which I am feeding info into from a form. These are the tables here

I was going to auto increment RoomsBookedID as well but it gives an error saying I can only have one auto inc per table, my problem is that I don't know how to have an auto incremented number for the ID and then how do I copy it across to RoomsBooked table? Or perhaps my tables need some redesign?

Any help is greatly appreciated :D

Posted: Sun Feb 13, 2005 11:15 am
by feyd
that's correct, you cannot have two auto_increment's in a given table. You can copy the ID from the first insert by calling mysql_insert_id() (in php) or LAST_INSERT_ID() (in mysql)

Posted: Sun Feb 13, 2005 11:18 am
by timvw
in booking you want autoincrement for bookingid
in room you want autoincrement for roomid
in roomsbooked the foreign keys roomid and bookingid make up the primary key.

don't see why you want to have a roomsbookedid column in your booking table?

don't see where you would need 2 columns to be autoincrement?


story goes like this:
a customer can make a reservation.
the customer has to tell which guests he is making the reservation for.
the guests are all assigned to a room. (can only be a room that is not reserverd at that time)
guests can consume stuff...

you can check out my simple design :)
http://timvw.madoka.be/stuff/HOTEL.pdf

klant = customer
reservatie = reservation
gast = guest
kamer = room
besteding = consumption
accomodatie = consumtion good

Posted: Sun Feb 13, 2005 12:13 pm
by Timmymna
Thank you timvw.
My tables now look like this That has solved problem needing two auto inc columns.

Thank you feyd
Thats exactly what I was looking for.

Thank you both :D Now on to bigger problems :P