PHP Logic (Printing Categories and Subcategories in groups)

[Nibinaear]

Hi, haven't been here in ages. I have an issue with PHP when trying to print out an object of values from my database. The values are categories actually which appear in groups.

The html looks like this:

 <div class="cat-block-group">

   <div class="cat-block">
     <h2>Security</h2>
     <ul class="cat-list">
       <li>one</li>   
       <li>two</li>   
       <li>three</li>   
     </ul>
   </div>
   
   <div class="cat-block">
     <h2>Hardware</h2>
       <ul class="cat-list">
         <li>one</li>   
     <li>two</li>   
     <li>three</li>   
      </ul>
    </div>

    </div> 

So it prints a group, then two blocks then ends the group. Each block contains a category such as Security plus all the security subcategories. The HTML/css works find and prints great, the problem I have is in printing it all out programatically.

The database looks like this:

[cat_id]  [catname]  [subcatname]
1            Security      Viruses
2            Security      Firewalls
3            Hardware   Troubleshooting etc 

All this is changed so it can look nice and simple for the forum.

My PHP currently looks like this. It's really complicated, I've tried to use a series of booleans to simplify things.

I've commented it throughout:

<? 
   $last='';
     $pos=0;
      $prevCat='';$prevCat2='';
    
      ?>
    <?     
         $i=2;//Begins at one because of next 'if'
        $j=0;//index
        $lastCat='Security';
      foreach($cats->result() as $key =>$value)
        {
            if($j==0)//First time through loop
            {
                $startBlock=true;
                $endBlock=false;
                $closingBlock=false;
            }
            else//Not first time
            {
                if($lastCat==$value->catname)//Same main category as last time = No new group / block needed.
                {
                    $startBlock=false;//Start a new <ul> container & <div class="cat-block">
                    $endBlock=false;//Finish <ul> & </div>
                    $closingBlock=false;//Close group of 2 blocks, move to new row.
                }
                else//Different category reached, new group / block needed.
                {
                    if($i==1)//Position left=1, pos right=2
                    {
                        $i=2;
                        $startBlock=true;
                        $endBlock=false;
                        $closingBlock=true;
                    }
                    elseif($i==2)
                    {
                        $i=1;
                        $closingBlock=true;
                        $startBlock=true;
                        $endBlock=false;                        
                    }
                }                    
            }

            if($closingBlock)//Close off last group
            {
                ?>
                        </ul>
                    </div>
                <?
            }
            
            if($i==1)//Left
            {
                if($startBlock)//New block & required
                {
                    ?>
                    <div class="cat-block">
                        <h2><?=$value->catname?></h2>
                        <ul class="cat-list">
                <?
        }
        ?>
                            <li><a href="<?=base_url().$value->sname.'/'.$value->subcat_id;?>/"><?=$value->sname;?></a></li>   
                <?
        if($endBlock)//New block & required
        {
        ?>
                        </ul>
                    </div>
                    <?
                }
            }
            elseif($i==2)//Right
            {
                if($startBlock)//New block & required
                {
                ?>
        <div class="cat-block">
          <h2><?=$value->catname?></h2>
          <ul class="cat-list">
        <?
                }
                ?>
            <li><a href="<?=base_url().$value->sname.'/'.$value->subcat_id;?>/"><?=$value->sname;?></a></li>   
        <?
                if($endBlock)//New block & required
                {
                ?>
          </ul>
        </div>
                <?
                }
            }
            
            $j++;
            $lastCat=$value->catname;//Record last category for next time.
        }
?>

I've been at this one for weeks and I'm stuck, any help much appreciated.

[Nibinaear]

By the way I'm using MySQL 5.0.41, PHP 5.2.3 and Apache 2.2.10 on Windows XP SP3.

[PeejAvery]

You'll have to do an initial query to get all of the category names into an array. Then, query for each subcategory. The code below isn't tested, but should work.

<?php
$query = "SELECT catname FROM database";
while ($row = mysql_fetch_object(mysql_query($query))) {
  $currentCategory = $row->catname;
?>
   <div class="cat-block">
     <h2><?php echo $currentCategory; ?></h2>
     <ul class="cat-list">
<?php
  $subquery = "SELECT * FROM database WHERE catname = '" . $currentCategory . "'";
  while ($subrow = mysql_fetch_object(mysql_query($subquery))) {
?>
       <li><?php echo $subrow->subcatname; ?></li>
<?php } /* subquery while */ ?>
     </ul>
   </div>
<?php
} /* query while */

mysql_free_result($row);
mysql_free_result($subrow);
?>

[Nibinaear]

Thanks that worked great. The only thing I'm a little concerned about is that this is on the homepage and there are now 9 db queries for the categories section alone. Won't this slow it down a bit?

[PeejAvery]

It will only be a problem if you have a very large database and many loads per second on this page.

Just make sure that your cat_id is an index. Personally, I'd suggest PRIMARY KEY with auto-increment.That will make loads so much faster!

[Nibinaear]

It will only be a problem if you have a very large database and many loads per second on this page.

Just make sure that your cat_id is an index. Personally, I'd suggest PRIMARY KEY with auto-increment.That will make loads so much faster!

The cat_id and subcat_id are pks so I would think that they will be indexes automatically? The cats table only has 8 categories and the subcats table only has 50 or so rows. But this is the homapge so this will get lots of hits. Is there no way of doing this code without the extra queries? I've tried myself for ages so know how tough it is.

[PeejAvery]

Still, how often is this going to be hit? I have a 3rd party hosted server that is taking thousands of inserts an hour. Inserts are much harder on a database than selects!

If its multiple thousands every hour...you could always create a session within a required file just for that data. Have it update every 24 hours. Once again...code is from the top of my head, not tested.

<?php
session_name('db_cache');
session_start();
// initialize the data every 24 hours...so check the actual day of the month
if (@$_SESSION['last_update'] !== date('d')) {
  // create an array here from which you can pull the data
  $db_cache = array();

  // now loop through the database and save the 
  $query = "SELECT catname FROM database";
  while ($row = mysql_fetch_object(mysql_query($query))) {
    $currentCategory = $row->catname;
    $subquery = "SELECT * FROM database WHERE catname = '" . $currentCategory . "'";
    while ($subrow = mysql_fetch_object(mysql_query($subquery))) {
      // first dimension will be the category name
      // second dimension is the sub-category
      $db_cache[$currentCategory][] = $subrow->subcatname;
    }
  }

  $_SESSION['last_update'] = date('d');
}
?>

[Nibinaear]

Nice idea. This would be something to implement if the website got very popular (optimistic scenario). I just always wonder about have multiple queries in a page. 9 for for the cats and subcats, then news articles in the sidebar, then events in the sidebar, it all adds up. But if you're saying that it won't have an impact then I won't worry about that stuff anymore. I've always focused on the least queries approach so to see 9 in one go was a concern.