how to store data in mysql from html table by laravel

Question

I have a Dynamic Sortable Table where I can add or delete rows like enter image description here

Now I am struggling to insert these data into my database (Mysql).

here is my view page

<table class="table table-hover table-sortable" id="tab_logic" name="DataTable">
    <thead>
    <tr class="text-center">
        <td style="width: 40%;">Process Name</td>
        <td style="width: 30%;">Machine Name</td>
        <td style="width: 10%;">Machine Qty</td>
        <td style="width: 10%;">SMV</td>
        <td style="width: 10%;">Action</td>
    </tr>
    </thead>
    <tbody>
    <tr id='addr0' data-id="0" class="hidden">
        <td data-name="ProcessName">
            {{Form::text('ProcessName', '', ['id'=>'ProcessName', 'class' => 'form-control', 'placeholder'=>''])}}
        </td>
        <td data-name="MachineName">
            <div class="form-group row-fluid m-auto">
                <select name="MachineName" class="form-control" id="MachineName" data-live-search="true">
                    <option value=""></option>
                    @foreach($machineName as $machineName)
                    <option value="{{$machineName->id}}">{{$machineName->MachineName}}</option>
                    @endforeach
                </select>
            </div>
        </td>
        <td data-name="MachineQty">
            {{Form::number('MachineQty', '', ['id'=>'MachineQty', 'class' => 'form-control', 'placeholder'=>''])}}
        </td>
        <td data-name="SMV">
            {{Form::number('SMV', '', ['id'=>'SMV', 'class' => 'form-control', 'placeholder'=>''])}}
        </td>
        <td data-name="del">
            <a name="del0" id="del0" class="btn btn-outline-danger row-remove"  value="del0">Delete</a>
            {{-- <button name="del0" class='btn btn-outline-danger row-remove'>Delete</button> --}}
        </td>
    </tr>
    </tbody>

    <tfoot>
    <tr>
        <td colspan="5" style="text-align: left;">
            <a id="add_row" class="btn float-right btn-lg btn-block btn-outline-secondary">Add Row</a>
        </td>
    </tr>
    <tr>
        <td colspan="5" style="text-align: left;">
            {{Form::submit('Save', ['class'=>'btn btn-lg btn-outline-primary btn-block', 'name'=>'ProductInsert'])}}
        </td>
    </tr>
    </tfoot>
</table>

there is my controller

// Create Post
$work_breakdown = new work_breakdown;
$work_breakdown->Buyer = $request->input('Buyer');
$work_breakdown->Style = $request->input('Style');
$work_breakdown->Item = $request->input('Item');
$work_breakdown->Size = $request->input('Size');
$work_breakdown->ThreadType = $request['Thread'];
$work_breakdown->Description = $request->input('Description');
$work_breakdown->ProductImage = $fileNameToStore;
$work_breakdown->user_id = auth()->user()->id;
$work_breakdown->save();


$maxValue = work_breakdown::max('id');
$i = 0;
$ProcessName_ID = $request->input('MachineName');
foreach($ProcessName_ID as $key => $MachineName) {

    $i++;
// Create Post
    $work_breakdown = new work_breakdown;
    $work_breakdown->ProductID = $maxValue;
    $work_breakdown->MachineID = $MachineName;
    $work_breakdown->ProcessName = $request->input('ProcessName');
    $work_breakdown->MachineQty = $request['MachineQty'];
    $work_breakdown->SMV = $request['SMV'];
    $work_breakdown->user_id = auth()->user()->id;
    $work_breakdown->save();
}

and I find this error: "Invalid argument supplied for foreach()"

enter image description here

This simply means that the thing you're trying to loop over (likely `$ProcessName_ID`) is not something that can be looped over. E.g., you might think it's an array but it's actually a string. — Alex Howansky, Jan 04 '19 at 18:23
you need validate if you input print one array or get first value — Alexander Villalobos, Jan 04 '19 at 18:25
@Alex Howansky, yes, actually I'm new and I don't know how to fetch data from a table. Can you please help me to do so? — Muslehuddin Juned, Jan 04 '19 at 18:38

Mike Harrison · Answer 1 · 2019-01-06T13:47:14.823

0

This is a classic example of a tricky concept when I was first starting with passing arrays as form inputs. It is a variation of this question but instead of defining the keys you can leave it blank to create a 0-index array.

You will need to have an "object like" format for each row in your form. In JSON you would want to send this to the server:

[
  {
    "ProcessName": "First process",
    "MachineID": "123",
    "MachineQty": "1",
    "SMV": "10"
  },
  {
    "ProcessName": "Second process",
    "MachineID": "456",
    "MachineQty": "2",
    "SMV": "20"
  }
]

To achieve this in your html you need to use the name attribute of the html input.

<tr id='addr0' data-id="0" class="hidden">
    <td data-name="ProcessName">
        {{Form::text('items[][ProcessName]', '', ['id'=>'ProcessName', 'class' => 'form-control', 'placeholder'=>''])}}
    </td>
    <td data-name="MachineName">
        <div class="form-group row-fluid m-auto">
            <select name="items[][MachineName]" 
                    class="form-control" 
                    id="MachineName" 
                    data-live-search="true">
                <option value=""></option>
                @foreach($machineName as $machineName)
                <option value="{{$machineName->id}}">{{$machineName->MachineName}}</option>
                @endforeach
            </select>
        </div>
    </td>
    <td data-name="MachineQty">
        {{Form::number('items[][MachineQty]', '', ['id'=>'MachineQty', 'class' => 'form-control', 'placeholder'=>''])}}
    </td>
    <td data-name="SMV">
        {{Form::number('items[][SMV]', '', ['id'=>'SMV', 'class' => 'form-control', 'placeholder'=>''])}}
    </td>
    <td data-name="del">
        <a name="del0" id="del0" class="btn btn-outline-danger row-remove"  value="del0">Delete</a>
    </td>
</tr>

And in your controller you will now have access to loop over each item by doing this:

//  ... initial setup here

foreach($request->input('items', []) as $item){
    $work_breakdown = new work_breakdown($item);
    // do other logic and then save to database
}

Hope this helps!

edited Jan 06 '19 at 13:47

answered Jan 04 '19 at 18:55

Mike Harrison

1,309
12
17

Also I was assuming you were using [this package](https://laravelcollective.com/docs/master/html#text) to generate the form inputs – Mike Harrison Jan 04 '19 at 18:56
It is working but it only iterates 4 times. not more than that. – Muslehuddin Juned Jan 05 '19 at 20:38
I think it iterates four times because there are four columns. but here column number is fixed and row number is variable and I want to iterate this loop on the basis on row number. – Muslehuddin Juned Jan 06 '19 at 04:28
In my example the column number is not variable...the number of rows is actually variable. You can check to see what you are sending to the server by looking at the network tab in chrome and inspecting the post data. Make sure it is in the format I showed in JSON or however you would like to be able to use it on the back end – Mike Harrison Jan 06 '19 at 08:43
everything is going fine except it only store the data of the first 4 rows. Rest of the rows values are not stored. If I use `dd($request->all())` then it shows [https://drive.google.com/open?id=1SWiWUPCgOnnCJnqiU4NDNT6Zp2poJwEZ] – Muslehuddin Juned Jan 06 '19 at 09:57
Also please don't edit an answer with code that doesn't work...I deleted your edits. – Mike Harrison Jan 06 '19 at 13:41
Your drive link is broken – Mike Harrison Jan 06 '19 at 13:48
You must have changed your HTML to different from my example. Please use the name to indicate an array of items like so: `` and do that with each of the columns for your variable amount of rows. Do not include a number at the end of the name of the column like you show in your `dd($request->all())` – Mike Harrison Jan 07 '19 at 17:45
could you please tell me what should I write after `$work_breakdown = new work_breakdown($item);` to save data – Muslehuddin Juned Jan 12 '19 at 19:36
save is done like this: `$work_dreakdown->save();`. Here is the docs: [Eloquent](https://laravel.com/docs/5.7/eloquent#inserting-and-updating-models) – Mike Harrison Jan 12 '19 at 19:38
Actually, I want to know how do I call data. Like what should I write instead of this `$work_breakdown->ProcessName = $request->input("ProcessName{$j}");` – Muslehuddin Juned Jan 12 '19 at 20:02
The line `$work_breakdown = new work_breakdown($item);` is going to fill the model with your data that you have in the `$item` variable. Which should contain all of those variables. You don't need to set every single one. The way I wrote it just sets all the properties at once. – Mike Harrison Jan 12 '19 at 20:05
Still not working. When I have inserted a row it stored 10 rows in the database. For details please visit this link: https://drive.google.com/open?id=1SWiWUPCgOnnCJnqiU4NDNT6Zp2poJwEZ . Here is my AnyDesk address 566 353 664. If you have the time it is very kind to me to contact. – Muslehuddin Juned Jan 13 '19 at 18:57

Muslehuddin Juned · Accepted Answer · 2019-01-08T14:41:17.857

I have solved this problem in a tricky way. If anyone knows the standard way then please let me know. Here I changed my view page like this

<table class="table table-hover table-sortable" id="tab_logic" name="DataTable">
                                <thead>
                                    <tr class="text-center">
                                        <td style="width: 2%;" class="text-white">SN</td>
                                        <td style="width: 38%;">Process Name</td>
                                        <td style="width: 30%;">Machine Name</td>
                                        <td style="width: 10%;">Machine Qty</td>
                                        <td style="width: 10%;">SMV</td>
                                        <td style="width: 10%;">Action</td>
                                    </tr>
                                </thead>
                                <tbody>
                                    <tr id='addr0' data-id="0" class="d-none">
                                        <td data-name="SN" class="d-none">
                                            <div class="form-group row-fluid m-auto">
                                            <input name="items[][SN]" type="text" class="form-control d-none" id="forIncrementalRow">
                                            </div>
                                        </td>
                                        <td data-name="ProcessName">
                                            {{Form::text('ProcessName0', '', ['id'=>'ProcessName', 'class' => 'form-control', 'placeholder'=>''])}}
                                        </td>
                                        <td data-name="MachineName">
                                                <select name="MachineName0" class="form-control row-fluid m-auto" id="MachineName" data-live-search="true">
                                                    <option value=""></option>
                                                    @foreach($machineName as $machineName)
                                                        <option value="{{$machineName->id}}">{{$machineName->MachineName}}</option>
                                                    @endforeach
                                                </select>
                                        </td>
                                        <td data-name="MachineQty">
                                        {{Form::number('MachineQty0', '', ['id'=>'MachineQty', 'class' => 'form-control', 'placeholder'=>''])}}
                                        </td>
                                        <td data-name="SMV">
                                        {{Form::number('SMV0', '', ['id'=>'SMV', 'class' => 'form-control', 'placeholder'=>''])}}
                                        </td>
                                        <td data-name="del">
                                            <a name="del0" id="del0" class="btn btn-outline-danger row-remove"  value="del0">Delete</a>                        
                                            {{-- <button name="del0" class='btn btn-outline-danger row-remove'>Delete</button> --}}
                                        </td>
                                    </tr>
                                </tbody>
                                
                                <tfoot>
                                    <tr>
                                        <td colspan="6" style="text-align: left;">
                                            <a id="add_row" class="btn float-right btn-lg btn-block btn-outline-secondary">Add Row</a>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td colspan="6" style="text-align: left;">
                                            {{Form::submit('Save', ['class'=>'btn btn-lg btn-outline-primary btn-block', 'name'=>'ProductInsert'])}}
                                        </td>
                                    </tr>
                                </tfoot>
                            </table>

and there is the controller

$maxValue = product_details::max('id');
            $i=0;
            $j=0;
            foreach($request->input('items', []) as $item){
            
            $j++;
            // Create Post
            $work_breakdown = new work_breakdown($item);
            $work_breakdown->ProductID = $maxValue;
            $work_breakdown->SN = $j;
            $work_breakdown->ProcessName = $request->input("ProcessName{$j}");
            $work_breakdown->MachineID = $request->input("MachineName{$j}");
            $work_breakdown->MachineQty = $request["MachineQty{$j}"];
            $work_breakdown->SMV = $request["SMV{$j}"];
            $work_breakdown->user_id = auth()->user()->id;
            $work_breakdown->save(); 
            }

            $userID = auth()->user()->id;

            //to delete unexpected row
            $maxValue2 = work_breakdown::where('user_id', $userID)->max('id');
            DB::delete('delete from work_breakdown where id = ?',[$maxValue2]);

This loop always save an extra row with null value, so I delete it after the loop

how to store data in mysql from html table by laravel

2 Answers2