I am trying to make a directory list using jQueryUI tabs and menu widgets. I have got the following code for obtaining the directory.
<?php
$db = new PDO('mysql:host='.$host.';dbname='.$databaseName,
$user,
$password,
array(
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
$menu = '';
$charTabs = '<div id="charTabs">'; //1
$db->exec('set names utf8');
$queryFirstLetters = $db->prepare('SELECT DISTINCT LEFT(organization, 1) as org FROM Organizations WHERE parent_id = 0 ORDER BY organization', array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
//Add each first characters to the charTabs
$queryFirstLetters->execute();
$charTabs .='<ul>'; //2
while($rowFL = $queryFirstLetters->fetch(PDO::FETCH_ASSOC))
{
$charTabs .= '<li><a href="#' . $rowFL['org'] . '">' . $rowFL['b'] . '</a></li>';//3
}
$charTabs .= '</ul>'; //4
//Create Divs corresponding to list items for each first letter.
$queryFirstLetters->execute();
while($rowFL = $queryFirstLetters->fetch(PDO::FETCH_ASSOC))
{
$charTabs .= '<div id="' . $rowFL['org'] . '">'; //5
//Get the parent organizations and add their children as sub menu items
$queryParentOrganizations = $db->prepare('SELECT * FROM Organizations WHERE parent_id = 0 AND LEFT(organization,1) = :p1 ORDER BY organization', array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
$queryParentOrganizations->bindValue(':p1', $rowFL['org'], PDO::PARAM_STR);
//This is the line 26 which crashes
$queryParentOrganizations->execute();
//This is the line 26 which crashes
echo '<script>$(function(){ $("#menu'.$rowFL['org'].'").menu(); });</script>';
$menu = '<ul id="menu'.$rowFL['org'].'">';
while($rowPO = $queryParentOrganizations->fetch(PDO::FETCH_ASSOC))
{
$queryChildOrganizations = $db->prepare('SELECT * FROM Organizations WHERE parent_id = :p1 ORDER BY organization', array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
$queryChildOrganizations->bindValue(':p1', $rowPO['id'], PDO::PARAM_INT);
$queryChildOrganizations->execute();
$menu .= '<li><a class="list" href="#">'.$rowPO['organization'].'</a>';
if ($queryChildOrganizations->rowCount() > 0 )
{
$menu .= '<ul>';
while($rowCO = $queryChildOrganizations->fetch(PDO::FETCH_ASSOC))
{
$menu .= '<li><a class="list" href="#' . $rowCO['id'] . '">' . $rowCO['organization'] . '</a></li>';
}
$menu .= '</ul>';
}
else
{
$menu .= '</li>';
}
}
$menu .= '</ul>';
$charTabs .= $menu . '</div>';
}
$charTabs .= '</div>';
echo $charTabs;
?>
Ignoring the preformance issuses, it works fine. But after uploading to the server I got the following errors which is related to PDO.
PHP 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 /home/server/public_html/directory/index.php:26
Stack trace:
#0 /home/server/public_html/directory/index.php(26): PDO->prepare('SELECT * FROM O...')
#1 /home/server/public_html/directory/index.php(26): include('/home/server/publ...')
#2 {main} thrown in /home/server/public_html/directory/index.php on line 26
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.