Posted By

fostersolutions on 11/25/08

Tagged

mysql
php

Versions (?)

Last Edited at 11/25/08 04:11pm

Statistics

Viewed 644 times

Favorited by 0 user(s)

Related snippets

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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

//connect to mysql and select db
		$link1 = @mysql_connect("p50mysql39.secureserver.net","frontstreetdb","FrOntStEet223_")
						or die("Could not connect to MySQL Server!");
		@mysql_select_db("frontstreetdb") or die("Could not select database");
		$query = 'SELECT c.category_name, p.* FROM tblProducts p
						INNER JOIN tblProductCategory c on
						p.prod_category = c.category_id;';		
		$result = mysql_query($query) or die(mysql_error()); 
 
$currentCategory = null;
$currentType = null;
 
if (mysql_num_rows($result)) {
 
echo "<ul>";
 
while ($row = mysql_fetch_assoc($result)) {
	if ($currentCategory != $row['category_name']) {
			 if ($currentCategory !== null) {
				 echo '</ul></li>';
			 }
			 echo '<li>'.htmlspecialchars($row['category_name']).'<ul>';
			 $currentType = null;
		}
 
		echo '<li><a href="'.$row['File_Path'].'">'.htmlspecialchars($row['prod_name']).'</a></li>';
 
		$currentCategory = $row['category_name'];
		$currentType = $row['prod_name'];
	}
	echo "</ul></li></ul>";
		mysql_close($link1);			 
 
}
?>