Insert several rows into MySQL table at once

Question

I am building this website and I need to send some information to DB, however, the website must be able to insert several lines at once.

As you can see in the figure, at the column 'Grupo', the first on the left, I have 4 different numbers, these numbers are ids, each one for a different product. What I need to do is to insert these four lines into the DB with just one requisition.

My initial thought: Store the information as variables using JavaScript and attribute them as value to inputs of hidden type, but it would work just if I had only one line or if the number of rows was priviously fixed, what it isn't.

If you're just getting started with PHP and want to build applications, I'd strongly recommend looking at various [development frameworks](https://www.cloudways.com/blog/best-php-frameworks/) to see if you can find one that fits your style and needs. They come in various flavors from lightweight like [Fat-Free Framework](https://fatfreeframework.com/) to far more comprehensive like [Laravel](http://laravel.com/). These give you concrete examples to work from and guidance on how to write your code and organize your project's files. — tadman, Nov 03 '19 at 01:39
@tadman Laravel is my next stop, but I am trying to learn PHP first then go to a framework. I consider it very important, at least for me. Thank you. — Berg_Durden, Nov 03 '19 at 01:43
Laravel will teach you how to implement properly a lot faster than scrabbling around with low-level PHP ever will. What you're asking to do here is not simple to build from first-principles, and it's hard enough to do in Laravel. — tadman, Nov 03 '19 at 01:44
Adding to what tadman is suggesting is you are needing to figure out the front end at the same time. Overall it equates to a big learning curve. Simplified framework API's (simpler the better) can make that curve a little bit less steep — charlietfl, Nov 03 '19 at 01:49
@tadman I see. I'll consider that. But, in your opinion, learning Laravel, or any framework, before learning plain PHP wouldn't let me with a lack of knowledge. I am asking this because when I stopped to study React to start with plain JS and just after getting some knowledge to get back to React, it was really helpful for me, because it made things much easier. I thought that with PHP would be similar. Am I wrong? — Berg_Durden, Nov 03 '19 at 01:52
You'll need to know *some* PHP before tackling Laravel, just as knowing JavaScript will help you understand React, but using Laravel should be your goal if you want to build apps, and learning PHP supports that goal. Doing this in "plain old PHP" is really tricky to do, and even harder to explain, because there's about twenty concepts to work through to get to a solution. Laravel provides an abstraction around a bunch of those, and even then it will he challenging. — tadman, Nov 03 '19 at 01:54
Laravel will show you how modern object-oriented PHP is done. If you're learning "regular PHP" you may end up reading up on how to do old procedural style PHP which doesn't teach you much of anything other than really primitive scripting. Done right PHP can be as powerful and capable as Java. Done wrong and it's a mess of global variables and other garbage masquerading as code, the sort of stuff promoted in an infinite number of YouTube-grade "tutorials". You need to learn from good examples, and Laravel is one of the best I've seen that's community-supported. — tadman, Nov 03 '19 at 01:55
@tadman this is my goal, to go to Laravel and work with it rather than with plain PHP, that is why I am interested in your opinion. So, what you're saying is, in order to learn Laravel, I need to have a little background in PHP, but I don't need to become an expert, is this right? About your second comment, I know the importance of object-oriented languages, in fact, this is what I am studing right now. This app was all built based in OO. So, that is not a problem. — Berg_Durden, Nov 03 '19 at 02:02
@tadman of course I admit that about React, you're right. It really makes things much easier and allow me to get more quality in my apps. The only reason I went to plain JS is because when I started with React with no JS background, things got really difficult. So I decided to study the foundation then the framework. — Berg_Durden, Nov 03 '19 at 02:05
If you are using a framework like react then the idea of using hidden form inputs will be more work than sending arrays of data objects using ajax — charlietfl, Nov 03 '19 at 02:05
Just as React has the React way, Laravel has its own preferences. You're not the first to use React with Laravel, either, so there's going to be many example to work from here. It really depends on what kind of API you have on the back-end. Laravel makes implementing a REST API pretty straight-forward and GraphQL, a more modern approach, is also achievable. — tadman, Nov 03 '19 at 02:07
@charlietfl I know that, but this app is an exception. I am not using any framework here. — Berg_Durden, Nov 03 '19 at 02:08
Ah ok...so mostly a php driven page with some js for ui enhancments then? — charlietfl, Nov 03 '19 at 02:09
As for your actual question you are going to need to scale it down to more bite sized chunks. You are asking how to eat a whole elephant and the only way is one bite at a time — charlietfl, Nov 03 '19 at 02:14
@tadman I see what you're saying. I haven't thought in that way, my only references have been my experience with JS and React, and in that case, my step back was really important. Maybe it is time to take a step foward with PHP. I already have a bit of PHP background and I believe that the trying is worth it. Thank you for your time and your advices, it helped me a lot to see things in a more clear way. :) — Berg_Durden, Nov 03 '19 at 02:16
@charlietfl I understand, I know that. I'll try what you are saying. As a matter of fact I am not in a dead end, I have a plan B, but things would be much more easier if it was possible to do this in the way I have described. But if it is not possible, or if it is so complicated as both of you have said, I guess I'll try the long way. Thank you so much for your time and for your help. — Berg_Durden, Nov 03 '19 at 02:21
Sure it's possible but the overall scope of the question is far too broad to know where to even start to assist — charlietfl, Nov 03 '19 at 02:26

score 0 · Accepted Answer · edited Nov 05 '19 at 17:22

I kept trying and I was able to get the results I was looking for. In fact, this is a quite easy process.

What I did was to save the info from the inputs as a matrix, then loop over it and execute the query after every loop, simple as that.

I am providing some example codes. They are different from the table I have provided with the question, but the principles are the same.

The first thing I did was to format the names of the inputs as below. It made easier to loop over the arrays. The credit for this part belongs to the first answer of this post: Submitting a multidimensional array via POST with php

<tr>
   <td><input name="info[0][top]" value="tr 1/ td 1" /></td>
   <td><input name="info[0][bottom]" value="tr 1/ td 2" /></td>
</tr>
<br/>
<tr>
   <td><input name="info[1][top]" value="tr 2/ td 1" /></td>
   <td><input name="info[1][bottom]" value="tr 2/ td 2" /></td>
</tr>
<br/>
<tr>
   <td><input name="info[2][top]" value="tr 3/ td 1" /></td>
   <td><input name="info[2][bottom]" value="tr 3/ td 2" /></td>
</tr>

After that I just loop over the data coming from the form before executing the query.

$query = "INSERT INTO tb_teste (teste_info1, teste_info2) VALUES (:teste_info1, :teste_info2)";

$stmt = $this->conexao->prepare($query);

$el = $this->teste->__get('teste_info1');

$i = 0;
foreach ( $el as $item ) {

   $stmt->bindValue(':teste_info1', $this->teste->__get('teste_info1')[$i]['top']);
   $stmt->bindValue(':teste_info2', $this->teste->__get('teste_info1')[$i]['bottom']);

   $stmt->execute();
   $i++;
}

About the need of having a dynamic number of rows, I'll just create an input in JavaScript for every id that is generated and attribute the index of 'info' to a variable that will be incremented as it is needed. This will give me everything I need.

Insert several rows into MySQL table at once

1 Answers1