Insert Data Into Next Available Column
Posted: Tue Mar 03, 2009 9:23 am
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 passenger 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 sql statement works by:
UPDATE journey
SET seat1 = $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?
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 passenger 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 sql statement works by:
UPDATE journey
SET seat1 = $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?