CODEWITHSUNDEEP
phpmysqlforms
leoarce
leoarce

Reputation: 549

How do I store data in mysql database from a form where unique fields are dynamically added?

Other people use "dynamic fields" definition meaning to clone same fields over and over, but that's not how I'm defining "dynamic fields". I have a script where I manage categories and products in a single table, then I use that table to display a form with text fields on a different page. So my question is, how do I create the next table and the php for inserting into that 2nd table so it saves the dynamic fields? Fields may be added or removed at anytime and the new table and insert statement should be able to be flexible and accommodate that.

Here is an example of records in 1st table (categories_products):

id  label        altname      parent  sort
1   cat2         cat          0       4
2   subcat1      cat          1       5
3   cat1         cat          0       1
4   subcat2      cat          1       9
5   product 1a   product_1a   3       2
6   product 2a   product_2a   2       6
7   product 2b   product_2b   2       7
8   product 3a   product_3a   4       10
9   product 3b   product_3b   4       11
10  new product  new_product  3       3

Info about cat/prod table: altname ends up being the names of form fields, and the ones with "cat" in alt name never become fields, they are titles of categories and subcategories. here is example of generated form below. i added one extra field (company) which i will also need to save.

<form class='' id='form-id'>
    Company: <input type="text" name="company" value="">
    <ul class='a'>
        <li class="b">
            <p><strong>cat1</strong></p>
            <ul class='a'>
                <li class="c"><p>product 1a: <input type="text" name="product_1a" ></p></li>
                <li class="c"><p>new product: <input type="text" name="new_product" ></p></li>
            </ul>
        </li>

        <li class="b">
            <p><strong>cat2</strong></p>
            <ul class='a'>
                <li class="b"><p><strong>subcat1</strong></p>
                    <ul class='a'>
                        <li class="c"><p>product 2a: <input type="text" name="product_2a" ></p></li>
                        <li class="c"><p>product 2b: <input type="text" name="product_2b" ></p></li>
                    </ul>
                </li>
                <li class="b">
                    <p><strong>subcat2</strong></p>
                        <ul class='a'>
                            <li class="c"><p>product 3a: <input type="text" name="product_3a" ></p></li>
                            <li class="c"><p>product 3b: <input type="text" name="product_3b" ></p></li>
                        </ul>
                </li></ul>
        </li>
    </ul>
    <input type="submit" name="submit" value="Submit">
</form>

The point of this is that different companies get different pricing. Prices are what will be typed into form fields. And the point of having all the categories and products generating the form is because all companies get all the same categories and products. Then later admins will think of new products and go in and add the new products, so the new products then need to be part of the new form for saving.

So at this point, I don't know what table structure to use for saving records of company prices. I keep hearing that it's not wise to create and destroy table columns on-the-fly, so what is the better way? Should all form field values get saved into a single table column? but how? how can this be dynamic and what will happen to the data of this column for past rows when new form fields are created?

I started this new table, but this is where it ends and I don't know what to do next or how to store data of dynamic form.

table name: product_pricing
id
company
price

and the following might be an example of a couple records, but I really wouldn't know how to make this happen:

id    company    price
1     abc        {product_1a:0.01,product_2a:0.01,product_2b:0.01,product_3a:0.01,product_3b:0.01,new_product:0.01}
2     xyz        {product_1a:0.02,product_2a:0.02,product_2b:0.02,product_3a:0.02,product_3b:0.02,new_product:0.02}

or will this be better since names of products might change? save by ID's instead:

id    company    price
1     abc        {5:0.01,6:0.01,7:0.01,8:0.01,9:0.01,10:0.01}
2     xyz        {5:0.02,6:0.02,7:0.02,8:0.02,9:0.02,10:0.02}

I can worry about how I will retrieve and parse this column data later, but I need to get passed this 1st hurdle of how I am going to save/store the data via php and mysql code. Obviously there will be an edit page for the the records to edit prices, so update will need to resave the data properly. this is what I am worried about. how data will always be in correct spot no matter what is done to the first table (adding and removing of products).

Upvotes: 1

Views: 275

Answers (1)

James Hunt
James Hunt

Reputation: 2528

Right let's separate this out. You have:-

  • Categories
  • Products
  • Companies

A category can contain many categories and many products, a product can be connected to many companies and each company can have a unique price per product, correct?

If so, what I'd suggest is:-

Category Table:

Category ID, Category Name, Category Alt Name, Parent Category, Order

Product Table:

Product ID, Product Name, Product Alt Name, Category ID, Order

Company Table:

Company ID, Company Name

Company Product Link Table:

Link ID, Company ID, Product ID, Product Price

At this point, you don't need any dynamic columns, as everything has been separated out. Let's say you add a product that you only want certain companies to have/have prices for, you only insert links for those companies in the Link table, then your SQL for the form would be:-

SELECT
    cplt.link_id,
    pt.product_name,
    pt.product_alt_name,
    cplt.product_price
FROM
    company_product_link_table cplt
INNER JOIN
    product_table pt
ON
    pt.product_id = cplt.product_id
WHERE
    cplt.company_id = PUT COMPANY ID HERE

The names of your input fields for the prices can then just be the link ID in the table, then to update them it's just a case of

UPDATE
    company_product_link_table
SET
    product_price = PRODUCT PRICE
WHERE
    link_id = NAME OF FIELD

Upvotes: 1

Related Questions