How I can group category and subcategory and display all the results from misqli DB for header meniu? I made a table called category with 3 columns id, category, parent then I grouped the category using parent for the with value parent=0 for the category and the subcategory value with corresponding parent with the main category. But the problem is the code shows me only the first main category and the first corresponding subcategory, and I want to display all.
so I have 3 category values (fruits id=1 , parent=0, cars id=2 parent=0, clothes id=3 parent=0 with parent=0 that means their are a category header) and e.g. for cars I have 2 subcategory value (moto id=52 parent=2 auto id=34 parent=2 ) you got the idea...
$parentTake = '0';
$categorieDrowdown = "";
$categorieDrowdownLista = "";
$stmt = $con->prepare('SELECT id, category, parent FROM category WHERE parent=?');
$stmt->bind_param('i', $parentTake);
$stmt->execute();
$existCount = $stmt->store_result();
if($existCount == 0){
echo "nU ai nici o categorie adaugata";
exit();
}
$stmt->bind_result($idParent, $categorie, $parent);
while ($stmt->fetch()) {
$idParent;
$categorie;
$categorieDrowdown .= '<li class="dropdown"> <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false"> '.$categorie.'<span class="caret"></span></a>
';
$stmt = $con->prepare('SELECT id, category, parent FROM category WHERE parent=?');
$stmt->bind_param('i', $idParent);
$stmt->execute();
$existCount = $stmt->store_result();
if($existCount == 0){
echo "nU ai nici o subcategorie lista adaugata";
exit();
}
$stmt->bind_result($idLista, $categorieLista, $parentLista);
while ($stmt->fetch()) {
$idLista;
$categorieLista;
$categorieDrowdownLista .= ' <ul class="dropdown-menu">
<li><a href="#">'.$categorieLista.'</a></li>
</ul>
</li>
';
}//close while subcategorie
}//close while categorie first select
and the display
<div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
<ul class="nav navbar-nav">
<li class="active"><a href="#">Home </a></li>
<li><a href="#">Admin CMS</a></li>
<?php echo $categorieDrowdown;//CATEGORIE PRIMA ex: fructe?>
<?php echo $categorieDrowdownLista;//CATEGORIE SECUNDA ex:mere?>
</ul>
Sorry for my English and my problem but I'm newbie
You are overwriting the first $stmt with the second $stmt variable, which is why you are getting only the first menu item and the subcategories for this. I have made some adjustments to your code and added an alternative way of doing it, which doesn't have such a performance hit as the loop queries. Also worth mentioning if you don't have a subcatergory you shouldn't exit the script. Simply make an empty placeholder so you can continue with the page loading.
$stmt->store_result(); doesn't returns the number of rows. You call it to have access to $stmt->num_rows property, which will give you the results of found rows. Notice I haven't stored the result in the second example ( mysqli::store_result() ), I'm just filling the $menu array, without even the need to know if I have results.
$start = microtime( true );
$output = '';
$stmt = $con->prepare( '
SELECT
id, category, parent
FROM category
WHERE parent = 0
' );
$stmt->execute();
$stmt->store_result();
// YOU DON'T WANT TO DO THIS... o.O it's fine for debuging tho
//~ if($stmt->num_rows == 0){
//~ echo "nU ai nici o categorie adaugata";
//~ exit();
//~ }
// INSTEAD
if( $stmt->num_rows > 0 ){
$stmt->bind_result($idParent, $categorie, $parent);
while ($stmt->fetch()){
$output .= '
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">
' . $categorie . ' <span class="caret"></span>
</a>
<ul class="dropdown-menu">';
// rename $stmt to $stmt2 so you'll not overwrite the initial statement
$stmt2 = $con->prepare('SELECT id, category, parent FROM category WHERE parent=?');
$stmt2->bind_param('i', $idParent);
$stmt2->execute();
$stmt2->store_result();
// NOR THIS... o.O
//~ if($stmt2->num_rows == 0){
//~ echo "nU ai nici o subcategorie lista adaugata";
//~ exit();
//~ }
// INSTEAD
if( $stmt2->num_rows > 0 ){
$stmt2->bind_result($idLista, $categorieLista, $parentLista);
while ($stmt2->fetch()){
$output .= '
<li>
<a href="#">'.$categorieLista.'</a>
</li>';
} //close while subcategorie
}
$output .= '
</ul>
</li>';
} // close while categorie first select
}
echo $output, "\n\n\n";
echo 'Duration: ', microtime( true ) - $start, "\n\n\n";
//-----------------------------------------------------------
// I LIKE IT THIS WAY... ONLY FOR 1 SUBLEVEL THO
// for unlimited dept sub categories you need different approce
$start = microtime( true );
unset( $output );
$output = '';
$menu = array();
$sel = $con->prepare( '
SELECT
id, category, parent
FROM category
ORDER BY parent, category ASC
' );
$sel->execute();
$sel->bind_result( $id, $category, $parent );
while( $sel->fetch() ){
if( ! $parent ){ // same as $parent == 0
$menu[ $id ] = array(
'name' => $category,
'sub' => array(),
);
} else {
$menu[ $parent ]['sub'][ $id ] = array(
'name' => $category,
);
}
}
$sel->close();
foreach( $menu as $id => $item ){
$output .= '
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">
' . $item['name'] . ' <span class="caret"></span>
</a>
<ul class="dropdown-menu">';
foreach( $item['sub'] as $subid => $subitem ){
$output .= '
<li>
<a href="#">' . $subitem['name'] . '</a>
</li>';
}
$output .= '
</ul>
</li>';
}
//~ echo '<pre>', var_dump( $menu ), '</pre>';
echo $output, "\n\n\n";
echo 'Duration: ', microtime( true ) - $start, "\n\n\n";
Sample output:
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">
fruits <span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li>
<a href="#">apple</a>
</li>
<li>
<a href="#">orange</a>
</li>
<li>
<a href="#">banana</a>
</li>
<li>
<a href="#">Pen Pineapple Apple Pen</a>
</li>
</ul>
</li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">
cars <span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li>
<a href="#">moto</a>
</li>
<li>
<a href="#">auto</a>
</li>
</ul>
</li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">
clothes <span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li>
<a href="#">skirt</a>
</li>
</ul>
</li>
Duration: 0.0020129680633545
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">
cars <span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li>
<a href="#">auto</a>
</li>
<li>
<a href="#">moto</a>
</li>
</ul>
</li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">
clothes <span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li>
<a href="#">skirt</a>
</li>
</ul>
</li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">
fruits <span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li>
<a href="#">apple</a>
</li>
<li>
<a href="#">banana</a>
</li>
<li>
<a href="#">orange</a>
</li>
<li>
<a href="#">Pen Pineapple Apple Pen</a>
</li>
</ul>
</li>
Duration: 0.00058293342590332
Database structure and data
CREATE TABLE `category` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category` varchar(255) NOT NULL,
`parent` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `parent` (`parent`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `category` (`id`, `category`, `parent`) VALUES
(1, 'fruits', 0),
(2, 'cars', 0),
(3, 'clothes', 0),
(4, 'moto', 2),
(5, 'auto', 2),
(6, 'apple', 1),
(7, 'orange', 1),
(8, 'banana', 1),
(9, 'skirt', 3),
(10, 'Pen Pineapple Apple Pen', 1);