This topic is locked
[SOLVED]

 Unlimited categories and subcategories in one table

1/11/2011 9:42:39 AM
PHPRunner General questions
K
komlos author

Hi,
Does PHPRunner support the management of unlimited category and subcategory if they come from one SQL table like this?
TABLE CATEGORY:

category_id

category_name

parentcategory_id
for example:
1, root, 0

2, cars, 1

3, red cars, 2

4, blue cars, 2

....
the tree structure:

  • root

    ------ cars

    --------- red cars

    --------- blue cars
    For example when adding and editing classified ads I would like to select the appropriate category from a dropdown select box, that lists all the categories like this:
    Cars > Red cars

    Cars > Blue cars

    Bikes > City bikes > Women

    Bikes > City bikes > Men

    Bikes > Trecking bikes
    Or using dependent dropdownboxes...
    box1: Bikes

    box2: City bikes

    box3: Women
    Category List page should look the same (all the categories and subcategories on one page, one category per row: Bikes > City bikes > Men), with links to edit, add, delete categories

    OR

    in a master/detail fashion where clicking on a category name will show a new page with its subcategories and so on.
    Classified ads table looks like this:

    (There should be only one category id)
    TABLE ADS:

    ad_id

    category_id (from table "Category")

    ad_description
    If PHPRunner doesn't support it, what is the workaround using events?
    Thanks

Admin 1/11/2011

It's not about what PHPRunner supports. You won't be able to handle unlimited recursive categories in a single SQL query.
If number of nested levels is known there are workarounds. Here is a really good article though this subject is quite complicated:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
The easiest approach is to limit number of subcategories to a certain number and add a database field for each level i.e. category, subcategory1, subcategory2, subcategory3 etc.

K
komlos author 1/11/2011

I know that article. You are right, but I could use SQL queries in nested while loops in PHP, and that can be unlimited. I've done that a couple of times. I was actually thinking about this solution somehow integrated in events. Though your simple solution looks quite promising, but please give me some clarification.
Using your approach do you mean creating all the "category, subcategory1, subcategory2, subcategory3" fields in "Category" table, not separate tables for each category level, right?

What about the "Ads" table? Do I need to create all those fields in here too? I suppose all the choosen levels will be saved here.

If yes, when listing ads how do you know which category the ad belongs to if there is 4 category_id associated with the ad?

When adding, editing a category for a classified ad, will I be able to use the dependent dropdown boxes?

Let's say there are four dropdown boxes as there are max. four levels. What happens when I select a leaf that is only three level deep? The fourth box work won't have a value. Will I be able to add the record?
Thanks

Admin 1/12/2011

I would suggest to add category, subcategory1, subcategory2, subcategory3 fields to Ads table. It's not that difficult to replace all of them with a single category field and restore the whole path running a few SQL queries but having a dedicated field for each level is really the easiest way.
In regards to category table. You can either store all levels in one table (a separate field per level) or store each level in separate table. You may want to check 'Creating multiple chained dropdown boxes' tutorial at http://xlinesoft.com/phprunner/php-database.htm to see how to setup chained dropdowns in PHPRunner.

K
komlos author 1/12/2011

I've tried your solution and it is quite simple to add dependent boxes if a subcategory belongs to only one parent category. Otherwise one have to follow your "BMW" workaround in your above tutorial.
but I run into some problem

  1. When I set the dropdown boxes as "Required", sometimes when there is a category-leaf that has less then 4 levels, one or two boxes remain empty and I can't submit the form. If the boxes are not required, a user could submit the form without selecting the appropriate categories. Is it possible to somehow hide the boxes if there is no additional level?
  2. As per your suggestion, the above 4 categories are saved by their names into the ADS table, and no category ID. If there are no category ID saved with an ad, how can I tell which category the ad is in, and how can I perform actions like browsing ads by categories, etc...

Admin 1/13/2011
  1. I guess you don't have to make all dropdown boxes required. You can hide them as well if you want, here is the code sample:

    http://xlinesoft.com/phprunner/docs/show_dropdown_list_of_us_states.htm
  2. You can save IDs while displaying names. When you setup dropdown box choose ID as 'Link field', choose Name as 'Display field'

K
komlos author 1/13/2011


  1. I guess you don't have to make all dropdown boxes required. You can hide them as well if you want, here is the code sample:

    http://xlinesoft.com/phprunner/docs/show_dropdown_list_of_us_states.htm


  1. Sounds good, but how can I tell Javascript side, only from the value of the dropdown box, that there is no more level, so the next child dropdown box should be hidden?
  2. Another thing: if I hide a box with Javascript that is required, can I submit form wihout error message that the field is required?

Admin 1/13/2011

Javascript API allows you to make field 'Required' or 'Not required' on the fly:

http://xlinesoft.com/phprunner/docs/ctrl_addvalidation.htm

http://xlinesoft.com/phprunner/docs/ctrl_removevalidation.htm
I guess you can make it work the way that dropdown box becomes 'Required' if it contains items.

K
komlos author 1/14/2011

Thanks I'll try that. How do I make this modification on the PHP side using events?

Admin 1/14/2011

To the best of my understanding - you need to handle all this on the client side using Javascript.
If you stuck at some point publish your project to demo account and open a ticket at http://support.xlinesoft.com sending your Demo Account URL. Probably we can provide better help once we see where you stuck.

K
komlos author 1/18/2011

I tried to use the "Path Enumeration Method" for storing 3 level of categories in one table like in the below example, but with this setup it is not possible to create the dependent dropdown boxes.

Path Enumeration Method
So I decided to store each category level in separate tables. Although it is not an elegant way, and you are limited to a fixed category level, but at least it is compatible with the dependent dropdown boxes, all in all, and in regards of managing categories this seem to me the best choice...
Regarding required fields, I haven't tried your solution yet...