UL list with nested UL list (subcategories)

[soltek]

Hello there,

 

this is a really noob question, but I've no idea on how to make it work without making everything messy and crappy.

 

I have one SQL table called categories, and another called subcategories.

Inside the subcategories table there's a field named 'parent' that will link it to the correspondent category, so it should look something like this in html:

 

<ul>
<li>Category
<ul>
<li>Subcategory</li>
</ul>
</li>
</ul>

 

Would you please give this poor guy a hand? =)

[KevinM1]

You need to provide more information.  Namely, your db table structure.

[soltek]

Oh okay.

 

Category table:

- ID

- name

- image

 

Subcategory table:

-ID

-name

-image

-parent

[scootstah]

How deep are these subcategories allowed to be?

[soltek]

How deep are these subcategories allowed to be?

 

Only up to one level.

Each category may have more than one subcategory, but that's it.

[scootstah]

Okay, for one level deep the following should be what you want. First, restructure your database to be one table. Two tables isn't needed. I used the following:

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(100) NOT NULL,
  `image` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `parent_id`, `name`, `image`) VALUES
(1, 0, 'cars', ''),
(2, 1, 'ford', ''),
(3, 1, 'toyota', ''),
(4, 1, 'chevrolet', ''),
(5, 0, 'computers', ''),
(6, 5, 'dell', ''),
(7, 5, 'hp', ''),
(8, 5, 'sony', '');

 

You can import that directly into your database if you like.

 

Then, the following code to create the nested UL's:

<?php

$mysqli = new mysqli('localhost', 'root', 'root', 'test');

$query = 'SELECT id, parent_id, name, image FROM categories';

$result = $mysqli->query($query);

if ($result->num_rows > 0) {
$categories = array();

while($row = $result->fetch_assoc())
{
	if ($row['parent_id'] != 0) {
		$categories[$row['parent_id']]['sub'][] = $row;
	} else {
		$categories[$row['id']] = $row;
	}
}

echo '<ul>';

foreach($categories as $cat)
{
	echo '<li>';

	if ($cat['parent_id'] == 0) {
		echo $cat['name'];	
	}

	if (isset($cat['sub']) && is_array($cat['sub'])) {
		echo '<ul>';

		foreach($cat['sub'] as $sub)
		{
			echo '<li>' . $sub['name'] . '</li>';
		}

		echo '</ul>';
	}

	echo '</li>';
}

echo '</ul>';
} else {
echo 'There are no categories';
}

 

EDIT: Changed if (is_array($cat['sub'])) { to if (isset($cat['sub']) && is_array($cat['sub'])) {

[Mahngiel]

There is no issue with having multiple tables for this.

 

The first thing you'll need to do is assign each sub to the parent:

<?php
$query = select * from `parents`;
$parents = msyql_fetch_array($query);

// run loop for all parents
 foreach($parents as $parent){

// associate children with parents
$parent['children'] = select * from `children` WHERE `parent_id` = $parent['id'];

}
?>
// check for parents, if exists, start unordered list
<?php if($parents): ?>
<ul>
// loop through every parent, and associate a list item
<?php foreach($parents as $parent): ?>
<li><?php echo $parent['name']: ?>

	// check for children, and run a new UL foreach
	<?php if($parent['children']): ?>
	<ul>
		<?php foreach($parent['children'] as $child): ?>
		<li><?php echo $child['name']; ?></li>
		<?php endforeach; ?>
	</ul>
	<?php endif;?>
</li>
<?php endforeach; ?>
</ul>
<?php endif; ?>

[scootstah]

Except that yours has infinite queries, mine has one.

[soltek]

I don't get it 

 

Where does this:

 

$categories[$row['parent_id']]['sub'][] = $row;

 

comes from -the sub part?

[soltek]

Anyone?

 

I'm using this code

 

<?php

$mysqli = new mysqli('localhost', 'root', 'root', 'test');

$query = 'SELECT id, parent_id, name, image FROM categories';

$result = $mysqli->query($query);

if ($result->num_rows > 0) {
$categories = array();

while($row = $result->fetch_assoc())
{
	if ($row['parent_id'] != 0) {
		$categories[$row['parent_id']]['sub'][] = $row;
	} else {
		$categories[$row['id']] = $row;
	}
}

echo '<ul>';

foreach($categories as $cat)
{
	echo '<li>';

	if ($cat['parent_id'] == 0) {
		echo $cat['name'];	
	}

	if (isset($cat['sub']) && is_array($cat['sub'])) {
		echo '<ul>';

		foreach($cat['sub'] as $sub)
		{
			echo '<li>' . $sub['name'] . '</li>';
		}

		echo '</ul>';
	}

	echo '</li>';
}

echo '</ul>';
} else {
echo 'There are no categories';
}

 

It kinda works, but the output display no sub-categories, when in should.

I think the problem may be in this line:

	if (isset($cat['sub']) && is_array($cat['sub'])) {
[/doce]

[jcbones]

No, that shouldn't be a problem.

 

Right after your closing bracket on your while statement, add this code, then copy/paste the array here.  We should get it sorted quickly.

echo '<pre>' . print_r($categories,true) . '</pre>';

[soltek]

Here:

 

Array
(
    [23] => Array
        (
            [id] => 23
            [parent_id] => 0
            [name] => Bracelets
            [image] => 1333296301.jpg
        )

    [27] => Array
        (
            [id] => 27
            [parent_id] => 0
            [name] => Pendants
            [image] => 1333296468.jpg
        )
)

[soltek]

Hmmm this is weird.

I just created the categories table again, using the SQL code you guys shared with me earlier, and the subcategories thingy works properly.

I deleted all the rows from that table and added the new categories and subcategories guess what? There were no subcategories on the HTML output.

 

I created the table again using the SQL code, added the new categories and subcategories withougt deleting the categories added using the SQL code and... it works.

 

Any idea what's going on?

[soltek]

This is the SQL table:

 

id	parent_id	    name	image
1	0	cars	 
2	1	ford	 
16	13	Other Bracelets	 
9	0	Pendants	1333394949.jpg
10	9	Silver Pendants	1333394961.jpg
11	0	Blue Pendants	1333394975.jpg
12	9	Blue Pendants	1333395004.jpg
13	0	Bracelets	1333395054.jpg
14	13	Silver Bracelets	1333395068.jpg
15	13	Children Bracelets	1333395093.jpg

Some subcategories aren't visible.

Actually only:

Ford

Silver Bracelets

Children Bracelets

Silver Pendants

Blue Pendants

 

And this only happens after I delete the categories and subcategories from the SQL code you guys gave me.

I tried to delete all but one - cars - but that didn't work either.