Convert query result to HTML list

Question

I'm really just dipping my toes into the PHP and MySQL waters. I've made this work the way I want it to, but I know it's ugly and I'd like to know how it would be done by someone who actually knows what they're doing.

    function displayCategoryMenu($db){  
       echo '<ul class="class">';
       foreach($db->query('SELECT * FROM categoryDb, thingDb WHERE category = category AND categoryId= "aaa0001"  ORDER BY category, thingName') as $row){
          if($categoryName!== $row['categoryName']){
            $categoryName= $row['CategoryName'];
            echo '<li class="category">'.$categoryName.'</li>';
          }
          echo'<li><a class="fader" href="?&amp;page='.$row['thingId']">'.$row['thingName'].'</a></li>';
      }
      echo '</ul>';

       echo '<ul class="class">';
       foreach($db->query('SELECT * FROM categoryDb, thingDb WHERE category = category AND categoryId= "aaa0002"  ORDER BY category, thingName') as $row){
          if($categoryName!== $row['categoryName']){
            $categoryName= $row['CategoryName'];
            echo '<li class="category">'.$categoryName.'</li>';
          }
          echo'<li><a class="fader" href="?&amp;page='.$row['thingId']">'.$row['thingName'].'</a></li>';
      }
      echo '</ul>';
      $db=null;
    }

This basically results in an unordered list menu, where the first child acts as a header:

    <ul>
       <li>category1Name</li>
       <li><a>thing</a></li>
       <li><a>thing</a></li>
       <li><a>thing</a></li>
    </ul>
    <ul>
       <li>category2Name</li>
       <li><a>thing</a></li>
       <li><a>thing</a></li>
       <li><a>thing</a></li>
    </ul>

or:

Category1

Thing
Thing
Thing

Category2

Thing
Thing
Thing

and so forth...

The only difference in each ul's code is the "categoryId" (aaa0001, aaa0002, etc...) I can only imagine that I'm going about this the hard way, with the ugliest code that I can muster. I'd love to see how it should be done.

Well done on the question. Up vote, plus some proposed edits. I dropped two tags as less-relevant and revised the title to "Convert query result to HTML list" (you may not see that change right away, it must be peer-reviewed; or you can revise it yourself). — Smandoli
– Smandoli, Commented Jun 28, 2012 at 10:26

strnk · Accepted Answer · 2012-06-28 10:11:26Z

You should store the list of category ids somewhere in an array, an iterate over that while using a variable in place of the category ID. Something like :

function displayCategoryMenu($db){  
    $categoryIds = array('aaa0001', 'aaa0002');

    foreach ($categoryIds as $categoryId) {
        echo '<ul class="class">';
        foreach($db->query('SELECT * FROM categoryDb, thingDb WHERE category = category AND categoryId= "' . $categoryId . '"  ORDER BY category, thingName') as $row) {
            if($categoryName !== $row['categoryName']){
                $categoryName = $row['CategoryName'];
                echo '<li class="category">' . $categoryName . '</li>';
            }
            echo '<li><a class="fader" href="?&amp;page=' . $row['thingId'] . '">' . $row['thingName'] . '</a></li>';
        }
        echo '</ul>';
    }
}

Here the array $categoryIds contain the list of IDs you have to display, you can generate it dynamically or pass it as a function parameter as you wish, it's easily expandable. The foreach loop iterate over each element of this array and do exactly the same the HTML display and SQL query job for each of the ID denoted by the $categoryId variable in the loop.

Sidenote about your $db = null; line : if you don't pass the $db function parameter as a reference, modifying its value in your function the way yo did it will not have any influence on the original variable, this line is unnecessary here.

Gustav Bertram · Accepted Answer · 2012-06-28 12:16:34Z

Using the same functions you did, I would select all the categories and things at the same time, like so:

function renderCategoryMenus($db, $category_id_array)
{  
   // Convery array('a00','b00') to string "'a00','b00'"
   $category_ids = "'" . implode("','", $category_id_array) "'";

   // Change * to just the fields you need
   // Use a join
   // WHERE id IN ('a00','b00')

   $query = 
      'SELECT CategoryName, thingName 
      FROM categoryDb 
      INNER JOIN thingDb 
      ON categoryDb.category = thingDb.category 
      WHERE categoryId IN \''. $category_ids .'\'  
      ORDER BY category, thingName'; 

   $results = $db->query($query);

   // Initialize your variables
   $output = '';
   $categoryName = '';

   foreach($results as $row) 
   {
      if ($categoryName != $row['categoryName']) // If this is a new category
      {
         if ($categoryName != '') // And it is NOT the first new category
         {
            $output .= '</ul>'; // End off the previous category
         }         

         $categoryName = $row['CategoryName'];

         // Store your output into a variable so you can return it
         $output .= '<ul class="class"><li class="category">' . 
            $categoryName . '</li>';
      }

      $output .= '<li><a class="fader" href="?&amp;page=' . 
         $row['thingId'] . '>' . $row['thingName'] . '</a></li>';
   }

   // End off the last category
   if ($output != '') // If we had any categories whatsoever
   {
      $output .= '</ul>';
   }

    return $output;
}    

echo renderCategoryMenus($db, array('aaa0001', 'aaa0002'));

A simpler way would be to use an Object Relational Mapper like Yii has.

With Yii, you could do something as simple as this:

function renderCategoryAsMenu($category)
{  
   $output .= '<ul class="class"><li class="category">' . 
      $categoryName . '</li>';      

   // This bit actually selects all 'things' in this 'category'
   foreach ($category->things as $thing)   
      $output .= '<li><a class="fader" href="?&amp;page=' . 
         $thing->thingId . '>' . $thing->thingName . '</a></li>';
   }

   $output .= '</ul>';

    return $output;
}    

$category_keys = array('a00','b00');
$categories = Category::model()->findAllByPk($category_keys);

$output = '';
foreach ($categories as $category)
{
   $output .= renderCategoryAsMenu();
}
echo output;

You could even make renderCategoryAsMenu a function on the Model, in which case you could just say $category->renderAsMenu().

EricG · Accepted Answer · 2012-06-28 10:19:01Z

0

Depending on which categoryIDs you wish to show:

I would suggest 1 query and loop through the resultset.

That would be either

1) without the WHERE clausule (unless you need it for the join, still, since you have 2 WHERE conditions)

SELECT * FROM categoryDb, thingDb ORDER BY category, thingName

2) or expand the WHERE

WHERE categoryId="aaa001" OR categoryId="aaa002" ...

Also I would suggest:

if($categoryName!== $row['categoryName'])
{
    echo '<li class="category">' . $row['CategoryName'] . '</li>';
}

You could rename the function to "writeCategoryMenu".

answered Jun 28, 2012 at 10:19

EricG

3,8491 gold badge25 silver badges35 bronze badges

Comments

SDC · Accepted Answer · 2012-06-28 15:21:03Z

Try something like this.. i think it's much easier... and for debugging i'd advice you to use i.e. $sql_query:

<?php
function displayCategoryMenu($db){  

    $sql_query  =  'SELECT * FROM 
                        categoryDb 
                    LEFT JOIN thingDb
                        ON categoryDb.category = thingDb.category
                    WHERE
                        thingDb.visible = 1                     
                    ORDER BY 
                        thingDb.category ASC, 
                        thingDb.thingName';

    $result = $db->query($sql_query);
    if(!mysql_error()){

    foreach ($result as $row) {
        if($currentcategory <> $row['CategoryName']){
            $output .=  '<ul class="class">';
            $output .=  '   <li class="category">'.$categoryName.'</li>';
            $output .=  '   <li><a class="fader" href="?&amp;page='.$row['thingId'].'">'.$row['thingName'].'</a></li>';
            $output .=  '</ul>';
        } else {
            $output .=  '<li><a class="fader" href="?&amp;page='.$row['thingId'].'">'.$row['thingName'].'</a></li>';
        }

        $currentcategory = $row['CategoryName'];

    }
    }else{
        $output = mysql_error().'<br>QUERY: '.$sql_query;
    }

    echo $output;

?>

Collectives™ on Stack Overflow

Convert query result to HTML list

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related