I want to insert records into a table from another table. The first record to insert is the record that has the sum of the amounts of the records of the source table. (Charge) And the rest of the records to be inserted are those that appear in the origin table (Abonos).
Only the first query is executed, inserting the corresponding record.
I have this:
$BD = new ConexionDB();
$libro = 10
$asiento = ObtenerMaxAsiento();
//---------- CARGO
$sql = "SET @id = (SELECT max(id) from tabla);
SET @total = (SELECT SUM(importe) from temporal WHERE cruza_cod = 'X');
INSERT INTO tabla (id, libro, asiento, correlativo, periodo, importe)
VALUES (@id:=@id+1, $libro, $asiento, 1, $periodo, @total)";
$sth = $BD->query($sql);
//funcion obtiene maximo id
$id = ObtenerMax();
//---------- ABONO
$sql = "INSERT INTO tabla (id, libro, asiento, correlativo, periodo, importe)
SELECT @id:=@id+1 as id, $libro, $asiento, @corr:=@corr+1 as corr, periodo, importe
FROM (SELECT @id:= $id) r, (SELECT @corr:=1) s, temporal WHERE cruza_cod='X'";
$sth = $BD->query($sql);
When executing it shows me:
Fatal error: Uncaught exception 'PDOException' with message '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
My MySQL database connection is with PHP/PDO.
class ConexionDB extends PDO {
public function __construct () {
try {
parent:: __construct('mysql:host='.DB_HOST.';dbname='.DB_DATA.';charset=utf8', DB_USER, DB_KEY);
parent:: setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $ex) {
die ('La Base de Datos no existe');
}
}
function __destruct(){
}
}
I run it on Xampp 1.8.2 what is the problem?DS
Probably the method
query
is not the right one for what you want to do. The buffer remains open as long as you don't fetch. I would recommend you to useexecute
and/or explicitly usecloseCursor
it may
closeCursor
be too much. It depends in part on the connection parameters that you have passed, or the optional parameters that you pass to the execute. For exampleIt would automatically close the cursor after execution.
It seems to me that pdo doesn't execute multiple statements in one. for that you must use
mysqli->multi_query
http://php.net/manual/es/mysqli.quickstart.multiple-statement.phpChange:
by: