Trying to create an Order Log Form...Help

[cmaclennan]

Hey Guys,

 

First off I just want to say I love this forum, and everyone thats out there offering there expertise to those of us that need it.

 

The problem I'm having is that I am trying to create a form that submits to a MySQL Database the form is to log all of the orders that my company processes, currently they are using a large and very ugly excel sheet. The main part i'm having difficulty wrapping my head around is how to incorporate all the necessary fields it has to have all the necessary customer details & invoice numbers and then be able to store the part number & quantity the only thing is that it could be anywhere from 1 part to 40.

 

Any help or suggestions on how to build this would be greatly appreciated.

 

Thanks in Advance.

C

[the182guy]

Start by getting a basic forum up and running which inserts into the orders table.

 

Then add the more complicated bits like multiple parts. For that create a new table for parts with fields like part_number, quantity, order_id. The order_id field being important because it will link the part to an order. You can then query the database to retrieve all the parts for a certain order for example.

 

I would also seperate the customer info and store that in its own table.

 

So this is how I would structure the database:

 

table_orders

------------

id [Primary Key with Auto Increment]

invoice_number

ordered_on (date/datetime)

 

table_parts

-----------

id [Primary Key with Auto Increment]

part_number

order_id (this links to order table, sometimes referred to as a Foriegn Key)

 

table_customer

---------------

id [Primary Key with Auto Increment]

first_name

last_name

telephone

 

Obviously you would probably have other fields such as payment_received or order_status and whatever else you need. A structure like that is known as a normalised database or partly normalised.

[cmaclennan]

Thanks for your help, just a couple of things, when setting up or designing the form I know I have seen instances on forms where a button or link can trigger the addition of a row for example to add more parts do you know how this is done? (Ajax or something?) also when setting up with multiple tables this way and creating the relationships between the ID's how do you set the form to submit to the right table and in the instances where there is more than one row for the products is it just handled as if submitting multiple forms to the table?

 

Hope that made sense.

 

Thanks Again

[the182guy]

I should have also mentioned a field for customer_id in the orders table.

 

There is no Ajax needed for what you describe if you mean, pop another textbox onto the form by pressing a an "Add Part" button. Just a bit of javascript to do that.

 

You would need to set the form action to submit to a PHP script, the PHP script will then read all the data posted to it, and it will need to insert into each of the tables one at a time. You'd first insert into customer table to get the customer ID, then into the orders table to get the order ID, then the parts table for each part that has been submitted.

 

What experience do you have with HTML, PHP and MySQL? If none then have a look at some starter HTML/PHP/MySQL tutorials.