Php/MySQL loop

[mikenl]

Hey,

 

I cannot figure out how to make this loop work less db intensive, does anyone know howto do this?

 

function shop_menu(){
    
dbsystem();
  
$query = "

SELECT 

category.cat,
category.un_id,
category.title,
category.text

FROM category

GROUP BY category.un_id

";

$query = mysql_query($query); 

$menu = array(); 
while ($row = mysql_fetch_row($query)) 
{ 
     $menu[] = $row; 
} 

$counter = 0;

$x = count($menu);

for($i = 0; $i < $x; $i++) {

   if(!empty($menu[$counter])) {

   
  ?>
            <table width="170" border="0" cellpadding="0" cellspacing="0">
              <tr>
                <td height="20" class="menue"><a href="../sortiment/?cat=<? echo $menu[$counter][1] ?>" class="textgreenbold"><? echo $menu[$counter][0] ?></a></td>
              </tr>
            </table>
            <table width="170" border="0" cellspacing="0" cellpadding="0">
              <?
		  
		   dbsystem();
$query="SELECT sub,un_id FROM subcategory WHERE cat = ".$menu[$counter][1]."";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
$cat = $row[0];
$id = $row[1];
		  
		  ?>
              <tr>
                <td width="10"> </td>
                <td width="160" class="menue"><a href="../sortiment/?sub=<? echo $id ?>" class="men"><? echo $cat ?></a></td>
              </tr>
              <?  } ?>
            </table>
            <? } 

		  $counter++;
		  }
		  }
		  ?>

[Psycho]

First off, there are some problems with the looping constructs in the code above. First it creates a $counter, then it usees a for loop with an $i counter. Why both? In any event you should have used neither. A foreach loop is a better construct for arrays. And, to go even further there is no need to dump the db results into an array and then loop through the array. You would only need to do that if you need to reuse the results from the db query several times. Also, you should always use <?php not <?. Last critique - you should never used nested queries - that is what is making the script db intensive. You can get all the info you need with one query.

 

Anyway, this will do the same thing more efficiently. I'm sure there are some typos as I don't have your data to test against.

 

<?php

function shop_menu(){
    
  dbsystem();

  // One query grabing all the pertinent data
  $query = "
    SELECT category.cat, category.un_id as cat_id, category.title, category.text,
           subcategory.sub, subcategory.un_id as subcat_id
    FROM category, subcategory
    WHERE category.un_id = subcategory.cat
    ORDER BY category.un_id";

  $query = mysql_query($query); 

  $current_catid = '';

  while ($row = mysql_fetch_assoc($query)) {

    if ($current_catid!=$row['un_id']) {

      // Close previous subcat table if not 1st time through
      if ($current_catid != '') { echo '</table>'; }

      //First entry for this category - show category header
      $current_catid=$row['un_id'];
      echo '
        <table width="170" border="0" cellpadding="0" cellspacing="0">
          <tr>\n";
            <td height="20" class="menue"><a href="../sortiment/?cat='.$row['cat_id'].' class="textgreenbold">'.$row['cat'].'</a></td>
          </tr>
        </table>
        <table width="170" border="0" cellspacing="0" cellpadding="0">';
    } // End if 

    //Display the subcat data
    echo '
          <tr>
            <td width="10"> </td>
            <td width="160" class="menue"><a href="../sortiment/?sub='.$row['subcat_id'].'" class="men">'.$row['sub'].'</a></td>
          </tr>';

  } //End while loop

  echo '</table>';

}
?>

[mikenl]

First of all, thx for assisting

 

I did the nested looping because there are main categories and subcategories and the subcategories must be listed as a submenu under each main category, like:

 

Main1

  sub1

  sub2

  sub3

Main 2

  sub 1

  sub 2

 

This code lists all subcategories as one list, without the main categories.

[Psycho]

Again, you do not need nested queries. The code I posted was designed to do exactly what you asked for. As I said, I can't test it since I don't have your database. Did you even try it?

[mikenl]

Yes I tried and it resulted in a list with all subcategories, no categories. I tried changing the code but I didn't manage to make it work

[mikenl]

UPDATE: Played with it again and then noticed $row['un_id']. When I replaced this with $row['cat_id'] all worked! THX!