Problem when running consecutive MySQL queries in PHP

Question

Problem when running consecutive MySQL queries in PHP

Navigation

#1 by (1 votes)
#2 by (1 votes)

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:=@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 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

mysql php pdo

asked by Piropeator 13.10.2017 в 00:58

source

2 answers

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:=@id+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

Show Array and Matrix Show the selected text dropdown

score 1 · Accepted Answer

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.