Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post

    Formatting mysql information by values

    Okay, here is something slightly complicated and I am trying to figure out the fastest and smoothest way to do the following.

    I couldnt post my mysql datat in a visible way on this forum, so im linking to a text file, the mysql structure and test data is at the top:

    http://eli-white.com/files/table%20information.txt

    The way I want to get things to display is:

    Songs
    ->Genre
    ->Albums
    ->Artists
    -->Green Day
    -->Flyleaf
    --->Singer
    -->Atreyu
    ->Title
    Movies
    ->Genre
    ->Actors
    But for the sake of you understanding how things would get organized that way I added the rest of the corresponding row information after the formatting:

    Code:
    Name – ID – SORT ORDER – (Parent Ids) – (Child Ids)
    Songs – 1 – 1 –( )- (2,3,5,9)
    ->Genre – 2 – 1 – (1) – ()
    ->Albums – 3 – 2 – (1) – ()
    ->Artists – 5 – 3 – (1) – (4,6)
    -->Green Day – 6 – 1 – (5,1) – ()
    -->Flyleaf – 4 – 2 – (5,1) – (8)
    --->Singer – 8 – 1 – (4,5,1) – ()
    -->Atreyu– 7 – 3 – (5,1) – ()
    ->Title – 9 – 4 – (1) – ()
    Movies– 10 – 2 – () – (11,12)
    ->Genre – 11 – 1 – (10) – ()
    ->Actors– 12 – 2 – (10) – ()
    So for example, it would find all the rows where is_cat is 1, then sort those by the sort order. Then, it would go to the first category it just found and find all the forums right under that (forums where id is in the category's child ids). Then after that it would continue to cascade through each forum finding all the forums where the id is in the parent forum's child ids.

    If that made any sense then thats great, otherwise please ask for me to re-explain something.

  • #2
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    *bump*

    any thoughts?

  • #3
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    Okay, I have come up with this class that does pretty much what I want, except for one issue.

    PHP Code:
    class forum
    {
        public 
    $forum_list=array();
        function 
    __construct()
        {
        }
        function 
    getcats()
        {
            global 
    $super;
            
    $cat_sql "SELECT * FROM ".TBL_PREFIX."forums WHERE `is_cat`='1' ORDER BY sort_order ASC";
            
    $cat_query $super->db->query($cat_sql);
            while (
    $cat $super->db->fetch_assoc($cat_query))
            {
                
    $this->forum_list[] = $cat['name'];
                
    $this->get_forums($cat['child_ids']);
            }
        }
        function 
    get_forums($ids)
        {
            global 
    $super;
            
    $array explode(","$ids);
            
            foreach (
    $array as $row)
            {
                
    $forum_sql "SELECT * FROM ".TBL_PREFIX."forums WHERE `id`='".$row."'";
                
    $forum_query $super->db->query($forum_sql);
                while(
    $forum $super->db->fetch_assoc($forum_query))
                {    
                    
    $parent_ids explode(","$forum['parent_ids']);
                    
                    
    $deep count($parent_ids);
                    
    //echo "Number Dashes: ".$deep;
                    
    $dashes str_repeat("--",$deep);
                    
    $dashes $dashes;
                    
    $this->forum_list[] = $dashes.$forum['name']."<br />";
                    
    $this->get_forums($forum['child_ids']);
                }
            }
        }
        

    This code does not order the children by the sort order. The children's order is at the mercy of whatever order the children id's are in the parent forums.

    Eg:
    Songs has the children id's "2,3,5,9"

    The forums 2,3,5,9 have the field sort_order set so that happens to be the way they show up.

    However, if Songs had children id's: "5,2,9,3"

    I would still want them to show up in the same order because of the sort order they have.

    What do I need to change in order to do this, or even what would be a better way to go about this?

  • #4
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    *bump*

  • #5
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,871
    Thanks
    2
    Thanked 164 Times in 159 Posts
    This is not a direct answer to you question, but it will help lead you to the answer. Your database is not designed very well and should be reworked (normalized). In particular, I'm referring to the the parent_ids and child_ids fields. It's never good practice to have an individual field that is made up of comma separated values.

    http://dev.mysql.com/tech-resources/...alization.html

  • #6
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    So you think it would be easier to just have a single parent id for the parent directly above it?


  •  

    Posting Permissions

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