How to insert all array values and avoid $ query-execute () at every execution of foreach?

One way to do a single insert is to have multiple VALUES to generate an sql as

INSERT INTO teste(nome, email) VALUES (?,?),(?,?),(?,?)

Your array is multidimensional, in that case the first step is to flatten it by creating a new one that is very important that the values are in the correct order, otherwise they can be written to wrong columns. Still within the foreach concatenate the string for general to sql in the expected format.

Initial array structure:

Array
(
    [0] => Array
        (
            [nome] => a
            [email] => [email protected]
        )

    [1] => Array
        (
            [nome] => b
            [email] => [email protected]
        )
)

Converted structure:

Array
(
    [0] => a
    [1] => [email protected]
    [2] => b
    [3] => [email protected]
)

Code:

$itens = array(array('nome' => 'a', 'email' => '[email protected]'),
               array('nome' => 'b', 'email' => '[email protected]'),
               array('nome' => 'c', 'email' => '[email protected]')                    
        );

$sql = "INSERT INTO teste(nome, email) VALUES ";

$novo = array();

foreach($itens as $item){
    $sql .= '(?,?),';
    array_push($novo, $item['nome'], $item['email']);
}

$sql = trim($sql, ',');//remova a última vírgula

$query = $conecta->prepare($sql);
if(!$query->execute($novo)){
    echo "<pre>";
    print_r($query->errorInfo());
}

You have to do three things:

1) Generate placeholders.

Knowing how many groupings of values are going to be entered, generate the sql with the placeholders. It will generate something like:

INSERT INTO test (name, email) VALUES (: name0,: email0), (: name1,: email2)

2) Bind with array values.

3) Run the query outside the foreach after you have done all the binds

At the end, execute only one query with all the insert.

<?php
/* Connect to a MySQL database using driver invocation */
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'root';
$password = '';

try 
{
    $conecta = new PDO($dsn, $user, $password);
} catch(PDOException $e) 
{
    echo 'Connection failed: ' . $e->getMessage();
}

//imaginando que se tenha uma matriz de nomes e e-mails, como é um csv
$csv_array = array(
    array(
        'nome'    =>'Teste 1',
        'email'    =>'[email protected]'
    ),
    array(
        'nome'    =>'Teste 2',
        'email'    =>'[email protected]'
    ),    
    array(
        'nome'    =>'Teste 3',
        'email'    =>'[email protected]'
    )        
);

// gerando os placeholders e inserindo no seu sql
$qtd = count($csv_array);

$sql= 'INSERT INTO teste(nome, email) VALUES ';
$sql_fragment = '(:nome, :email),';

for($i=0; $i<$qtd; $i++)
{
    $sql_to_add = str_replace(    
                    array(':nome',':email'),    
                    array(":nome$i",":email$i"),
                    $sql_fragment 
                  );
    $sql.=$sql_to_add;
}
$sql = rtrim( $sql, ",");

//echo $sql;
//INSERT INTO teste(nome, email) VALUES (:nome0, :email0),(:nome1, :email1),(:nome2, :email2)

// fazendo os binds dos placeholders com os valores do array
try{
    $query= $conecta->prepare($sql);

    $i=0;
    foreach ($csv_array as $linha) 
    {
        $query->bindValue(":nome$i", $linha['nome'],PDO::PARAM_STR);
        $query->bindValue(":email$i",$linha['email'],PDO::PARAM_STR);
        $i++;
    }

    $query->execute();
}
catch(Exception $e)
{
    echo $e->getMessage();
}