Dynamic creation of categories (MySQL/PHP)

[slain84]

Hi everyone,

I am going to program an ads page. Every ad belongs to a category. An example:

--> Cars --> Porsche --> Cayenne
--> DVDs --> Action-Movies --> >18 years --> Lethal Weapon 3

Thus, there might be several sub-categories. This is no problem at all, but these sub-categories should be created by the admin later dynamically. He should be able to click through the categories and add a sub-category to "Cayenne" for example.

I have now the problem that I do not really know how to set up the MySQL database and how a script should look like so that the added categories are displayed.

I was thinking about adding fields to the category table in the database. That means that cat01 stands for the first sub-category, cat02 for the second and so on. If the admin adds a new, a new cat03 must be created. I do not how to implement that in my PHP script though.

Can anyone help?

Thanks,

Dominik

[NancyJ]

What I would do is have a categories table that was something like this:

CategoryID
CategoryText
Parent

Then your data would look like

1 Cars 0
2 Porche 1
3 Cayenne 2
4 DVDs 0
5 Action Movies 4
6 18-years 5
7 Lethal Weapon 6

IMO that the simplest way of representing the data - with infinate levels of sub categories.

Then you might have a script that looks like


displayChildren(0);
function displayChildren($id)
{
$sql = "SELECT * FROM tblCategories WHERE Parent = $id";
echo "<ul>";
if($result = mysql_query($sql))
{
while($row = mysql_fetch_assoc($result))
{
echo "<li>{$row['Category']}";
displayChildren($row['CategoryID']);
echo "</li>";
}
}
echo "</ul>";
}


This basically prints out the categories and sub categories in a neat list but you can modify it to suit your needs easily enough

[slain84]

Yes, that is a good idea. I have researched this topic in the last hours and everybody hints to that table structure.

Now I have to get the script running... I will post it here, if I succeeded! At the moment, it gives me an HTTP Error...

[slain84]

Sorry, my fault... Works just fine and there are several options to edit!

Thank you very much!

One addition: If you want to display the parent categories in kind of a "bar", use this script:


function displayParent($id, $session_id) {
$cat = array();
$i = 0;
$sql = "SELECT * FROM ads_01_cat WHERE id = '$id'";
if($result = mysql_query($sql))
{
while($row = mysql_fetch_assoc($result))
{
$i++;
$content = '--> <a href="cat_list.php?session_id='.$session_id.'&id='.$row['id'].'">'.$row['name'].'</a>';
$cat = array_fill($i, 1, $content);
displayParent($row['parent_id'], $session_id);
}
}
$cat = array_reverse($cat);
$count_cat = count($cat);

for($i=0; $i<=$count; $i++) {
echo $cat[$i];
}
}


If you want to display the proper sub-categories of the selected parent category, use this one:


function displayChildren($id, $session_id) {
$sql = "SELECT * FROM ads_01_cat WHERE parent_id = '$id'";
echo "<ul>";
if($result = mysql_query($sql))
{
while($row = mysql_fetch_assoc($result))
{
echo '<li><a href="cat_list.php?session_id='.$session_id.'&id='.$row['id'].'">'.$row['name'].'</a>';
// displayChildren($row['id']);
echo "</li>";
}
}
echo "</ul>";
}


These scripts assume, that you transfer a Session-ID. If you do not want to use it, just remove it in the function line!

Hope these short scripts help anyone who wants to do something similar!

See you,

Dominik

[marek_mar]

I would recommend using MPTT (http://www.codingforums.com/showthread.php?t=79773) as it would allow you to get the whole category tree or just a branch in one query.
Now I don't know any good explanation of what MPTT exactly is and how it works so you'll have to find it.

[slain84]

The solution just posted works fine for me and I guess also for anyone who wants to do something similar...

Thanks for your answer though!