Array split with array_push for multiple INSERT in SQL

Question

Array split with array_push for multiple INSERT in SQL

Navigation

#1 by (2 votes)

1

I have a foreach looping in a file's data, this loop generates some values that are entered in array with array_push , precise break those values entered in this array to generate multiple INSERT INTO because some INSERT were over 2500 characters long.

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 this to have a query with multiple INSERT INTO ? I do not know how to do this by adding INSERT INTO 'tabela' VALUES VALUE-OF-ARRAY ; to each new split.

Example of data added to array :

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

Example of data obtained with print_r :

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

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

Example code:

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

How could I do this in php?

php mysql sql array array-push

asked by anonymous 01.05.2018 / 04:10

1 answer

C # EFD-REINF 1.03.02 - Event signature invalid. Digital signature of XML document is invalid Syntax Error in Ruby: unexpected tCONSTANT, expecting keyword_end

score 2 · Accepted Answer

You can go through each array, see:

I created a variable of type array.

$resultado = [];

Follow these steps:

Scroll through the parent array.

for($i = 0; $i < count($dados); $i++)

Define a variable with part of the command.

$sql = "INSERT INTO 'tabela' VALUES ";

Scroll through the child array.

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

Verify that the value of the $b variable is greater than zero, if it concatenates a comma.

if ($b > 0) { $sql .= ', '; }

Concatenate the value.

$sql .= $dados[$a][$b];

Checks whether the value of $b is equal to the number of elements in the index $a of $dados , if it adds a semicolon.

if ($b === (count($dados[$a]) -1 )) { $sql .= ";"; }

Add the value of the $sql variable in the $resultado array.

array_push($resultado, $sql);

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

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

And you have the output:

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 .com