Insert Data Into Next Available Column

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Insert Data Into Next Available Column

Post by 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 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?
User avatar
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

Post by JAB Creations »

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. :wink: Good luck!
User avatar
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

Post by Bill H »

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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Insert Data Into Next Available Column

Post by Benjamin »

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.
Post Reply