Create Form Eventually Table

focus310

Hello:

I was wondering if someone can help me out. I'm trying to create a rate update form and post the data into a table.

The form have two columns: rate and price. The form will have 10 rows to enter rates. Something like this:

Rates | Price

6.0 | 0.00
6.5 | 0.125
etc.

Both columns will be text boxes for entering. My first question, when creating the form and text boxes, what would be the best way to "name" each text box? Can I name the rates column as name="rates" or does each row have to have its own name, such as for rate 6.0 name=rate1; 6.5 name=rate2, etc.?

Depending on how I setup the form to pass the data, I need to create a table to store the information.

I was hoping to have my table appear like the form does above. The table would have two columns: rates and price. And, all 10 rates and 10 prices would fall into those two columns.

Now to complicate things a bit more, there are 3 types of mortgages: 30yr, 15yr, and 5/1 ARM. I know I have to create a lookup table to assign a unique id for each mortgage type. Then, I know in my rates table I need to include the mortgage type because the table in the end should appear as such:

id | mortgage_type_id | rate | price

1 1 6.0 0.0
2 1 6.5 0.125
3 1 7.0 0.150
4 2 6.0 0.0
etc......

The reason for creating the table like this is to extract the lowest rate for each program.

Is this a good approach? Unfortunately, in order to create my table to look the one above, I need to pass the data from the form correctly.

Does anyone have any ideas how I create my form so my end result will the table I have above?

Thank you for the help.

leatherback

Hi,

First off: you form should NEVER define your database.

Your needs define your database. Usability defines the forms.

That being said:

Table morgages (All info about a morgage, from provider to type):
<id>
<provider>
<type>
<cost. (I would assume the prioce is linked to the morgage, not the rating!?)

Table ratings:
<rating>
(<price>) see above
<Morgage_nr>

In the form:
Name your variables as this:
rate[]
price[]

which will return an array of values for each variable, which is easy to work with when processing the form.

focus310

Hello:

Thank you for the reply. I do believe I have my tables defined.

I was basically going to use two tables.
The first table is the mortgage_type lookup table. It would look like this:

id | type

1 | 30 Year
2 | 15 Year
3 | 5/1 ARM

The second table would be the rates table which will have the data inserted from the data entry form. The table would look like this (each mortgage type will have 10 entries):

id | mortgage_type_id | rate | price
1 | 1 | 6.0 | 0.00
2 | 1 | 6.125 | 0.025
3 | 2 | 5.5 | 0.0
4 | 3 | 4.25 | 0.025
etc....

Then, if I want to see all the rates and prices for a 30 Year, I would write my select like this:
SELECT rate a, price a, type b
FROM rates a, mortgage_type b
WHERE b.mortgage_type = a.mortgage_type_id;

I should get a result like this:
30 Year | 6.0 | 0.00
30 Year | 6.125 | 0.025

Am I on the right track with the database portion?

Now, I need to design my form so I can get the data from the form into the table. I'm not sure if I'm following you. Here's what I think you are suggesting.

If I have 10 input boxes for the rates, then each input box would have a name = rate[]. The input boxes for price would then have the name = price[].

Or, would I have to identify the names for the text boxes for each row like this:
Row 1: name=rate[0] name=price[0]
Row 2: name=rate[1] name=price[1]
Row 3: name=rate[2] name=price[2]
etc....?

Then, my insert statement would look like this?

INSERT INTO rates (rate,price) VALUES ($POST['rate'], $POST['price']);

Am I anywhere close?

Thanks for the help.