CODEWITHSUNDEEP
phpmysqlpdo
user2371684
user2371684

Reputation: 1555

multiple sql queries running issue

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

Upvotes: 0

Views: 94

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157870

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.

Upvotes: 1

Related Questions