3
How to insert all array values and avoid $query->execute() to each execution of the foreach?
$_POST = json_decode(file_get_contents('php://input'), true);
$sql = "INSERT INTO teste(nome, email) VALUES (:nome, :email)";
try{
$query= $conecta->prepare($sql);
foreach ($_POST as $key => &$value) {
$query->bindValue(':nome',$value['nome'],PDO::PARAM_STR);
$query->bindValue(':email',$value['email'],PDO::PARAM_STR);
$query->execute();
}
}
.
.
.
4
One way to make a single Insert is to have several VALUES to generate an sql as
INSERT INTO teste(nome, email) VALUES (?,?),(?,?),(?,?)
Your array is multidimensional, in which case the first step is to flatten it creating a new one is very important that the values are in the correct order because otherwise they can be written in the wrong columns. Still inside the foreach, concatenate the string to general sql in the expected format.
Initial structure of the array:
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());
}
1
You need to do three things:
1) Generate the placeholders.
Knowing how many groupings of values will be inserted, generate sql with placeholders. It will generate something like:
INSERT INTO test(name, email) VALUES (:nome0, :email0),(:nome1, :Email1),(:Nome2, :Email2)
2) Make Binds with array values.
3) Run the query outside of foreach after you have done all Binds
In the end, it executes 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();
}
Browser other questions tagged php mysql pdo
You are not signed in. Login or sign up in order to post.
Since it is PDO, it is an alternative that may be valid in some contexts. If you are going to use native Binds, as mysqli does, in many situations it is more elegant and efficient to have several "execute", which transmit only the values, and in native types, greatly speeding up the process.
– Bacco