I'm facing a problem while executing some queries in a foreach loop.
Only the first loop is executing and then this error is appearing:
SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
I've tried many different ways but they didn't work. I added login parameters or even closeCursor after executing the query.
config.php:
<?php
//Database connection parameters
$host = 'xxxxxx';
$db = 'xxxxxx';
$user = 'root';
$pass = 'xxxxxx';
$port = "xxxxxx";
$charset = 'utf8mb4';
//connection
$options = [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES => false,
\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY =>true,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset;port=$port";
try {
$pdo = new \PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
actions.php:
Of course, I added example variables etc..
require_once 'config.php';
$sprawujacyP= array(
0 => array(
'plec' => 'Kobieta',
'data_urodzenia'=>'1998-01-14'
),
1 => array(
'plec' => 'Mężczyzna',
'data_urodzenia'=>'1998-01-14'
),
);
$id_r = 1;
$id_op = 3;
$outmsg="123";
$sql = "CALL example_procedure(:id_r,:plec,:data_urodzenia,:id_op,:outmsg)";
$statement = $pdo->prepare($sql);
$statement->bindParam(':id_r',$id_r,PDO::PARAM_INT);
$statement->bindParam(':id_op',$id_op,PDO::PARAM_STR);
$statement->bindParam(':outmsg',$outmsg,PDO::PARAM_STR);
foreach ($sprawujacyPas $sprawujacy) {
try{
$statement->bindParam(':plec',$sprawujacy['plec'],PDO::PARAM_STR);
$statement->bindParam(':data_urodzenia',$sprawujacy['data_urodzenia'],PDO::PARAM_STR);
if($statement->execute()){
$statement->closeCursor();
}
else{
echo 'error...';
}
}
catch(PDOException $e){echo $e->getMessage();}
}