#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0

    How should i solve this out (category, sub-category...)


    Im planning to make a category script for my articles. Im running over a few issues.
    - I dont want to be limited to one level of category or sub-category.
    • Development
      • PHP
        • Arrays

      • Java
        • OOP

      History
      • After year 1000
        After year 1700
        • WW1
          WW2

      News

    Some places the category level should be fine, other places i might need a few levels +/- bottom lime, i dont want to be limited.

    I have planned to store the categories in a mysql db. another few problems appears for me here.
    Should i put it all in one table or have one table for main categories and another for sub-categories and then do left-joins to check if sub-category table got any parent id?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,987
    Rep Power
    375
    it is simple:

    database:

    categories (id, name, parent_id). you then need a recursive function
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by paulh1983
    categories (id, name, parent_id).
    yes, this

    see Categories and Subcategories

    Comments on this post

    • Jacques1 disagrees : He specifically said he does *not* want to be limited to a fixed number of levels.
    • myusernamer agrees : thanks for link.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    no, it's not simple.

    To answer your question first: You should put all categories in one table. An extra table for the main categories has not benefit. To the contrary, it means repeating almost the same table definition and mainting two tables instead of one.

    For storing and retrieving the categories, there are basically three models:

    • Naive recursion: You store the parent ID for each category. Then you write a PHP function which retrieves the subcategories for a given category and recursively calls itself for each subcategory. This is simple but extremely inefficient, since you have to do a separate query for every single category (or at least for every level of the category treee).
    • A recursive query: You again store the parent ID for each category. But you retrieve the whole category tree in a single recursive query. This is very simple and efficient -- but MySQL doesn't have this feature.
    • The nested set model: See the link for an explanation. This allows retrieving all categories in a single query on every database system (even MySQL). It's efficient when retrieving categories but rather complicated. Adding or removing categories is both complicated and inefficient.


    The first solution is the one that most people come up first, but it's also the most "stupid" approach, since it involves a massive number of queries. If this is just a fun project without any requirements regarding efficiency, you may use this approach. Otherwise, it's probably not an option.

    A single recursive query is much more efficient and pretty much the perfect solution. However, MySQL is one of the very few database systems that doesn't have recursive queries. If you're bound to the MySQL crap due to some cheap web hoster, you'll have to consider the nested set model. It's pretty complicated, but it's the only way to store and retrieve an arbitrary number of category levels without bombarding the database with queries.

    Comments on this post

    • myusernamer agrees : Thank you very much for a deep and simple explanation. The alternative number 2, A recursive query, does sqlite support this ability? since i do i have access to that also.
    Last edited by Jacques1; April 28th, 2013 at 01:59 PM.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by Jacques1
    ... but it's also the most "stupid" approach, since it involves a massive number of queries.
    you couldn't be more wrong — or more obnoxious — if you tried extra hard

    Originally Posted by Jacques1
    A single recursive query is much more efficient
    total bullshít
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by r937
    total bullshít
    So you suggest making 100 queries for 100 categories?

    Go back to your MySQL forum. Seriously.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by Jacques1
    Go back to your MySQL forum. Seriously.
    stop being such a douche and open your mind

    pathetic elitist
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by myusernamer
    The alternative number 2, A recursive query,
    does sqlite support this ability? since i do i have access to that also
    SQLite doesn't have recursive queries either (it's a very lightweight system not meant for any advanced stuff).

    But all of the fully-featured systems like PostgreSQL, SQL Server, Oracle etc. have them (and many other useful features).

    Anyway, if you're fine with sacrificing efficiency or limiting yourself to a fixed number of levels, any of the above workarounds will do.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0
    @Jacques1 yes, this is just a project i am doing for myself, but i will have to use it sooner or later so yes i would want this as effective as possible. And i will need the ability for deleting/adding new categories.

    What exactly do you point at when you say that doing the same query 100x times is not effective? I want it to go "fast".

    @r937 and paulh1983
    sqllessons.com/categories.html
    is pretty much what i was thinking of to begin with. already made the id name parentid with innodb engin and parentid fk to id.

    i also see now that to be totaly limitless its "impossible" for me atm with a mysql db in a smooth way. so i guess i will end up with this alternative.

    Would it be a "problem" if i just for safety add like 5-5 tables of sub-levels. (wont probably use that many sub-levels, but just incase, so i dont have to add more later). Would it have an effect/big impact on the resources used?
  18. #10
  19. No Profile Picture
    I haz teh codez!
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2003
    Posts
    2,549
    Rep Power
    2337
    http://book.cakephp.org/2.0/en/core-libraries/behaviors/tree.html

    Comments on this post

    • myusernamer agrees : Thank you for the link.
    I ♥ ManiacDan & requinix

    This is a sig, and not necessarily a comment on the OP:
    Please don't be a help vampire!
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by myusernamer
    What exactly do you point at when you say that doing the same query 100x times is not effective? I want it to go "fast".
    Every query takes up resources and time (both in your application and on the database system), so you usually try to avoid flooding your database with unnecessary queries. Since the "naive recursion" requires a separate query for every single category, it's the least efficient approach in that regard.

    Separate queries also prevent optimization by the database system, which is yet another reason to avoid them.

    Using a library for the nested set model (as suggested by ptr2void) also looks like a promising approach. You'd get all the benefits of this model without having to struggle with its complexity (because the library takes care of that). Actually, I'd say this is the best workaround for systems without recursive queries.

    Comments on this post

    • myusernamer agrees : Thank you very much for spending your time helping me!
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0
    Cool, i have been into CodeIgnitre and Laravel3 and i liked it, but i wanted to do more of the raw-coding myself. CakePHP is another framework, which i never took time to try out (since most of the people on the LV forum came from Zend/PHP/CI, and had nothing nice to say about it! )

    Is it easy to use the Tree-ability from CakePHP without having too use CakePHP on my entire website?
  24. #13
  25. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    You don't necessarily have to use the CakePHP library. If you Google for "php nested set", you'll find quite a lot of standalone libraries like this one.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo