how to Group header meniu category and subcategory mysqli using stmt statement results and display by parent

Question

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 .= '  '.$categorie.'       
            ';      
                    
    $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 .= ' 
        '.$categorieLista.'
            
              
            ';
        }//close while subcategorie
    }//close while  categorie first select

and the display

   
      
        Home 
        Admin CMS

Sorry for my English and my problem but I'm newbie

Mujnoi Gyula Tamas · Accepted Answer

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 .= '
        
            
                ' . $categorie . ' 
            
            ';

        // 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 .= '
                
                    '.$categorieLista.'
                ';
            } //close while subcategorie
        }
        $output .= '
            
        ';
    } // close while  categorie first select
}

echo $output, "


";
echo 'Duration: ', microtime( true ) - $start, "


";

//-----------------------------------------------------------

// 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 .= '
    
        
            ' . $item['name'] . ' 
        
        ';
    foreach( $item['sub'] as $subid => $subitem ){
        $output .= '
            
                ' . $subitem['name'] . '
            ';
    }
    $output .= '
        
    ';
}
//~ echo '', var_dump( $menu ), '';
echo $output, "


";
echo 'Duration: ', microtime( true ) - $start, "


";

Sample output:

        
            
                fruits 
            
            
                
                    apple
                
                
                    orange
                
                
                    banana
                
                
                    Pen Pineapple Apple Pen
                
            
        
        
            
                cars 
            
            
                
                    moto
                
                
                    auto
                
            
        
        
            
                clothes 
            
            
                
                    skirt
                
            
        


Duration: 0.0020129680633545



    
        
            cars 
        
        
            
                auto
            
            
                moto
            
        
    
    
        
            clothes 
        
        
            
                skirt
            
        
    
    
        
            fruits 
        
        
            
                apple
            
            
                banana
            
            
                orange
            
            
                Pen Pineapple Apple Pen
            
        
    


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);

how to Group header meniu category and subcategory mysqli using stmt statement results and display by parent

Answers (1)

Related Questions