multiple sql queries running issue

Question

I am pretty new to PHP, and my current issue is with have to sql queries running on the same page.

I have two different divs on the same page. One div showing all jokes from of a store procedure, and the other one for the authors from a simple select.

What I get from this error message:

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.' in /usr/local/pem/vhosts/272348/webspace/httpdocs/icloudis.com/sohail/crud_cssgrids/aeroplane.php:18 Stack trace: #0 /usr/local/pem/vhosts/272348/webspace/httpdocs/icloudis.com/sohail/crud_cssgrids/aeroplane.php(18): PDO->query('select * from p...') #1 {main} thrown in /usr/local/pem/vhosts/272348/webspace/httpdocs/icloudis.com/sohail/crud_cssgrids/aeroplane.php on line 18

I understand that from this message that I can't have several unbuffered queries at the same time? So, I need to use fetchAll or have

setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);

and also use closeCursor(). What I don't grasp yes is how to use allt of this to make it work.

This is my config file:

try {


    $pdo = new PDO('mysql:host=xx.xxx.xxx.xx; dbname=xxxxxxx', 'xxxx', 'xxxxxx');
    $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
    $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);
} catch (PDOException $e) {

    echo 'Connection failed: ' . $e->getMessage();

    die();

}

The page where I want my queries, I have not attempted to put out the result of my second query here, but the current code is:

 <?php
//including the database connection file
//include_once("config_local.php");
include_once("config.php");

session_start();
if(empty($_SESSION['email']))
{
 header("location:index.php");
}

echo "Welcome ".$_SESSION['name']; 



$result = $pdo->query("call aeroplane");
$result->closeCursor();
$result1 = $pdo->query("select * from plane_maker");
$result1->closeCursor();

?>
<!DOCTYPE html>

<html>
    <head>
        <meta charset="UTF-8">
        <title>Aeroplanes</title>
        <link rel="stylesheet" href="css/styles.css">
    </head>
    <body>
<section class="grid-1">
  <div class="item-1">1</div>

  <div class="item-2">
    <div class="item-5">
    <a href="add_form.php">Add New Aeroplane</a> &nbsp; | &nbsp;
        <a href="aeroplane_maker.php">Add New aeroplane maker</a>
        <br/><br/>

    <table width='80%' border=0>

    <tr bgcolor='#CCCCCC'>
        <td>Maker Name</td>
        <td>Aeroplane</td>
        <td>Top speed</td>
        <td>Range</td>
        <td>Update</td>
    </tr>
    <?php

    while($row = $result->fetchAll()) {         
        echo "<tr>";
        echo "<td>".$row['planeMakerName']."</td>";
        echo "<td>".$row['aeroplaneName']."</td>";
        echo "<td>".$row['aeroplaneTopSpeed']."</td>";
        echo "<td>".$row['aeroplaneRange']."</td>";
        echo "<td><a href=\"edit.php?id=$row[aeroplaneID]\">Edit</a> | <a href=\"delete.php?id=$row[aeroplaneID]\" onClick=\"return confirm('Are you sure you want to delete?')\">Delete</a></td>";        
    }

    ?>
    <a href="logout.php">Logout</a>
    </table>
    </div>
    <div class="item-6">6</div>
    <div class="item-7">7</div>
  </div>

  <div class="item-3">3</div>
  <div class="item-4">4</div>
</section>
    </body>
</html>

-thanks

Your Common Sense · Accepted Answer · 2017-04-24 11:55:15Z

1

The code you posted is not a real one. Which makes the code and the error unrelated to each other, which, in turn, makes it impossible to answer.

You should always post the exact code that caused the error.

Anyway, to make your code work you should fetch your data before closing the cursor. Because after closing there obviously will be be no data to fetch.

$result = $pdo->query("call aeroplane")->fetchAll();
$result->closeCursor();
$result1 = $pdo->query("select * from plane_maker")->fetchAll();

The error has nothing to do with buffered queries but with stored procedures' behavior.

answered Apr 24, 2017 at 11:55

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

multiple sql queries running issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related