How to write multiple records in a table at the same time Mysql

Asked

Viewed 24,015 times

1

I need to record several records at once in Mysql for example.

I own the tables PRODUTOS, CATEGORIAS and a third table that lists the two and also a form where I select the category and mark all the checkboxes of the products that will be inserted in this category, I need each checkbox marked to become a record in the table so I can have 'N' records at the same time.

The record in the third table would look like this:

ID: 1 | Categoria: 1 | Produto: 1
ID: 2 | Categoria: 1 | Produto: 2
ID: 3 | Categoria: 1 | Produto: 3
ID: 4 | Categoria: 1 | Produto: 4

Each product entered was a checkbox marked and the product is related to category 1 (1 = category ID)

php mysql

2 answers

6

Mysql does not support Insert in Multi-tables. That is, it is not possible to insert data with a single query in different tables.

However, it supports inserting multiple data into a single table at a time (batch insertion). Ex:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Mysql Insert: http://dev.mysql.com/doc/refman/5.7/en/insert.html

  • What I want is exactly this, to insert several data into a single table, because the first two are already filled with the proper records, what I wish to know is what I must do to perform this insertion of multiple records because Inserts will not always have the same amount of records, may be that in a first insert I relate 2 products to a category after I relate 5 products to another category, so I wonder if I have to make a loop or something similar so that the Insert knows how many records will be entered.

  • If I understand correctly, then no. You don’t need to make any loop when inserting the new values. Regarding the fact of relating different products in different categories, it does not interfere in anything in the query. You can do all this in the same query. Just you adjust your code for this.

  • But this is exactly what I’m not knowing, what is the logic to insert multiple data at the same time in the table in a single Insert.

3


I found in an article and will post the solution with a link.

What I wanted is exactly what is in this article, insert multiple values into a database table dynamically where the first Insert could have 3 values the second 6 values and so on.

generate the SQL string with all values to be inserted, comma separated, in order to execute the query only once.

So we can do this:

$valores = range( 1 , 10 );
$sql = sprintf( 'INSERT INTO tabela(numero) VALUES (%s)', implode( '), (' , $valores ) );
$DB->query( $sql );

If we give an echo in $sql, we will have as output:

INSERT INTO tabela(numero) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

This way the SQL is only executed once, making the execution much faster.

Source: http://rberaldo.com.br/inserindo-multiplos-registros-em-tabela-de-banco-de-dados/

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.