How to insert values into MySQL database from a dynamic html table?

Question

form

I have this form in which user can add multiple rows depends on his/her preference. The name of each input tags were generated using jquery; meals1...amount1, meals2...amount2 and so on.

My question is how do I insert all those values into my MySQL database?

So far I have this function:

function dbRowInsert($table_name, $form_data)
{
    // retrieve the keys of the array (column titles)
    $fields = array_keys($form_data);

    // build the query
    $sql = "INSERT INTO ".$table_name."
    (`".implode('`,`', $fields)."`)
    VALUES('".implode("','", $form_data)."')";

    // run and return the query result resource
    return mysql_query($sql);
}

HTML:

<div id ="ca_meals"><!--start of ca_meals-->
                <div class="container">
                <div class="row clearfix">
                <div class="col-md-12 column">
                <h3>Meals Form</h3>
                <div class="form-group">
                    <label for="ca_meals_date">Date:</label>
                    <input type="text" class="form-control" id="ca_meals_date" name="ca_meals_date" placeholder="Date">
                </div>
                <div class="form-group">
                    <label for="ca_meals">Purpose:</label>
                    <input type="text" class="form-control" id="ca_meals_purpose" placeholder="Purpose">
                </div>
                <table class="table table-bordered table-hover" id="tab_logic_meals">
                    <thead>
                        <tr >
                            <th class="text-center">
                                Meals
                            </th>
                            <th class="text-center">
                                Number of PAX
                            </th>
                            <th class="text-center">
                                Alloted Budget Per PAX
                            </th>
                            <th class="text-center">
                                Amount
                            </th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr id='meals0'>
                            <td>
                            <input type="text" name='ca_accommodate'  placeholder='Meals' class="form-control"/>
                            </td>
                            <td>
                            <input type="text" name='ca_meals_numberpax' placeholder='Number of PAX' class="form-control"/>
                            </td>
                            <td>
                            <input type="text" name='ca_meals_budgetpax' placeholder='Alloted Budget Per PAX' class="form-control"/>
                            </td>
                            <td>
                            <input type="text" name='ca_meals_amount' placeholder='Amount' class="form-control"/>
                            </td>
                        </tr>
                        <tr id='meals1'></tr>
                    </tbody>
                </table>
                <div class="form-group">
                    <label for="ca_total">Total:</label>
                    <input type="text" class="form-control" id="ca_total" disabled>
                </div>
                </div>
                </div>
                <a id="add_row_meals" class="btn btn-primary pull-left">Add Row</a><a id='delete_row_meals' class="pull-right btn btn-danger">Delete Row</a>
                </div>
            </div><!--end of ca_meals-->

This is my JQuery:

$("#add_row_meals").click(function(){
        $('#meals'+l).html("<td><input name='ca_meal"+l+"' type='text' placeholder='Meals' class='form-control input-md'  /> </td><td><input  name='ca_meals_numberpax"+l+"' type='text' placeholder='Number of PAX'  class='form-control input-md'></td><td><input  name='ca_meals_budgetpax"+l+"' type='text' placeholder='Alloted Budget Per PAX'  class='form-control input-md'></td><td><input  name='ca_meals_amount"+l+"' type='text' placeholder='Amount' class='form-control input-md'></td>");
        $('#tab_logic_meals').append('<tr id="meals'+(l+1)+'"></tr>');
        l++; 
        });
        $("#delete_row_meals").click(function(){
         if(l>1){
         $("#meals"+(l-1)).html('');
         l--;
         }
        });

Never create your queries this way. It is susceptible to mysql injection. Other than that the mysql_ functions are deprecated (stackoverflow.com/questions/12859942/…) — edwardmp
– edwardmp, Commented Jan 17, 2015 at 23:39
It might be helpful to echo $sql and have a look at an example query. — jaynp
– jaynp, Commented Jan 17, 2015 at 23:45

Oleg Dubas · Accepted Answer · 2015-01-17 23:45:23Z

1

Usually it's done by passing arrays or indexes with the fields names.
Say, in JavaScript when you add a DHTML field for the name, call it meals[]
In PHP the $_POST['meals'] will contain an array then.
The other way - indexes. Say, when you create a new field with Javascript, give it a new name like meals_1, meals_2, etc. And then loop on them in PHP.

For the first case, with meals[], the POST request will be like:

 &meals[]=aaa&meals[]=bbb&meals[]=ccc&amount[]=1&amount[]=2&amount[]=3

An example of PHP code working with meals[] would be:

for($i=0;$i<count($_POST['meals']);$i++)
{
    $sql = "INSERT INTO ... SET
               `meals` = ". $_POST['meals'][$i].", 
               `amount` = ". $_POST['amount'][$i].",
            // etc
}

answered Jan 17, 2015 at 23:45

Oleg Dubas

2,3481 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sachi Tekina Over a year ago

Is there a way to make used of the function dbInsertRow? Like I'll just add another parameter for the length of the indexes.

Oleg Dubas Over a year ago

@SachiTekina it depends on how you use that function, you haven't shown us how do you call it, what is $form_data, where do you get it from

Sachi Tekina Over a year ago

Like this link

Oleg Dubas Over a year ago

Why use this function at all? You can address all variables coming from _POST directly, why inventing another array and creating additional difficulties?

Collectives™ on Stack Overflow

How to insert values into MySQL database from a dynamic html table?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related