Insert Data Into Next Available Column

[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?

 

[trq]

You need to look at some tutorials on database normalization. That database schema is not the best of designs.

 

Theres a link in my sig (Hudzilla) that has a small chapter on the subject.

[millsy007]

Hi agreed that 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?

[trq]

Your going to need to write a series of select statements that loops through and checks each field for a value, when it find an empty field exit the loop and do your update.

 

Terribly inifficient but I can't see another way with that terrible table schema.

[millsy007]

I know the schema is rubbbish, but really I need to work with what I have to get something working.

 

Would something like:

 

set $seat variable

 

select $seat as check from journey where id = $id

 

if returned check value is not null

 

increment $seat - do query with next seat value

 

else

 

do update query

 

?

[PFMaBiSmAd]

It's ALWAYS better to fix design problems during the design phase. It takes at least 10 times more time and effort to fix problems after a design is being used. Your present design is inflexible and won't accommodate a different number of seats without modifying the table or a broken seat that is unusable without modifying the code that figures out the next available seat.

 

If you were using a table to hold the assigned seats, you could do a straight forward INSERT query that checks if the current count of the seats assigned for any "trip" is less than the available seats.

[millsy007]

I would agree with that, unfortunately I'm past the design face of the overal website, page to delete and page to view the schedule are already done. It is only a last minute change to assign the passengers to specific seats that has caused an issue. That is why I am looking for a solution, that although it may be a little unflexible would get the information inserted.

 

Although not neat would the code plan I suggested be viable?

 

I do welcome feedback re. redesigning the schema but its not really possible in this case.

[fenway]

Ignoring the normalization issue, the "real" problem if that you can't specify column names dynamically in mysql statements.

 

So you'll need to determine which seat is available "next" -- trivial to do with a single statement, which you can then "pipe" to your update statement (since obviously insert isn't what you want).