How to write multiple records to a table at the same time MySQL [duplicate]

0

I need to write multiple records at once in MySQL for example.

I have the PRODUTOS , CATEGORIAS tables 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 that category, I need each marked checkbox 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

Since each product entered was a checked checkbox and the product is related to category 1 (1 = Category ID)

    
asked by anonymous 15.03.2015 / 15:51

2 answers

0

I found 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 the values to enter, separated by a comma, 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 have the output:

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

That way you can only run SQL once, making execution much faster.

Source: link

    
16.03.2015 / 19:57
3

MySQL does not support insert into multi-tables. That is, it is not possible to insert a single query with data in different tables.

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

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

MySQL Insert: link

    
15.03.2015 / 17:22