SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    May 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dynamic query - let me explain

    Hi everyone,

    i'm using a MVC style system for my code and i need some help.

    Now what i need to do is display a table with parent and child category but the child categories need to be indented.
    The problem i am having is creating the dynamic array for the child categories part.

    The function i have takes two parameters a ID and a ParentID
    PHP Code:
    $link dbconnect();
        
    $fullArry = array();

        
    $query mysqli_query($link"Query String");
        
    $queryNumRows mysqli_num_rows($query);

        if(
    $queryNumRows 0){
            while(
    $queryResults mysqli_fetch_assoc($query)){
                
    //Build array
            
    }
        }

        return 
    $fullArry
    The above code will get the parent categories - so my idea is to call the function again in the same function and carry the parents id into the function
    PHP Code:
        $link dbconnect();
        
    $fullArry = array();

        
    $query mysqli_query($link"Query String");
        
    $queryNumRows mysqli_num_rows($query);

        if(
    $queryNumRows 0){
            while(
    $queryResults mysqli_fetch_assoc($query)){
                
    //Build array
                //Call the function
                
    $childArry categories(/*include the in and parent id here*/);
            }
        }

        return 
    $fullArry
    And append the $childArry to the existing one.

    is there a better way of doing this or am i on the right track?
    Thanks for reading any replays will be great.

  2. #2
    SitePoint Zealot bronze trophy xMog's Avatar
    Join Date
    Mar 2011
    Posts
    145
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)
    I'm not a 100% sure I understand your question but you want to display something like this?

    Code:
    Parent Category #1
      Child  #1.1
      Child #1.2
    Parent Category #2
      Child #2.1
    Parent Category #3
      Child #3.1
      Child #3.2
      Child #3.3
    etc...
    It's hard to tell because we don't see your table structures, but normally you would get all the data with one SELECT JOIN and just loop through it.

    You would end up with a result set like this :

    Headers would be something like this:
    ParentID ChildID ParentName ChildName

    And the data :

    1, 1, Parent #1, Child #1.1
    1, 2, Parent #1, Child #1.2
    2, 3, Parent #2, Child #2.1

    Then, when you loop, you check if the "current parent" was already displayed. If not, it's a new parent so you need to display the "parent", then all the children until a new parent shows up.

  3. #3
    SitePoint Member
    Join Date
    May 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi xMog,

    When i posted i was in a state of confusion and anger so sorry if the question was not clear

    Let me explain the structure of the table and way i'm having trouble with this.

    Let say i have a category and a child category inside it:

    Code:
    parent.category#1
        child.category#1.1
        child.category#1.2
    parent.category#2
        child.category#2.1
        child.category#2.2
    ...etc
    I have no problem showing the above but where i am having trouble with is when someone adds a "child.category" inside another "chil.category"

    Code:
    parent.category#1
        child.category#1.1
            sub.child.category#1.1.1
            sub.child.category#1.1.2
            sub.child.category#1.1.3
        child.category#1.2
    parent.category#2
        child.category#2.1
        child.category#2.2
            sub.child.category#2.2.1
    ...etc
    The table structure has to grow according to the array structure, the above is for two levels deep but i need the table to grow as far down as they want for example if i wanted to have child categories 5 deep then the table has to grow to that size without having to change any code.

    But i think i have a plan on how to do what i want.
    i'm thinking of making my array structure as follows:

    Code:
    parent.id#1 =>
    	name => parent.name
    	order => parent.order
    	children =>
    		child.id =>
    			name => child.name
    			order => child.order
    			level => child.level
    		child.id =>
    			name => child.name
    			order => child.order
    			level => child.level
    parent.id#2 =>
    	name => parent.name
    	order => parent.order
    	children =>
    		child.id =>
    			name => child.name
    			order => child.order
    			level => child.level
    		child.id =>
    			name => child.name
    			order => child.order
    			level => child.level
    ...etc
    so all the child categories are now in a flat array structure and i use the "level" will tell me how meany indentations i have to apply to that child category in the table.
    Let me know what you think and if it's a good idea.
    Thanks for the replay have a great day.

  4. #4
    SitePoint Zealot bronze trophy xMog's Avatar
    Join Date
    Mar 2011
    Posts
    145
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)
    Well, that's a case where OOP would be easier to work with IMO. You could have an object like this (it's pseudo code) :

    Code:
    class Category {
    
       $children (list of "Category")
       $title
    
       Category($title, $level, $id);
    
       getChildren();
       addChild();
    }
    Also, getting a recursive structure like this is a little bit tricky with PHP & mySQL, but I think that you should be able to do it with one SQL query if you use a stored procedure.
    Do a search on Google for "mysql tree structure". I found this article which seems nice: http://mikehillyer.com/articles/mana...data-in-mysql/

    Also, doing a search on PHP & mysql tree structure should give you some ideas, like :
    http://stackoverflow.com/questions/5...tree-structure
    http://leon.vankammen.eu/tech/storin...and-mysql.html


    Good luck!

  5. #5
    SitePoint Member
    Join Date
    May 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you xMog i will have a look at the links you gave and let you know what the results are.

  6. #6
    SitePoint Member
    Join Date
    May 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you xMog.

    The links you gave me are amazing, it is things like this that make me love coding - when you have no idea of what to do and then someone has the answer to your questions and it's so simple, i think the links you gave will work i'm just looking up a couple of things so that i understand it currently.

  7. #7
    SitePoint Zealot bronze trophy xMog's Avatar
    Join Date
    Mar 2011
    Posts
    145
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)
    I'm glad I could help! It's actually a "not so easy problem", but at least is has a name and somebody already spent time on it! Good luck!


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
  •