mySQL, PHP and Seat Planners... HELP!

[A Twig]

Right all, quick questions, I am building an online seat planner for an event. I am basing the front end on this but customised to suit tables rather than a 737:
http://www.digital-web.com/extras/jquery_crash_course/

This I am happy with. However, the back-end stuff, not so. It looks like I'll be using mySQL and PHP, neither of which I have any experience of. I've done C and Java aeons ago and thats it.
I essentially need a database that holds a transaction ID, a first name and a last name for each entry. These are the people who have purchased tickets. This database will be populated by me, probably from a CSV file.
They will click on the seat they want, put in their transaction ID, first and last names. If these match a database entry they will be allowed that seat, otherwise, it will be refused. Also, if the seat is already taken, it will be refused (I will think of some way to show this on the front end as well, i.e. seat being red.)

How hard can it be? :S

Can anyone point me the way of a good starter guide? Or some sort of existing thing I could customise/bodge to do what I want? Or just tell me how the best way of going about it would be?

Cheers

[DrunkenZombiee]

To need to come up with the DB design using Primary, Secondary or Composite keys to link the normalised tables together.

To do this model the relationships between the data to get normalised tables using the different relationship types of "one to many" and "one to one" crows foot or chen notation. There are a series of normalisation steps that you can follow (up to 7 [ignore boyce codd form]) but I just go straight to 7. You may think that one to one relationships are useless but trust me they are not. Really good way to enforce referential integrity if used correctly and depending on the views you can give different users it can be a way to abstract sensitive and non sensitive from different users.

Once the design is done you can either use something like SQL management server to generate the SQL for you or use the syntax to make tables and relationships yourself.

Once the Db is done use PHP to add and update records in the table from fields. Syntax of PHP is quite similar to C and its all quite straight forward for you. Make sure you protect against injection etc.

Cant recomend any good sites apart from W3 Schools.

If you need any help let me know and I will give you my company's the reduced company rate of 1 grand a day =P.

DZ

[A Twig]

Okay, sounds good, what program (if any) do you use for database design?

[DrunkenZombiee]

MS sql managment server 2008 or VISIO if you have the pro version if you are on windows. There will be loads of free tools on the internet that you can use to generate the SQL for your tables, constraints and relationships.

Happy hunting!

[ArithonUK]

If you download Visual Studio Express from Microsoft, it's free and has web editing capability for Java, C++, C#, VB, Jquery and a built-in local SQL server. You can download the SQL management tools (also free) from //www.microsoft.com/sql.

You can also download Apache and MySQL and run your own server (it'll run on a netbook even!)

The PHP help online has a heap of code examples in each section.

I've been writing in SQL since the mid nineties, so the INSERT, UPDATE and DELETE statements i'd do off the top of my head. MySQL, when installed, has a web control panel that allows a pretty simple table design interface.

I would advise that your interface displays occupied seats (think Odeon.co.uk seat booking) and only allows booking of unoccupied seats, then re-verify on POSTBACK with a "sorry that's now booked" message.

Think about how you handle multi-seat bookings (as they'll want to be next to each other).

Data storage is a lot simpler than input verification.

If you need any help, just ask.

[A Twig]

Hi, people will only be able to book one seat at a time, but they will be able to see who is already in the occupied seats. That should make the multi-booking system easier. Joy of joys, 123-reg have managed to lose our mySQL and php web-hosting package in the nether, so I'm being hamstrung. I've set up a basic model using MySQLWorkbench as that seemed an effective integrated solution for management and creation of databases. I created the initial table and am starting on a model. I'm not on my work machine at the mo, but I'll post up my model if someone wouldn't mind running their eye over it?

Basically I'm going to build a really simply version using drop down menus to get the concept and structure working, so that if the worst comes to the worst, then we have a functioning system. Once that is all in place I will tart up the front end as much as possibile in the time remaining. It needs to be robust and live by the 18th April, so fingers crossed!

[DrunkenZombiee]

Sounds like you have a single table (flat file DB) which can be OK sometimes depending on your use. What constrains are you going to put on the table? E.G. can one user modify another users entry? Depends what you have in mind.