Problem when running consecutive MySQL queries in PHP

2

I want to insert records in a table from another table. The first record to insert is the record that has the sum of the amounts of the records in the source table. (Position) And the rest of the records to insert are those that appear in the source table (Subscriptions).

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:[email protected]+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:[email protected]+1 as id, $libro, $asiento, @corr:[email protected]+1 as corr, periodo, importe
FROM (SELECT @id:= $id) r, (SELECT @corr:=1) s, temporal WHERE cruza_cod='X'";
$sth = $BD->query($sql);

When you run, it shows me:

  

Fatal error error: Uncaught exception 'PDOException' with message   'SQLSTATE [HY000]: General error: 2014 Can not 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 connection to the MySQL database 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 in Xampp 1.8.2 What is the problem? DS

    
asked by Piropeator 13.10.2017 в 00:58
source

2 answers

1

Probably the query method is not the right one for what you want to do. The buffer is open as long as you do not fetch. I would recommend you use execute and / or explicitly use closeCursor

//---------- CARGO
$sql1 = "SET @id .... ";
$sth = $BD->prepare($sql1);
$sth->execute();
$sth->closeCursor();

//funcion obtiene maximo id
$id = ObtenerMax();

//---------- ABONO
$sql2 = "INSERT INTO tabla ... ";
$sth = $BD->prepare($sql2);
$sth->execute();
$sth->closeCursor();

maybe closeCursor is over. It depends in part on the connection parameters that you have passed, or on the optional parameters that you pass to the execute. For example

 $sth = $BD->prepare($sql2, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false]);

It would automatically close the cursor after the execution.

    
answered by 13.10.2017 / 13:01
source
1

It seems to me that I do not execute multiple instructions on one. for that you must use mysqli->multi_query link

Change:

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:[email protected]+1, $libro, $asiento, 1, $periodo, @total)

by:

INSERT INTO tabla (id, libro, asiento, correlativo, periodo, importe)
VALUES ((SELECT max(id) from tabla), $libro, $asiento, 1, $periodo, (SELECT SUM(importe) from temporal WHERE cruza_cod = 'X'))
    
answered by 19.10.2017 в 07:47