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 (http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — edwardmp, Jan 17 '15 at 23:39
It might be helpful to `echo $sql` and have a look at an example query. — jaynp, Jan 17 '15 at 23:45

score 1 · Accepted Answer · answered Jan 17 '15 at 23:45

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 '15 at 23:45

Oleg Dubas

2,320
1
10
24

Is there a way to make used of the function `dbInsertRow`? Like I'll just add another parameter for the length of the indexes. – Sachi Tekina Jan 17 '15 at 23:57
@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 – Oleg Dubas Jan 18 '15 at 00:17
Like this [link](http://stackoverflow.com/questions/27977679/how-to-pass-parameter-to-php-function-and-call-in-html/27977755) – Sachi Tekina Jan 18 '15 at 00:50
Why use this function at all? You can address all variables coming from _POST directly, why inventing another array and creating additional difficulties? – Oleg Dubas Jan 18 '15 at 01:10

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

1 Answers1