how to loop multiple times through 1 php/mysql result

Question

wondering if there's an simpler way to do this...

i have a table with a bunch of rows and need to display them separately based on specific values on 1 column named "page_group". right now it does work when i reset the pointer of the result back to zero but wondering if a simpler way to code because got at least 15 different sections to group results ...?

//this is the query
$q_home = mysql_query("SELECT * FROM pages WHERE page_nav = 'No' ORDER BY page_group ASC");

//this would show results 1, showing all rows that have the value "sample1"
<h3>Result 1</h3>
<ul>
<? 
    while($r_nav = mysql_fetch_assoc($q_home)){
      $group_folder = $r_nav['page_group'];
      if ($r_access["$group_folder"] == "Yes" && $group_folder == 'sample1') { 
            $access = 'block;'; 
        } else { 
            $access = 'none;'; 
        } {;
            echo '<li style="display:'.$access.'"><a href="'.$group_folder.'/'.$r_nav['page_url'].'.php">'.$r_nav['page_name'].'</a></li>';
      }
    }
?>
</ul>   

//this would be result 2 showing all rows that have value "sample2"
<h3>Result 2</h3>
<ul>
<? 
    mysql_data_seek($q_home, 0);
   while($r_nav2 = mysql_fetch_assoc($q_home)){
      $group_folder = $r_nav2['page_group'];
      if ($r_access["$group_folder"] == "Yes" && $group_folder == 'sample2') { 
            $access = 'block;'; 
        } else { 
            $access = 'none;'; 
        } {;
            echo '<li style="display:'.$access.'"><a href="'.$group_folder.'/'.$r_nav2['page_url'].'.php">'.$r_nav2['page_name'].'</a></li>';
      }
    }
?>
</ul>

Putting SQL together with business logic and HTML generation code is not good. You want classes for each responsibility there. One that answers the question you are asking to the storage backend and thus runs the SQL. One that generates a view (ie HTML) from some dumb data structure. Such a dumb data structure. And a use case class that uses your store class to run the query, then construct a data object from the result and gives it to a view to visualize it. 4 different responsibilities at least. — Jeroen De Dauw, Mar 05 '13 at 02:42

score 0 · Answer 1 · answered Mar 05 '13 at 02:39

You have the mysql_fetch in a loop, so just don't stop the loop on a specific $group_folder (remove that part of the if clause):

<ul>
<?php
  while($r_nav = mysql_fetch_assoc($q_home)){
    $group_folder = $r_nav['page_group'];
    if ($r_access["$group_folder"] == "Yes")
      $access = 'block;';
    else
      $access = 'none;';
    echo '<li style="display:'.$access.'">
    <a href="'.$group_folder.'/'.$r_nav['page_url'].'.php">
    '.$r_nav['page_name'].'</a></li>';
  }
?>
</ul>

score 0 · Answer 2 · answered Mar 05 '13 at 02:44

Something like this loop will be better solution:

$q_home = mysql_query("SELECT * FROM pages WHERE page_nav = 'No' ORDER BY page_group ASC");
$x = 1;
<? while( $r_nav = mysql_fetch_assoc($q_home) ) { ?>
<h3>Result <?= $x; ?></h3>
<ul>
<?php
  $group_folder = $r_nav['page_group'];
  if( $r_access["$group_folder"] == "Yes" && $group_folder == 'sample1') {
    $access = 'block;';
  }
  else { 
    $access = 'none;';
  }
  echo '<li style="display:'.$access.'"><a href="'.$group_folder.'/'.$r_nav['page_url'].'.php">'.$r_nav['page_name'].'</a></li>';
  }
  $x++;
?>
</ul>
<?php } ?>

how to loop multiple times through 1 php/mysql result

2 Answers2