PHP Forms, saving in Database?

[briesm]

Hey all. I've got a php form script working online, and essentially I have it emailing the data to an email address. What I was wondering if there would be a way to somehow also send it to an online database that would keep all the information and save it. This way it might be possible to re-open the form and edit it at another time. Anyone mind pointing me in the right direction? I guess what I'm hoping to do is somehow have a feature that allows users to edit a form days or weeks after it has been submitted.

[lobo235]

You could use a database like mysql to save all the values of the form with a unique id. Then, whenever you want to edit that form you just need the unique id to pull up all the information and populate your form so it can be edited.

[briesm]

Right now in the form I use sessions because I have it calculating and passing variables along through each step, but basically I'd want to send all variables to a MySQL database on each submit? I guess the database would have columns for each variable I wanted to save and then it would have a unique ID that could be recalled to load the form with the appropriate information?

[lobo235]

Exactly. You could either save the information at each step or you could just save the final values from the session when the user submits the form. It just depends on your needs.

[briesm]

Alright, there are two potential problems that I am worried about. But first, let me say that my goal is when a quote form (potential sales order) is filled out, I receive an email and the variables are all saved into a MySQL form for easy editing later on. Now, one problem is that I need a numbering system, and a way to retrieve the information as well. I found a good site to start off at:
[thescripts.com...]

ISSUE 1:
They have created a table that would use a unique id AutoIncrement so that each quote would essentially be the last entry +1. Though I'm wondering if there is a way to reference the form's number as it is submitted, maybe to somehow look up the greatest number and then add 1 to it or something?

ISSUE 2:
One variable in my form is basically an array that holds the order information, i.e. all orders and there pricing per item, total, etc. I'm wondering how I could go about saving this array in MySQL. It contains numbers, words, and even a short description. maybe I could save each seperate array element into a different column or something but it is important to note that the array size varies because there are different numbers of orders.
Possible Solution: I think I can overcome this by having a seperate table for orders. If order 1 had 5 items, the Order ID Number 1 would be entered 5 times, with all of its information for each individual part, and I'd have to retrieve the 5 rows. So I think that would work.

Thanks so much for your help :)

[lobo235]

The auto increment will take care of the unique number, just don't insert anything into it yourself and mysql will take care of it for you. One way that you can save arrays in a mysql database is to use the serialize() function which turns the array into a string that can be saved into the database. Then when you pull it out you can use the unserialize() function to return it to it's array form.

[Sarah Atkinson]

ISSUE 2:
One variable in my form is basically an array that holds the order information, i.e. all orders and there pricing per item, total, etc. I'm wondering how I could go about saving this array in MySQL. It contains numbers, words, and even a short description. maybe I could save each seperate array element into a different column or something but it is important to note that the array size varies because there are different numbers of orders.
Possible Solution: I think I can overcome this by having a seperate table for orders. If order 1 had 5 items, the Order ID Number 1 would be entered 5 times, with all of its information for each individual part, and I'd have to retrieve the 5 rows. So I think that would work.

Thanks so much for your help :)

Exsactly

What you have is a one-many relivence. so you need 2 tables in your DB

One with the order info and another with the items/price/discription ect.

in the second you would have something like
orderid ¦ product ¦ price ¦ discription

or you could make it even more streamlined by having 3 tables

one would be
id ¦ product name ¦ price ¦ discription

then the other would be
orderid ¦ productid

of course if you change your price or discription you might have to create a new entery so that you can perserve the old order data So you can go back and easily check to see what the price was at time of purchace.

[briesm]

Does anyone see anything wrong with this code? For some reason it's not adding entries to mysql database...No errors either, and it still sends the emailed information...Also, all $ variables are defined to equal Posts, i.e. $territory=$POST_['territory'];
Also, the table is setup with all of those fields, all of which are NULL, varchar, and a length of 50, except for order_Num which is an int, being autoincremented to work as an order number

mysql_connect ("#*$!x", "#*$!x", "#*$!x") or die ('Could not connect to the database because: ' . mysql_error());

mysql_select_db ("quoteDatabase") or die ('Could not select database');

$result = MYSQL_QUERY("INSERT INTO customerMaster (order_Num, territory, salesRep, repEmail, facility, address, contact1, department1, phone1, fax1, email1, contact2, department2, phone2, fax2, email2, comments, gpo, otherDiscount)".
"VALUES('NULL', '$territory', 'salesRep', '$repEmail', '$address', '$contact1', '$department1', '$phone1', '$fax1', '$email1', '$contact2', '$department2', '$phone2', '$fax2', '$email2', '$comments', '$gpo', '$otherDiscount')");

mysql_close();

[edited by: jatar_k at 6:01 pm (utc) on June 21, 2005]
[edit reason] fixed sidescroll [/edit]

[Sarah Atkinson]

i think i would write it in this format:

$result = MYSQL_QUERY("INSERT INTO customerMaster SET
order_Num='NULL', (although this should be auto so you just wouldn't have this line right?)
territory= '$territory',
salesRep= 'salesRep',
repEmail= '$repEmail',
facility=
address= '$address',
contact1= '$contact1',
department1= '$department1',
phone1= '$phone1',
fax1= '$fax1',
email1= '$email1',
contact2= '$contact2',
department2= '$department2',
phone2= '$phone2',
fax2= '$fax2',
email2= '$email2',
comments= '$comments',
gpo= '$gpo',
otherDiscount='$otherDiscount'");

This way you can also make sure there are no mistakes like with 'facility'
Sarah

[briesm]

Thanks for the input. That does look a lot better. Sadly enough, i think my problem was that I was typing $POST_['varname'] when it should be $_POST
:( :( :( Hehe. Now to tackle the array issue.

Then I'll have to work on withdrawing data from the database later on. SHouldn't be too tough though.

[briesm]

Okay, here's my next issue...So, essentially i'm keeping track of order numbers by using an autoincrement. I want my other table, with all the order information (items selected for purchase), to be able to capture what the autoincrement is from the other table so that it can apply the same number to each order entry. Is there a way to connect to an SQL Database and retrieve what the autoincrement is at?

[jatar_k]

when you insert, insert the row with the auto increment id first and then use mysql_insert_id [php.net] to get the id used and then use that to insert into your other table.