Posted By

fostersolutions on 11/25/08


Tagged

mysql php lists


Versions (?)

Nested list from grouped sql


 / Published in: PHP
 

This will create a list grouped by the sorted field, which is an inner join on a category field.

  1. //connect to mysql and select db
  2. $link1 = @mysql_connect("p50mysql39.secureserver.net","frontstreetdb","FrOntStEet223_")
  3. or die("Could not connect to MySQL Server!");
  4. @mysql_select_db("frontstreetdb") or die("Could not select database");
  5. $query = 'SELECT c.category_name, p.* FROM tblProducts p
  6. INNER JOIN tblProductCategory c on
  7. p.prod_category = c.category_id;';
  8. $result = mysql_query($query) or die(mysql_error());
  9.  
  10. $currentCategory = null;
  11. $currentType = null;
  12.  
  13. if (mysql_num_rows($result)) {
  14.  
  15. echo "<ul>";
  16.  
  17. while ($row = mysql_fetch_assoc($result)) {
  18. if ($currentCategory != $row['category_name']) {
  19. if ($currentCategory !== null) {
  20. echo '</ul></li>';
  21. }
  22. echo '<li>'.htmlspecialchars($row['category_name']).'<ul>';
  23. $currentType = null;
  24. }
  25.  
  26. echo '<li><a href="'.$row['File_Path'].'">'.htmlspecialchars($row['prod_name']).'</a></li>';
  27.  
  28. $currentCategory = $row['category_name'];
  29. $currentType = $row['prod_name'];
  30. }
  31. echo "</ul></li></ul>";
  32. mysql_close($link1);
  33.  
  34. }
  35. ?>

Report this snippet  

You need to login to post a comment.