Split array with multiple INSERT array_push in SQL

Question

Split array with multiple INSERT array_push in SQL

Asked 6 years, 6 months ago

Viewed 522 times

1

I have a foreach that makes the loop in the data of a file, this loop generates some values that are inserted in a array with array_push, I need to break these values inserted in this array to generate multiples INSERT INTO for some INSERT had more than 2500 characters.

Code:

$dados = array();
foreach ($arquivo->entrada as $xyz):
...
array_push($dados, $valores);
endforeach;

implode(', ', $dados);

print_r(array_chunk($dados, 2, true));

With this, I can break the values every 2 entries of array for example.

But how could I do that to have a query with several INSERT INTO? I don’t know how to do that by adding INSERT INTO 'tabela' VALUES VALUE-DO-ARRAY ; for each new split.

Example of data added to array:

('2615509767','Challenge','','','Portuguese','','')

Example of data obtained with the print_r:

Array
(
    [0] => Array
        (
            [0] => ('2615509767','Challenge','','','Portuguese','','')
            [1] => ('2178947891','Name','','','Portuguese','','')
        )

    [1] => Array
        (
            [2] => ('1877844784','City','','','English','','')
        )
)

Example of code desired:

INSERT INTO 'tabela' VALUES ('2615509767','Challenge','','','Portuguese','',''), ('2178947891','Name','','','Portuguese','','');
INSERT INTO 'tabela' VALUES ('1877844784','City','','','English','','');

How could I do this in with php?

Note, put 2 in the array_chunk as an example to the question, my query of INSERT has many values, and consequently would change this value to suit it.

– Florida

2018/05/01 at 02:12
the function print_r() passing true returns a string like the one of the right question? It wouldn’t just concatenate with the rest of the sql script?

– Costamilam

2018/05/01 at 02:24
@Guilhermecostamilam updated the data from print_r sorry for the discrepancy in the code. I don’t know exactly how to do this, nor do I remember how to do if I ever knew, so here I am.

– Florida

2018/05/01 at 02:40

1 answer

Browser other questions tagged php mysql sql array array-push

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

by NoobSaibot • **9,554** points · Answer 1 · 2018-05-01T04:00:09+00:00

You can go through each array, see:

I created a variable of type array.

$resultado = [];

Follow the steps:

Traverse the parent array.
```
for($i = 0; $i < count($dados); $i++)
```
Set a variable with part of the command.
```
$sql = "INSERT INTO 'tabela' VALUES ";
```

Traverse the child array.

for($b = 0; $b < count($dados[$a]); $x++)

Check whether the variable value $b is greater than zero if it concatenates a comma.
```
if ($b > 0) { $sql .= ', '; }
```
Concatenates the value.
```
$sql .= $dados[$a][$b];
```
Checks whether the value of $b is equal to the number of items in the index $a of $dados, if a semicolon is added.
```
if ($b === (count($dados[$a]) -1 )) { $sql .= ";"; }
```
Add the variable value $sql in the array $resultado.
```
array_push($resultado, $sql);
```

To finish, just make a implode adding a line break:

echo implode("\n", $resultado);

And you’ll get your way out:

INSERT INTO 'tabela' VALUES ('2615509767','Challenge','','','Portuguese','',''), ('2178947891','Name','','','Portuguese','','');
INSERT INTO 'tabela' VALUES ('1877844784','City','','','English','','');

Complete code:

$dados = [
  [
    "('2615509767','Challenge','','','Portuguese','','')",
    "('2178947891','Name','','','Portuguese','','')"
  ],
  [ "('1877844784','City','','','English','','')" ]
];

$resultado = [];
for($a = 0; $a < count($dados); $a++) {
  $sql = "INSERT INTO 'tabela' VALUES ";
  for($b = 0; $b < count($dados[$a]); $b++) {
    if ($b > 0) { $sql .= ', '; } // Concatena vírgula
    $sql .= $dados[$a][$b]; // Concatena valor
    if ($b === (count($dados[$a]) -1 )) {
      $sql .= ";"; // Concatena ponto e vírgula
    }
  }
  array_push($resultado, $sql);
}

echo implode("\n", $resultado);

See working in repl.it or in ideone.with