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?
Insert Data Into Next Available Column
Moderator: General Moderators
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Insert Data Into Next Available Column
I'm not really sure what your setup is like though I would probably set each seat to have either a 0 or 1 value...obviously 0 would be available and 1 would mean taken...and the row id's (if I set this up) would simply be the seat id's (so if you have 44 seats you'd have 44 rows naturally)...
...you may somehow use limit to restrict the returned rows to only the first...so maybe do a search for...
WHERE available='0'
...and then do limit. Beyond that I'd recommend maybe posting your existing database setup for others to see. I'm not to the point where I can plan an entire database out like you're supposed to before you start a project out...one day soon though.
Good luck!
...you may somehow use limit to restrict the returned rows to only the first...so maybe do a search for...
WHERE available='0'
...and then do limit. Beyond that I'd recommend maybe posting your existing database setup for others to see. I'm not to the point where I can plan an entire database out like you're supposed to before you start a project out...one day soon though.
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: Insert Data Into Next Available Column
Assuming the "seat1" etc values are strings, I think you are going to need to read the record first and test the length of the existing data to see which one has a zero length (or a value of NULL) and then write the record accordingly.
Re: Insert Data Into Next Available Column
Ideally the seats would be in a different table, but with your existing setup I think the best solution would be to first run a select on it and use PHP to determine the first available seat. There may be a way to do it in a single query or possibly a stored procedure, although it would probably be pretty tricky to write.