unordered list from mysql table

[quasiman]

I am just not getting what I'm doing wrong.  I want to create a hierarchical list from a database, but it seems I can’t get this to order the items correctly.

 

I want it to look like:

• Item 1

• Item 2

            o Subitem1

            o Subitem2

• Item 3

 

Here's my code - any help would be greatly appreciated!

<ul>
<?php

    mysql_connect("host", "login", "password");
    mysql_select_db("database");
displayChildren(1);
function displayChildren($id)
{
$sql = "SELECT * FROM categories WHERE cat_parent_id = $id";

if($result = mysql_query($sql))
{
while($row = mysql_fetch_assoc($result))
{
if($row['cat_parent_id'] > $id)
{
echo "<ul><li>{$row['cat_name']}";
displayChildren($row['cat_id']);
echo "</li>\n";
}else {
echo "<li>{$row['cat_name']}";
displayChildren($row['cat_id']);
echo "</li>\n";
}
}
}
}

?>
</ul>

[boo_lolly]

where's your logic to know if there is a sub-bulleted list needed in your database? have you checked the database to make sure the values are correct? could you post your database table we are querying here?

 

also, what is this about?

if($result = mysql_query($sql))

 

that's not necessary. i'm not sure what you're tryin to do there.

[quasiman]

Thanks for the reply,

 

"cat_parent_id" and "cat_id" determine whether an item is first or second level.

 

You're right - it wasn't needed...not sure why I still had that in!

 

Here's my table data:

 

CREATE TABLE `categories` (

  `cat_id` int(11) unsigned NOT NULL auto_increment,

  `cat_parent_id` int(11) NOT NULL default '1',

  `cat_name` varchar(50) NOT NULL default '',

  PRIMARY KEY  (`cat_id`),

  KEY `cat_parent_id` (`cat_parent_id`),

  KEY `cat_name` (`cat_name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

--

-- Dumping data for table `tbl_category`

--

 

INSERT INTO `categories` VALUES (10, 1, 'Sports & Outdoors');

INSERT INTO `categories` VALUES (11, 10, 'Apparel');

INSERT INTO `categories` VALUES (12, 10, 'Backpacks');

[boo_lolly]

that's not what i meant by show your table. i meant show the contents of the table, not how you created the table. and i still don't know which column is the parent column, and which one is the child column. i can help you do this, i just need to know i'm writing the right code.

[jcbarr]

Also, what are you getting as a result of the code?

[quasiman]

I'm not really sure what you're asking for...

cat_idcat_parent_idcat_name

101Sports & Outdoors

1110Apparel

1210Backpacks

131Tools

 

 

cat_id is the category's ID, then cat_parent_id is the ID of the category above it.  So for instance in this case, it would look like this:

 

• Sports & Outdoors

            o Apparel

            o Backpacks

• Tools

 

It does actually display the way it's supposed to now, but I want to give the top level a CSS class - as it is, it gives each <ul> the class.  Here's the revised code and then HTML source of what I have:

<?php

    mysql_connect("localhost", "login", "password");
    mysql_select_db("database");
displayChildren(1);
function displayChildren($id)
{
$sql = "SELECT * FROM categories WHERE cat_parent_id = $id";

$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result))
{
if($row['cat_parent_id'] < $id)
{
echo "<ul>\n<li>{$row['cat_name']}";
displayChildren($row['cat_id']);
echo "</li></ul>\n";
}else {
echo "<ul class=\"menu\">\n<li>{$row['cat_name']}";
displayChildren($row['cat_id']);
echo "</li></ul>\n";
}
}
}

?>

 

<ul class="menu">
<li>Sports & Outdoors<ul class="menu">
<li>Apparel</li></ul>
<ul class="menu">
<li>Backpacks</li></ul>
<ul class="menu">
<li>Camping<ul class="menu">
<li>Tents</li></ul>
</li></ul>
<ul class="menu">
<li>Consumables</li></ul>
<ul class="menu">
<li>Cool Stuff</li></ul>
<ul class="menu">
<li>Slingshots & Airguns</li></ul>
</li></ul>

[boo_lolly]

that's awesome that you got it working. were you asking for help otherwise?