Insert Function Help

[millsy007]

I have a coach booking program, Each Coach/Shuttle trip is made of up of journeys, on each of these passengers names are booked into a seat. For example:

shuttle_id Journey_id route_id depart_dttm seat1 seat2 seat3 seat4 seat5
1 3 3 01-01-2009 Jos Jan Joe

When I insert a record I want to insert the current passenger name into the first available seat, so for the above example it would be seat 4.
My current insert statement works by:

INSERT INTO journey
(seat1)
VALUES ('$name')
WHERE shuttle_id=$id
AND route_id=$route

Obviously this is only okay if seat 1 is available, how can I make it go into the first available/blank seat column?

Ideally I would add an extra table but alot of the program has already been produced so if I could keep the table in this format it would be great. I dont have a problem displaying the records or deleting them, it is just the part when I need to assign a passenger to a seat that is tricky. Is there something I could write that would allow this functionality?

[Fumigator]

Why are you doing another INSERT if you're just updating a row to add a guy to another seat? Don't you actually want to do an UPDATE?

[millsy007]

Yes actually, Sorry It was an insert but I changed the table structure so now it should update the row with the passenger in the seat, but I still have the same problem of knowing which column to update?

[Old Pedant]

Very bad database design!

GO BACK to the design you had before!

EACH PASSENGER should be a *SEPARATE RECORD*. Period. End of statement.

You cannot imagine how many problems you are going to have if you persist with this bad design!

You would have to write a very very complex Stored Procedure to handle this. And don't forget about passengers CANCELLING a reservation!

PLEASE don't do this.

[Fumigator]

Pedant has a point, but I wouldn't share his passion, as it's not _that_ bad to not normalize everything. Obviously it's preferable to normalize, but sometimes another decision must be made due to real life. You won't have to write a complex stored procedure; you can use your server language (PHP or whatever) to handle the seat assignments.

You'll need to first select the data from the table and then through a series of conditions (if statements or whatever), determine which seat is open and update the table using that column.

[CFMaBiSmAd]

And since you are updating at this point, why not just do a traditional edit/update form where you display the current values of the seat fields on one form, allow them to be entered or changed, and UPDATE the row in the table when the form is submitted.

[Old Pedant]

Yeah, if you feel you must change the DB (why, if it was working?), then what CFMaBiSmAd and Fumigator say make sense.

But you *COULD* do it all in a Stored Proc if you were so inclined. Ugly SP, but it would work.