Jump to content

Php/MySQL loop


mikenl

Recommended Posts

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++;
		  }
		  }
		  ?>

Link to comment
https://forums.phpfreaks.com/topic/48139-phpmysql-loop/
Share on other sites

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

}
?>

Link to comment
https://forums.phpfreaks.com/topic/48139-phpmysql-loop/#findComment-235274
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/48139-phpmysql-loop/#findComment-235322
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.