SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    First step for a menu of subcategories and sub-subcategories - the query

    Hello all, this is a middle question, could be made on a php part, or on the mysql part... I don't know... still I presume it fits better here:

    I'm having the mysterious query:

    Code:
    SELECT DISTINCT t2.nome_cat AS subcategoria, t3.nome_cat AS subsubcategoria
    FROM categoria AS t1
    LEFT JOIN categoria AS t2 ON t2.parent_id_cat = t1.id_cat
    LEFT JOIN categoria AS t3 ON t3.parent_id_cat = t2.id_cat
    WHERE t1.id_cat = 1
    I'm getting this as return:
    subcategoria subsubcategoria
    Fitofármacos Herbicidas
    Fitofármacos Insecticidas
    Fitofármacos Fungicidas
    Adubos NULL
    Sementes NULL

    On the menu, I want that the first level be with subcategories:
    like

    Fitofármacos
    Adubos
    Sementes


    But as you can see, I'm having 3 times Fitofarmacos from the database
    Instead of displaying 3 times, I must displayed only one time.

    1)
    Should this be handled on the php side of things, or on the query?
    As you may notice I've added a distinct clause hoping to solve this. No success.



    Thanks in advance,
    Newbie,
    Márcio

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT t2.nome_cat AS subcategoria
      FROM categoria AS t1
    LEFT OUTER
      JOIN categoria AS t2 
        ON t2.parent_id_cat = t1.id_cat
     WHERE t1.id_cat = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,



    I have done as mentioned:

    Code:
    SELECT t2.nome_cat AS subcategoria
    FROM categoria AS t1
    LEFT OUTER
    JOIN categoria AS t2 ON t2.parent_id_cat = t1.id_cat
    WHERE t1.id_cat =1
    But with this, I only get subcategories. But not the sub-subcategories.

    The intended result shoud be, instead of:

    subcategories - sub-subcategories
    subcategory1 - sub-subcategory1
    subcategory1 - sub-subcategory2
    subcategory1 - sub-subcategory3
    subcategory2
    subcategory3


    Have:

    subcategories - sub-subcategories
    subcategory1 - sub-subcategory1
    subcategory2 - sub-subcategory2
    subcategory3 - sub-subcategory3

    The intended result was to have no repeating subcategories or sub-subcategories returned.

    If this is possible?


    Regards,
    Márcio

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in your first post, it certainly looked like all you wanted was the subcategories

    now, it seems that you want the subsubcatories as well, bu only one of them

    my advice is that if you show only one of them, it is misleading to the user
    Code:
    SELECT t2.nome_cat AS subcategoria
         , MIN(t3.nome_cat) AS subsubcategoria
      FROM categoria AS t1
    LEFT OUTER
      JOIN categoria AS t2 
        ON t2.parent_id_cat = t1.id_cat
    LEFT OUTER
      JOIN categoria AS t3 
        ON t3.parent_id_cat = t2.id_cat
     WHERE t1.id_cat = 1
    GROUP
        BY t2.nome_cat
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Select all rows then pass the result as $arrRows to this function and tell me how it works out. You should end up with a structure of nested arrays that replicates the tree.

    PHP Code:
    $arrTree parse_into_tree(null,$arrRows,'id_cat','parent_id_cat','children');
    echo 
    '<pre>',print_r($arrTree),'</pre>';

    function 
    parse_into_tree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
        
        
    $arrChildren = array();

        for(
    $i=0;$i<count($arrRows);$i++) {
            if(
    $intParentId === $arrRows[$i][$strParentsIdField]) {
                
    $arrChildren array_merge($arrChildren,array_splice($arrRows,$i--,1));
            }
        }
        
        
    $intChildren count($arrChildren);
        if(
    $intChildren != 0) {
            for(
    $i=0;$i<$intChildren;$i++) {
                
    $arrChildren[$i][$strNameResolution] = parse_into_tree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
            }        
        }
        
        return 
    $arrChildren;


    Ex.
    HTML Code:
    Array
    (
        [0] => Array
            (
                [cats_id] => 1
                [parents_id] => 
                [name] => Tv & Video
                [sort_order] => 1
                [cats] => Array
                    (
                        [0] => Array
                            (
                                [cats_id] => 6
                                [parents_id] => 1
                                [name] => Televisions
                                [sort_order] => 0
                                [cats] => Array
                                    (
                                    )
    
                            )
    
                        [1] => Array
                            (
                                [cats_id] => 7
                                [parents_id] => 1
                                [name] => DVD Players
                                [sort_order] => 1
                                [cats] => Array
                                    (
                                    )
    
                            )
    
                    )
    
            )
    
        [1] => Array
            (
                [cats_id] => 2
                [parents_id] => 
                [name] => Audio
                [sort_order] => 2
                [cats] => Array
                    (
                        [0] => Array
                            (
                                [cats_id] => 8
                                [parents_id] => 2
                                [name] => Car Audio
                                [sort_order] => 0
                                [cats] => Array
                                    (
                                    )
    
                            )
    
                        [1] => Array
                            (
                                [cats_id] => 9
                                [parents_id] => 2
                                [name] => Music
                                [sort_order] => 0
                                [cats] => Array
                                    (
                                        [0] => Array
                                            (
                                                [cats_id] => 10
                                                [parents_id] => 9
                                                [name] => Compact Discs
                                                [sort_order] => 0
                                                [cats] => Array
                                                    (
                                                    )
    
                                            )
    
                                    )
    
                            )
    
                    )
    
            )
    
        [2] => Array
            (
                [cats_id] => 3
                [parents_id] => 
                [name] => Toys & Games
                [sort_order] => 3
                [cats] => Array
                    (
                    )
    
            )
    
        [3] => Array
            (
                [cats_id] => 4
                [parents_id] => 
                [name] => Cameras & Camcorders
                [sort_order] => 4
                [cats] => Array
                    (
                    )
    
            )
    
        [4] => Array
            (
                [cats_id] => 5
                [parents_id] => 
                [name] => Cell Phone & Office
                [sort_order] => 5
                [cats] => Array
                    (
                    )
    
            )
    
    )
    That is the first step. Once the tree is built its just a matter of recursively parsing it into a menu.

    ** You will also need to replace null with 0 if a parent of 0 represents the root level.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Here is an example of my test code prior to the previous posting. Ignore ActiveRecord:: getConnection() as it just returns the database connection object. In this case its PDO but it could be anything.

    PHP Code:
    $arrRows = array();
    $strSQL 'SELECT c.cats_id,c.parents_id,c.name,c.sort_order FROM CATS c';
    $objPDO ActiveRecord::getConnection();

    if(
    $objPDOSTMT $objPDO->prepare($strSQL)) {
        if(
    $objPDOSTMT->execute()) {
            while(
    $arrRow $objPDOSTMT->fetch(PDO::FETCH_ASSOC)) {
                
    $arrRows[] = $arrRow;
            }    
        }


  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Here is the full thing:

    PHP Code:
    $arrRows = array();
    $strSQL 'SELECT c.cats_id,c.parents_id,c.name,c.sort_order FROM CATS c';
    $objPDO ActiveRecord::getConnection();

    if(
    $objPDOSTMT $objPDO->prepare($strSQL)) {
        if(
    $objPDOSTMT->execute()) {
            while(
    $arrRow $objPDOSTMT->fetch(PDO::FETCH_ASSOC)) {
                
    $arrRows[] = $arrRow;
            }    
        }
    }

    parse_into_menu(parse_into_tree(null,$arrRows,'cats_id','parents_id','cats'),'cats','name');

    function 
    parse_into_tree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
        
        
    $arrChildren = array();

        for(
    $i=0;$i<count($arrRows);$i++) {
            if(
    $intParentId === $arrRows[$i][$strParentsIdField]) {
                
    $arrChildren array_merge($arrChildren,array_splice($arrRows,$i--,1));
            }
        }
        
        
    $intChildren count($arrChildren);
        if(
    $intChildren != 0) {
            for(
    $i=0;$i<$intChildren;$i++) {
                
    $arrChildren[$i][$strNameResolution] = parse_into_tree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
            }        
        }
        
        return 
    $arrChildren;

    }

    function 
    parse_into_menu($arrMenu,$strChildKey,$strNameKey,$intRunner=0) {
        
        
    $strNL "\n";
        
    $intMenu count($arrMenu);
        
        for(
    $i=0;$i<$intMenu;$i++) {
            if(
    $i==0) { 
                echo 
    str_repeat("\t",$intRunner),'<ul>',$strNL
            }
            
            if(!empty(
    $arrMenu[$i][$strChildKey])) {
                echo 
    str_repeat("\t",$intRunner+1),'<li>',$arrMenu[$i][$strNameKey],$strNL;
                
    parse_into_menu($arrMenu[$i][$strChildKey],$strChildKey,$strNameKey,$intRunner+2);
                echo 
    str_repeat("\t",$intRunner+1),'</li>',$strNL;
            } else {
                echo 
    str_repeat("\t",$intRunner+1),'<li>',$arrMenu[$i][$strNameKey],'</li>',$strNL;
            }
            
            if(
    $i==($intMenu-1)) { 
                echo 
    str_repeat("\t",$intRunner),'</ul>',$strNL
            }
        }
        

    Output:
    HTML Code:
    <ul>
    	<li>Tv & Video
    		<ul>
    			<li>Televisions</li>
    			<li>DVD Players</li>
    		</ul>
    	</li>
    	<li>Audio
    		<ul>
    
    			<li>Car Audio</li>
    			<li>Music
    				<ul>
    					<li>Compact Discs
    						<ul>
    							<li>Alternative</li>
    							<li>Rock</li>
    							<li>Rap</li>
    
    						</ul>
    					</li>
    				</ul>
    			</li>
    		</ul>
    	</li>
    	<li>Toys & Games</li>
    	<li>Cameras & Camcorders</li>
    
    	<li>Cell Phone & Office</li>
    </ul>

  8. #8
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    in your first post, it certainly looked like all you wanted was the subcategories
    I'm sorry. You are absolutely right. I forgot something on my previous post. :s

    Quote Originally Posted by r937 View Post
    now, it seems that you want the subsubcatories as well, bu only one of them
    Sorry. I wanted the subsubcategories as well, but only one of each kind. I want to avoid repetition on subsubcategories as well as in subcategories.


    Quote Originally Posted by r937 View Post
    my advice is that if you show only one of them, it is misleading to the user
    To oddz consideration as well:
    My goal is to have an array of objects with subcategories and sub-subcategories inside. Once I have then, I intend to loop trought the results of this array showing on one page: a list of subcategory links only.
    Once the user clicks one of those subcategorie links, on another page, a URI segment will change, and I will get the list of subcategories as well as the list of the correspondent sub-subcategories.

    According to my hardly explained intends , shouldn't this be a nice query solution?


    thanks a lot,
    Márcio






    ps - A little more about the intended result:
    When the user clicks over one of the subcategories or sub-subcategories, the third segment of the URI will change to a value equal to the id_cat value stored on the database.
    Each time the user clicks on this list of links on the left side of the page: I will list (on the right side) all the products where the product cat_id will be = to the URI value on the third segment. (more then a menu, I'm talking about some sort of filtering mechanism).

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    My goal is to have an array of objects with subcategories and sub-subcategories inside. Once I have then, I intend to loop trought the results of this array showing on one page: a list of subcategory links only.
    Once the user clicks one of those subcategorie links, on another page, a URI segment will change, and I will get the list of subcategories as well as the list of the correspondent sub-subcategories.

    According to my hardly explained intends , shouldn't this be a nice query solution?
    Same concept as posted above. The only difference is that the call to parse_into_tree() first argument will be the primary key of the current item. So if someone is on the electronics page and electronics has a primary key of 309 then you send in 309 as the parent id. You still need to select every row though because with an infinite depth menu there is no way use a where clause. Instead you need to bring in everything and parse it on the application side. make sense?

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    So to get all the menu for items with a parents_id of 10 the below would work. I had to change the comparison in parse_into_tree() from identity to equality as well.

    PHP Code:
    ...

    parse_into_menu(parse_into_tree(10,$arrRows,'cats_id','parents_id','cats'),'cats','name');

    function 
    parse_into_tree($intParentId,&$arrRows,$strIdField,$strParentsIdField,$strNameResolution) {
        
        
    $arrChildren = array();

        for(
    $i=0;$i<count($arrRows);$i++) {
            if(
    $intParentId == $arrRows[$i][$strParentsIdField]) {
                
    $arrChildren array_merge($arrChildren,array_splice($arrRows,$i--,1));
            }
        }
        
        
    $intChildren count($arrChildren);
        if(
    $intChildren != 0) {
            for(
    $i=0;$i<$intChildren;$i++) {
                
    $arrChildren[$i][$strNameResolution] = parse_into_tree($arrChildren[$i][$strIdField],$arrRows,$strIdField,$strParentsIdField,$strNameResolution);
            }        
        }
        
        return 
    $arrChildren;

    }
    ... 
    Output:
    HTML Code:
    <ul>
    	<li>Alternative</li>
    	<li>Rock</li>
    	<li>Rap</li>
    </ul>

  11. #11
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm really trying to keep this simple:

    Let's say that, for this project, I don't want unlimited categories. I want no more then a 2 level array to be returned actually.

    If this is the case, then a nice builded query will sufice no?


    Ahhh!!! :P
    (I have a voice on the back of my head telling me
    However, we all know the client, they say: "Nooo.... I will never have more then 2 subcategories" and, one year later or even less: "Hi, I cannot add another sub sub sub sub category! What's going on?!!!".
    So YES, I should follow oddz solution.


    Let me be pertinent, I'm really sorry for this, , If I create another thread, can I have your help on building a multi-level category filter system based on URI segment changes and subcategories id, on a MVC structure, that, by the way, is supported by a framework called CodeIgniter that I have followed like a looser because, with my "newbility",I was unable to build my own framework, but hey I get myself a MVC structure (breeeaad)?

    If not, and I perfectly understand it, I will stay with a fixed subcategory solution, if yes. Let's gooo!!!!!



    Regards,
    Márcio

  12. #12
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Same concept as posted above. The only difference is that the call to parse_into_tree() first argument will be the primary key of the current item. So if someone is on the electronics page and electronics has a primary key of 309 then you send in 309 as the parent id. You still need to select every row though because with an infinite depth menu there is no way use a where clause. Instead you need to bring in everything and parse it on the application side. make sense?
    I see... so untill today, I was always trying to actually build different arrays, based on the URI, and I was getting different arrays, but the parents never showed up. But, what you are saying is that I should retriev, on every page, of my navigation, always an array containing all the three, then, based on the value of the URI I can then remove the parts that are not needed to show the menu?

    What I need to find then, must be the logic behind the removing keys and values of an array, based on the id passed on a specific segment of an uri.

    The tree will be on my model, and the "tree cuts" must be a task for the controller.


    Was this what you have suggested on the above quoted comment?


    Regards,
    Márcio


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
  •