Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Feb 2009
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Function Help

    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?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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?

  • #3
    New Coder
    Join Date
    Feb 2009
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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.

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,036
    Thanks
    2
    Thanked 316 Times in 308 Posts
    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.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •