Filter results in page relating a id-parameter

Question

Hello,

I use a SELECT to get all the rows from the table tblnavigation. The rows are: idnav, idnavcat, prognav, progurl

$rsnav = mysql_query($query_rsnav, $concat) or die(mysql_error()); $row_rsnav = mysql_fetch_assoc($rsnav);

The idnavcat stands for the navigation category. Now i want to filter on that "idnavcat"; and show the results per category in a table.

        <?php do { ?>
        <tr>
          <td width="10%"><div align="center"><?php echo $row_rsnav['idnav']; ?></div></td>
          <td width="70%"><?php echo $row_rsnav['navprog']; ?></td>
          <td width="11%"><a href="nav_adj.php?id=<?php echo $row_rsnav['idnav']; ?>"><img src="images/edit.png" alt="" width="32" height="32" border="0" /></a></td>
          <td width="8%"><p><a href="nav_del.php?id=<?php echo $row_rsnav['idnav']; ?>"><img src="images/delete.png" alt="" width="32" height="32" /></a></p>              </td>
        </tr>
        <?php } while ($row_rsnav = mysql_fetch_assoc($rsnav)); ?>

I know it's possible with multiple selects; but i know it's slower. Has something to do with "while do" ? Or array ? Not sure; newby here ! :-)

Answer 1

I don't get exactly what you want to do. If i'm right, you want to implement a code-break structure.

You need, first, to sort the query by idnavcat and try to execute the code like this:

<?php

[...]

$query = "SELECT * FROM tblnavigation ORDER BY idnavcat;"; 

[...] // you execute the query here and do the rest of the stuff


// fetch the row
$row_rsnav = mysql_fetch_assoc($rsnav);
// stablish a default current category to force headers to be written
$current_category = -1;
do {

    if ($current_category != $row_rsnav['idnavcat']) 
    {
        $current_category = $row_rsnav['idnavcat'];

        // print headers (for example)
        ?>
        <tr>
          <td width="10%">ID NAV</td>
          <td width="70%">NAV PROG</td>
          <td width="19%" colspan="2">CATEGORY: <?php echo $current_category ?></td>
        </tr>
        <?
    }
?>
    <tr>
      <td width="10%"><div align="center"><?php echo $row_rsnav['idnav']; ?></div></td>
      <td width="70%"><?php echo $row_rsnav['navprog']; ?></td>
      <td width="11%"><a href="nav_adj.php?id=<?php echo $row_rsnav['idnav']; ?>"><img src="images/edit.png" alt="" width="32" height="32" border="0" /></a></td>
      <td width="8%"><p><a href="nav_del.php?id=<?php echo $row_rsnav['idnav']; ?>"><img src="images/delete.png" alt="" width="32" height="32" /></a></p></td>
    </tr>
<?php } while ($row_rsnav = mysql_fetch_assoc($rsnav)); ?>

But BTW since this way of programming is more or less ugly we should consider to use SMARTY or any templating system to format the results of our sourcecode to be able to modificate it quickly without affecting a lot the functionality.

Answer 2

Thanks Pablo; i made the following code:

<table width="100%" class="rowlight">
        <?php $navcat = 1; do { if ($navcat == $row_rsnav['idnavcat']) { ?>
        <tr>
          <td width="10%"><div align="center"><?php echo $row_rsnav['idnav']; ?></div></td>
          <td width="70%"><?php echo $row_rsnav['navprog']; ?></td>
          <td width="11%"><a href="nav_adj.php?id=<?php echo $row_rsnav['idnav']; ?>"><img src="images/edit.png" alt="" width="32" height="32" border="0" /></a></td>
          <td width="8%"><p><a href="nav_del.php?id=<?php echo $row_rsnav['idnav']; ?>"><img src="images/delete.png" alt="" width="32" height="32" /></a></p>              </td>
        </tr>
        <?php } ?>
        <?php } while ($row_rsnav = mysql_fetch_assoc($rsnav)); ?>
  </table>

But i need to display another 4 tables with other $navcat on the same page. I know using the fetch-command; the recordset will be read once. Is there another command to fix this ? Thanks.

Answer 3

I stepped over to an array; and read it out via a foreach loop.

while ($row_rsprog = mysql_fetch_array($rsprog)) {
  $data[$row_rsprog['idprog']] = array(
'idprog' => $row_rsprog['idprog'],
'progtitel' => $row_rsprog['progtitel'],
'idnavcat' => $row_rsprog['idnavcat']
);
}

And i read it out with:

foreach ($data as $id => $va){
echo 'blablabla';
}

But it reads the whole array. I want only to display the arraylines with a specific idnavcat-value. How can this be done ? Thanks for your help !