SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 40 of 40
  1. #26
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    NULL should be a true NULL value, not stored as a string with a null. A string with NULL in it is not actually NULL. That said all root level items should either use a true NULL or 0 value depending on if there is some type of unique key constraint. If parent_id is part of a unique key constraint than 0 would be more appropriate than null since null would result in the constraint not being satisfied by root level items.
    The only code I hate more than my own is everyone else's.

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    0 is not an appropriate value for the top level's parent

    why would there be a UNIQUE key on that column???

    use NULL, not 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    A common case is unique name per parent. In which case there would need to be a unique key on parent_id and name. Using a NULL parent_id for the root level than would result in those items not following the restraint.
    The only code I hate more than my own is everyone else's.

  4. #29
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    If you UNIQUE(parent_id,name), then using a Null parent_id for the root level would NOT result in those items not following the restraint, unless their names were also the same.


    You're making an assumption about the user's natural key that should not be made; the natural key might include template_id, for example.

  5. #30
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    If you UNIQUE(parent_id,name), then using a Null parent_id for the root level would NOT result in those items not following the restraint, unless their names were also the same.


    You're making an assumption about the user's natural key that should not be made; the natural key might include template_id, for example.
    There is a template_id, yes. As to the rest of what you guys have said, I have to admit to not exactly following what the problem/argument/different method is that's being discussed for using one method over another?

    @Oddz - In the final bit of code that I've now got, how could I specify different classes for each item: child, grand-child, great grand-child, etc, so I can apply different css classes to these, for use in an on-hover drop-down menu.

    Al

  6. #31
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Oddz' code already gives you that setup:
    '<li class="depth-%u">%s%s</li>'
    Which will give you the CSS classes depth-1, depth-2, etc etc...

    (Note that %u is being filled by $runner , and is not itself a meaningful 'variable')

  7. #32
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oddz View Post
    A common case is unique name per parent.
    that doesn't make sense to me

    maybe you could actually whip up an example of what you mean?

    the only result i can see of putting a unique constraint on parent_id is to ensure there is maximum one child per parent

    which is pretty unusual, wouldn't you say?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #33
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    Oddz' code already gives you that setup:
    '<li class="depth-%u">%s%s</li>'
    Which will give you the CSS classes depth-1, depth-2, etc etc...

    (Note that %u is being filled by $runner , and is not itself a meaningful 'variable')
    Aah, great - yeah, I saw that in the source, but was slightly unsure as to what &u was! Upon reading-up on sprintf, I see that this is populated by the runner - am I right?

    Are there any sitepoint books, do you think, that I could read in order to learn more about how to construct a script such as this on my own, in the future? Very keen to learn more!

    The problem I now have is that the root items (ultimate parents), of which there are six, have image-based rollovers. I suppose I somehow need to insert an if() statement into the '<li class="depth-%u" part that will look to include a reference to each parent page's name, if it exists in the database (i'd need to create a new column, which isn't a problem), and then add that somewhere to the class name, that I can then hard-code in the css.

    What do you think?

    Al

  9. #34
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    I think what oddz has given you is the basis for what you want to do. Note the recursiveness (function that calls itself) of the TheMenu function, and extrapolate his output into whatever you want it to do.

  10. #35
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    I think what oddz has given you is the basis for what you want to do. Note the recursiveness (function that calls itself) of the TheMenu function, and extrapolate his output into whatever you want it to do.
    So if I was to put, for instance:

    1) an extra call into the SELECT function, so it read like

    $sql = 'SELECT id, parent_id, link_title, link_class FROM pages';

    2) then change the $menu var to read:

    $menu = convertAdjacencyListToTree(null,$rows,'id','parent_id','links','class');

    3) Then change the TheMenu function to read:

    PHP Code:
    function themeMenu($menu,$runner) {
     
        
    $out '';
            
        if(empty(
    $menu)) {
            return 
    $out;
        }
            
        
    $out.='<ul>';
        foreach(
    $menu as $link) {
            
    $out.= sprintf(
                
    '<li class="depth-%u-$link['class']">%s%s</li>'
                
    ,$runner
                
    ,$link['link_title']
                ,
    themeMenu($link['links'],($runner+1))
            );
        }
        
        
    $out.='</ul>';
        return 
    $out;
        

    Would that work, or have I missed something here?

    AL

  11. #36
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    1.) step one is correct
    2.) step two is not needed
    3.) step three would be better suited like

    PHP Code:
    function themeMenu($menu,$runner) {
     
        
    $out '';
            
        if(empty(
    $menu)) {
            return 
    $out;
        }
            
        
    $out.='<ul>';
        foreach(
    $menu as $link) {
            
    $out.= sprintf(
                
    '<li class="depth-%u%s">%s%s</li>'
                
    ,$runner
                
    ,isset($link['link_class'])?$link['link_class']:''
                
    ,$link['link_title']
                ,
    themeMenu($link['links'],($runner+1))
            );
        }
        
        
    $out.='</ul>';
        return 
    $out;
        

    The only code I hate more than my own is everyone else's.

  12. #37
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by StarLion View Post
    If you UNIQUE(parent_id,name), then using a Null parent_id for the root level would NOT result in those items not following the restraint, unless their names were also the same.


    You're making an assumption about the user's natural key that should not be made; the natural key might include template_id, for example.
    Code SQL:
    CREATE TABLE test(
         id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
        ,parent_id SMALLINT UNSIGNED NULL
        ,item_name VARCHAR(128) NOT NULL
        ,PRIMARY KEY(id)
        ,UNIQUE KEY(parent_id,item_name)
    );

    Code SQL:
    INSERT INTO test (parent_id,item_name) VALUES (NULL,'test'),(NULL,'test'),(NULL,'test'),(NULL,'test');

    Notice: the insert query does not fail and you end up with 4 items named test with the same parent because of the NULL value.


    Code SQL:
    INSERT INTO test (parent_id,item_name) VALUES (0,'test'),(0,'test');

    Notice: That only inserts one, as the other is a duplicate.

    That is what I meant when I referred to having something being unique per parent. NULL does not obey the unique key. So anywhere a NULL column is part of a unique key means the unique key will not be obeyed for NULL.

    Which is actually pretty nifty for handling soft-deletes:

    Code SQL:
    CREATE TABLE blogs (
           id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
           ,title VARCHAR(128) NOT NULL
           ,deleted TINYINT NULL DEFAULT '0'
           ,PRIMARY KEY(id)
          ,UNIQUE KEY(title,deleted)
    );

    insert blog:

    Code SQL:
    INSERT INTO blogs (title) VALUES ('blog1');

    - fail - duplicate key
    Code SQL:
    INSERT INTO blogs (title) VALUES ('blog1');

    - soft delete
    Code SQL:
    UPDATE blogs SET deleted = NULL WHERE title = 'blog1';

    - success
    Code SQL:
    INSERT INTO blogs (title) VALUES ('blog1');
    The only code I hate more than my own is everyone else's.

  13. #38
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    1.) step one is correct
    2.) step two is not needed
    3.) step three would be better suited like

    PHP Code:
    function themeMenu($menu,$runner) {
     
        
    $out '';
            
        if(empty(
    $menu)) {
            return 
    $out;
        }
            
        
    $out.='<ul>';
        foreach(
    $menu as $link) {
            
    $out.= sprintf(
                
    '<li class="depth-%u%s">%s%s</li>'
                
    ,$runner
                
    ,isset($link['link_class'])?$link['link_class']:''
                
    ,$link['link_title']
                ,
    themeMenu($link['links'],($runner+1))
            );
        }
        
        
    $out.='</ul>';
        return 
    $out;
        

    Thanks for this oddz. I'm not quite sure where the %u and %s are defined in the first place? Do you know of any books or learning resources (preferably sitepoint!) that I can get, to read-up on all this a little more?

    One query with the above - what if I wanted to give each UL a class too?

    Cheers

    Al

  14. #39
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Code SQL:
    CREATE TABLE test(
         id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
        ,parent_id SMALLINT UNSIGNED NULL
        ,item_name VARCHAR(128) NOT NULL
        ,PRIMARY KEY(id)
        ,UNIQUE KEY(parent_id,item_name)
    );

    Code SQL:
    INSERT INTO test (parent_id,item_name) VALUES (NULL,'test'),(NULL,'test'),(NULL,'test'),(NULL,'test');

    Notice: the insert query does not fail and you end up with 4 items named test with the same parent because of the NULL value.
    Interesting. Completely illogical, but that's indeed what happens. Bad engine coding, imo.

    That is what I meant when I referred to having something being unique per parent. NULL does not obey the unique key. So anywhere a NULL column is part of a unique key means the unique key will not be obeyed for NULL.
    Still not relevant, though - again, you're making an assumption about the user's desires for the table.

    One query with the above - what if I wanted to give each UL a class too?
    PHP Code:
        $out.='<ul>'
    (not sure why that . is there, but anyway)

    PHP Code:
        $out ='<ul class="ul-'.$runner.'">'

  15. #40
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by StarLion
    Still not relevant, though - again, you're making an assumption about the user's desires for the table.
    No assumption was made, I was merely providing some information that seemed relevant to using 0 or NULL as a parent. I have no idea what the actual table looks like considering the user never posted the create table statement. For all I know every column could be part of a unique key. Also, the $runner is there to identify the depth, which in most cases is a useful "hook" for CSS to have.

    Quote Originally Posted by StarLion
    (not sure why that . is there, but anyway)
    By the way… thanks for pointing out that gigantic inefficiency issue – much appreciated.

    The reason it was there though is because I pulled that function from this class where it makes more sense.

    PHP Code:
    <?php
    namespace UI\Element\Common\Listing;

    class 
    Tree implements \UI\Element {
        
        public function 
    settings() {
            return array(
                
    'data'=>array(
                    
    'default'=>array()
                )
                ,
    'child_key'=>array(
                    
    'default'=>'children'
                
    )
                ,
    'value_key'=>array(
                    
    'default'=>'value'
                
    )
                ,
    'list_element'=>array(
                    
    'default'=>'ul'
                
    )
                ,
    'depth_class'=>array(
                    
    'default'=>true
                
    )
                ,
    'mutation'=>array(
                    
    'default'=>null
                
    )
                ,
    'form'=>array( // flag to wrap contents in form element
                    
    'default'=>false
                
    )
                ,
    'form_action'=>array( // form action, when form
                    
    'default'=>''
                
    )
                ,
    'form_name'=>array( // form name, when form
                    
    'default'=>''
                
    )
                ,
    'form_method'=>array( // form action, when form
                    
    'default'=>''
                
    )
                ,
    'form_legend'=>array( // form legend, when form
                    
    'default'=>''
                
    )
            );
        }
        
        public function 
    html($settings,\UI\Manager $ui) {
            return 
    $this->_html($settings);
        }
        
        private function 
    _html($settings,$runner=0) {
            
            
    extract($settings);
            
    $out '';
            
            if(
    $form === true) {
                
    $out.= sprintf(
                    
    '<form name="%s" action="%s" method="%s"><fieldset><legend>%s</legend>'
                    
    ,$form_name
                    
    ,$form_action
                    
    ,$form_method
                    
    ,$form_legend
                
    );
            }
            
            
    /*
            * Build out tree 
            */
            
    if(!empty($data)) {
                
    $out.="<$list_element>";
                foreach(
    $data as $index=>$item) {
                    
    $out.= sprintf(
                        
    '<li%s>%s%s</li>'
                        
    ,$depth_class === true?' class="depth-'.$runner.'"':''
                        
    ,$mutation !== null?call_user_func_array($mutation,array($item[$value_key],$item,$index)):$item[$value_key]
                        ,
    $this->_html(array(
                            
    'data'=>isset($item[$child_key]) && !empty($item[$child_key])?$item[$child_key]:array()
                            ,
    'child_key'=>$child_key
                            
    ,'value_key'=>$value_key
                            
    ,'list_element'=>$list_element
                            
    ,'depth_class'=>$depth_class
                            
    ,'mutation'=>$mutation
                        
    ),($runner+1))
                    );
                }
                
    $out.="</$list_element>";
            }
            
            if(
    $form === true) {
                
    $out.= '</fieldset></form>';
            }
            
            return 
    $out;
            
        }
        
    }
    ?>
    By which I use to build out trees.

    PHP Code:
    echo $this->ui('Common.Listing.Tree',array(
        
    'data'=>$terms
        
    ,'value_key'=>'human_name'
        
    ,'child_key'=>'terms'
        
    ,'list_element'=>'ul'
        
    ,'mutation'=>$mutation
        
    ,'form'=>true
        
    ,'form_legend'=>$vocabulary?$vocabulary['human_name']:''
        
    ,'form_action'=>$frm_action
        
    ,'form_method'=>$frm_method
        
    ,'form_name'=>$frm_name
    )); 
    The only code I hate more than my own is everyone else's.


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
  •