PHP Forms with MySQL

boeta13

Hi Guys,

I'm trying to create a PHP form (For customer Quotations) which obviously contains a field where you can specify the item on the quotation, the quantity and the price (including or Excluding TAX of 15%).

to create the form is not the problem, but to store these multiple lines with each of their values in the right place into a MySQL database is the issue. The reason i want to do it like that is to be able to query or recall the quotations made to that specific client (selected from the client table) and to have the calculations correctly!

Can anyone please give me a hint! Ive been all over the net, but does not get the correct answers that im looking for!

Thanx in advanced!

bradgrafelman

Welcome to PHPBuilder!

I'd say the first steps to do would be to plan and design the MySQL database. So, how much of that have you done?

For example, using nothing but the bits of information included in your post above, I would probably start with a schema like:

[b]Clients[/b]
id - UNSIGNED INT, AUTO_INCREMENT, PRIMARY KEY
(any other relevant attributes, e.g. names, addresses, whatever)

[b]Items[/b]
id - UNSIGNED INT, AUTO_INCREMENT, PRIMARY KEY
(any other relevant attributes, e.g. name, description, category, whatever)

[b]Quotes[/b]
id - UNSIGNED INT, AUTO_INCREMENT, PRIMARY KEY
client_id - FOREIGN KEY (Clients.id)
date - DATETIME

[b]Quote_Items[/b]
quote_id - FOREIGN KEY (Quotes.id)
item_id - FOREIGN KEY (Items.id)
quantity - UNSIGNED INT
price - DECIMAL
is_taxable - BOOL

Note you could consider adding things like 'subtotal' and 'total' as attributes to the 'Quotes' table; this would mitigate the work of calculating these values on-the-fly, but it also runs the risk of becoming out-of-sync if you do some modifications to the quote without updating these attributes.

boeta13

:rolleyes:

HI bradgrafelman,

yes, i have the mysql database in place and have data in it already, regarding my stock company and client info!

wether the table structures is correct, im not that sure, although i have i.e. the id as UNSIGNED INT, AUTO_INCREMENT, PRIMARY KEY.

To connect, query and update the DB is not a problem, its just how to create the form as previously mentioned, for the different fields that will be used! and how to store the info in those fields.

The DB setup you mentioned surely helped a lot as a starting point, now all that remains is how to set up the form to populate the DB and columns correctly!

boeta13

the calculations is not that big deal. the pricing is calculated before hand (with the markup, etc.) the only thing is that we list the items excl VAT and then VAT is calculated from the grand Total. Perhaps the tax amount can be entered in a field which can be changed if needs be!