SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 32 of 32
  1. #26
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that was quick - going to take a close look at it now and see how you came up with rework. Thx again Awulf

  2. #27
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no prob, hopefully your bad luck is running out
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  3. #28
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I may try and fiddle around with this for a day and try to extend it from a 2 level to a 3 level once I understand it all thoroughly
    This should get interesting, wish me luck

  4. #29
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sure thing.

    one thing to think about when you extend it. currently you have 1 table for each level of your categories. so everytime you add a category you have to add a table. instead, you could combine everything into just 1 table, and have a parentId column that references some other row in that same table instead of a different table.

    so your category table would be
    Code:
    CREATE TABLE library_categories (
      ID bigint(11) NOT NULL auto_increment default '1',
      parent bigint(11) NOT NULL default '0',
      name text NOT NULL,
      description varchar(255) NOT NULL default '',
      PRIMARY KEY  (ID)
    ) TYPE=MyISAM PACK_KEYS=0;
    if the parent is set to 0, then you know that it's a top level category, otherwise you know it's one level below the "parent" row.

    so assuming you have your current category id in $cat_id, then finding the parent category or children categories is a very simple SQL command, using only 1 table instead of 2 or 3...

    the upside of this new database is that you can have as many level's as you want, and you won't be 'wasting' tables. the downside is that your database table structure won't be as straightforward and it might be overkill if you only need a 2 or 3 levels. (obvious downside is you'd have to rewrite some code that already works.... )

    anyways, just something to think about
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  5. #30
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would this be like recursive ? I was reading about it and was wondering about having one table for the cats, subcats, subsubcats, blah, blah and blah

    I'm very interested in how this would work. If this will be a more conventional approach I'm begining to like it already.

    As for one table this sounds an excellent Idea - maybe the queries would become less hastle.

    If you would like to shed any more light on this matter, I't would be a great learning curve for me and also others that might want to join in on this topic.

    Hope to hear from you soon

    Regards
    Deano

  6. #31
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it is a more conventional design. you get to use less tables to store the same amount of information. usually, less tables means simpler SQL queries, which is good.

    the 1 table would essentially be a recursive datastructure. this means that every row in your table could do one of two things: 1) refer to "0" in it's parent id column, which means it has no parent category or 2) refer to someother category.

    you'll need to watch out when you modify a row that you never set it's "parent id" to point to a row that is currently it's child. you'll want to make sure that your table is set up like a tree, not a cyclical graph...

    for instance if you're table has the following data:
    Code:
    category_table
    id | parent | name
    1  | 0      | Animals
    2  | 1      | Reptiles
    3  | 1      | Amphibians
    4  | 2      | Lizard
    this will give you a tree that looks like
    Code:
            Animals
               |
         |-----+-----|
      Reptiles    Amphibians
          |
      Lizard
    one benefit is that your URL's can become alot simpler:
    to list all files and subcategories of a category, the url can be:
    show.php?cat_id=4

    knowing only a category id can let you find the parent category, find all child categories, and find all files in the categeory:
    Code:
    SQL for parent category
    SELECT parent_id FROM category_table WHERE id=$cat_id
    then, getting the parent_id into $parent_id
    SELECT * FROM category_table WHERE id=$parent_id
    
    SQL for child categories
    SELECT * FROM category_table WHERE parent_id=$cat_id
    
    SQL for files
    SELECT * FROM files_table WHERE cat_id=$cat_id
    with these simple query's, you can make a page with links to go up a category, down to the next category, or click on a file, much like a file/directory browser

    a problem to watch out for: let's say you accidentally UPDATE'd the wrong row, and the Animals row got set to be a child of row 2, so you're table looks like...

    Code:
    category_table
    id | parent | name
    1  | 2      | Animals
    2  | 1      | Reptiles
    3  | 1      | Amphibians
    4  | 2      | Lizard
    now you have a cycle in your graph, so if you try to print out all the parents of the Lizard, then it prints out:
    Code:
    Reptiles
    Animals
    Reptiles
    Animals
    etc.....
    to make sure this never happens, either enter and check the data manually in phpMyAdmin (which it sounds like you're doing now?) or on your edit category page, just check and make sure before you set it that the new parent id is not already a child or, similarly, that the new child is not already the parent.


    hope this helps describe it a bit clearer
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  7. #32
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhh, I think I'm understanding this system - like you said before you can have lets say:

    - maincats - subcats - subsubcats - and the level depth can be infinate.

    So regardless of the depth, the system should work effortlessly - hyperthetically, could I create a new row for something like a comments ?

    This question is a little worrying for me. If I want to allow users to add data, how would this work, would this create further problems for me in finding a simple solution for adding to the database table. Ive seen alot of poor methods which can result in the data being placed into the wrong fields in the database. Hmm I think I'll leave this question alone for a while and work with the great guide you've written above. I can always come back to this and find an ideal solution.

    Kind Regards
    Deano


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •