echo as a list, loop within a loop

Question

I have a data set which looks similar to:

---------------------------------------------------------------------
id | category | sub_category | category_href | sub_category_href    |
01 | cat_1    | sub_cat 1    | cat/cat1.php  | cat/cat1/sub_cat1.php|
02 | cat_1    | sub_cat 2    | cat/cat1.php  | cat/cat1/sub_cat2.php|
03 | cat_2    | sub_cat 1    | cat/cat2.php  | cat/cat2/sub_cat1.php|
04 | cat_2    | sub_cat 2    | cat/cat2.php  | cat/cat2/sub_cat2.php|
---------------------------------------------------------------------

What I want to do with the data is have a layout like this:

<div>
  <h2 class="title">Cat1</h2>
  <p>
   <ul class="links">
      <li><a href="cat/sub_cat_1.php" target="_top">sub_cat_1</a></li>
      <li><a href="cat/sub_cat_2.php" target="_top">sub_cat_2</a></li>
  </p>
</div>
<div>
  <h2 class="title">Cat2</h2>
  <p>
    <ul class="links">
        <li><a href="cat/sub_cat_1.php" target="_top">sub_cat_1</a></li>
        <li><a href="cat/sub_cat_2.php" target="_top">sub_cat_2</a></li>
    </p>
</div>

So my question is how would I do this with php so that I can change the categories and sub categories from my db. The following is what I have below but I need a second loop for the sub categories or it will only have one sub category per category. Could someone point me in the right direction for the sub_cat loop. Thank you

EDIT:

So my question is now that I have distinct categories how I can echo the appropriate sub categories?

Thanks

<?php
include('connect.php');
$result = mysql_query("SELECT DISTINCT category FROM categories")
    or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
    echo "<div>";
    echo "<h2 class='title'>" . $row['category'] . "</h2>";
    echo "<p>";
    echo "<ul class='links'>";
    $result1 = mysql_query("SELECT * FROM categories ")
        or die (mysql_error());
    while ($row = mysql_fetch_array($result1)) {
        echo "<li><a href='" . $row['sub_category_href'] . " target='_top'>" . $row['sub_category'] . "</a></li>";
    }
    echo "</ul>";
    echo "</p>";
    echo "</div>";
}
?>

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — Madara's Ghost
– Madara's Ghost, Commented Sep 30, 2012 at 20:39
Yep will check that out any help on the function would be appreciated though — Owen
– Owen, Commented Sep 30, 2012 at 21:14

Marko Novakovic · Accepted Answer · 2012-09-30 21:16:51Z

That code could be looked like this:

<?php
include('connect.php');
$result = mysql_query("SELECT DISTINCT category FROM categories")
    or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
    echo "<div>";
    echo "<h2 class='title'>" . $row['category'] . "</h2>";
    echo "<p>";
    echo "<ul class='links'>";
    $result1 = mysql_query("SELECT * FROM categories WHERE category = {$row['category']}")
            or die (mysql_error());
    while ($row1 = mysql_fetch_array($result1)) {
        echo "<li><a href='" . $row1['sub_category_href'] . " target='_top'>" . $row1['sub_category'] . "</a></li>";
    }
    echo "</ul>";
    echo "</p>";
    echo "</div>";
}
?>

But I suggest you to optimize in this manner:

<?php
include('connect.php');
$result = mysql_query("SELECT * FROM categories")
    or die(mysql_error());
$cats = array();
while ($row = mysql_fetch_array($result)) {
    $category = $row['category'];
    $sub_category = $row['sub_category'];
    $sub_category_href = $row['sub_category_href'];
    $cat_hrefs[$category][$sub_category] = $sub_category_href;
}
foreach ($cat_hrefs as $category => $sub_category_hrefs) {
    echo "<div>";
    echo "<h2 class='title'>" . $category . "</h2>";
    echo "<p>";
    echo "<ul class='links'>";
    foreach ($sub_category_hrefs as $sub_category => $sub_category_href) {
        echo "<li><a href='" . $sub_category_href . " target='_top'>" . $sub_category . "</a></li>";
    }
    echo "</ul>";
    echo "</p>";
    echo "</div>";
}
?>

You do not need to make three queries, because you can do it in one query, which is more optimal.

david strachan · Accepted Answer · 2012-09-30 22:47:03Z

There is no need for four columns in database only sub_category & sub_category are required the other 2 category_href,sub_category_hrefare redundant as the can be generated from the first 2.

This version uses PDO which is recommended for new applications.

<div>
// connect to database 
<?php  
$host= "xxx";
$username="xxx";
$password="xxx";
$database="xxx";

// Opens a connection to a MySQL server
$connection=mysql_connect("localhost", $username, $password);
try {

// DBH means "D Handle"
// MySQL with PDO_MYSQL
 $DBH = new PDO("mysql:host=$host;dbname=$database", $username, $password);

}
catch(PDOException $e) {
  echo $e->getMessage();
}

// creating the statement
$STH = $DBH->query('SELECT * FROM  category  LIMIT 0 , 30');  
// setting the fetch mode  
$STH->setFetchMode(PDO::FETCH_ASSOC); 
 //Set up flag
 $test = 0;
while($row = $STH->fetch()) { 

 if($test == 0){
   echo "<h2 class= \"title\">".str_replace('_','',ucfirst($row['category']))."</h2>";
   $test = 1;
 }
 else{
   $test = 0;
 }
  echo "<li><a href=\"cat/".$row['category']."/".$row['sub_category'].".php\" target=\"_top\">".$row['sub_category']."</a></li>"; 

}  
// close the connection
$DBH = null;
?>
</div>

Collectives™ on Stack Overflow

echo as a list, loop within a loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related