{ Snipperize }

Handling Recursive Categories Hierarchical Data Add to Favorite

<?php 
class Category {
    function getParent($id) {
        return mysql_safe_query('SELECT parent.* FROM categories AS child, categories AS parent WHERE parent.id = child.parent_id AND child.id = %s LIMIT 1', $id);
    }

    function getChildren($id, $direct = false) {
        if($direct) {
            return mysql_safe_query('SELECT * FROM categories WHERE parent_id = %s ORDER BY left_val ASC', $id);
        } else {
            return mysql_safe_query('SELECT child.* FROM categories AS child, categories AS parent WHERE child.left_val >= parent.left_val AND child.right_val <= parent.right_val AND parent.id = %s ORDER BY child.left_val ASC', $id);
        }
    }

    function insert($name, $parent_id) {
        $result = mysql_safe_query('SELECT * FROM categories WHERE parent_id <=> %s AND name < %s ORDER BY name DESC LIMIT 1', $parent_id, $name);
        if(mysql_num_rows($result) > 0) {
            // insert between children
            $row = mysql_fetch_assoc($result);
            mysql_safe_query('UPDATE categories SET left_val = left_val + 2 WHERE left_val > %s', $row['right_val']);
            mysql_safe_query('UPDATE categories SET right_val = right_val + 2 WHERE right_val > %s', $row['right_val']);
            mysql_safe_query('INSERT INTO categories (parent_id, left_val, right_val, name) VALUES (%s, %s, %s, %s)', 
                $parent_id, $row['right_val'] + 1, $row['right_val'] + 2, $name);
        } else {
            $result = mysql_safe_query('SELECT * FROM categories WHERE id <=> %s LIMIT 1', $parent_id);
            if(mysql_num_rows($result) > 0) {
                // insert first child
                $row = mysql_fetch_assoc($result);
                mysql_safe_query('UPDATE categories SET left_val = left_val + 2 WHERE left_val > %s', $row['left_val']);
                mysql_safe_query('UPDATE categories SET right_val = right_val + 2 WHERE right_val > %s', $row['left_val']);
                mysql_safe_query('INSERT INTO categories (parent_id, left_val, right_val, name) VALUES (%s, %s, %s, %s)',
                    $parent_id, $row['left_val'] + 1, $row['left_val'] + 2, $name);
            } else {
                // insert at beginning of tree
                mysql_safe_query('UPDATE categories SET left_val = left_val + 2, right_val = right_val + 2');
                mysql_safe_query('INSERT INTO categories (parent_id, left_val, right_val, name) VALUES (%s, %s, %s, %s)',
                    null, 1, 2, $name);
            }
        }
    }

    function delete($id) {
        $result = mysql_safe_query('SELECT * FROM categories WHERE id = %s LIMIT 1', $id);
        $row = mysql_fetch_assoc($result);
        mysql_safe_query('DELETE FROM categories WHERE id = %s LIMIT 1', $id);
        mysql_safe_query('UPDATE categories SET parent_id = %s WHERE categories.parent_id = %s', $row['parent_id'], $id); // relink parent
        mysql_safe_query('UPDATE categories SET left_val = left_val - 1, right_val = right_val - 1 WHERE left_val > %s AND right_val < %s', $row['left_val'], $row['right_val']); // update children
        mysql_safe_query('UPDATE categories SET left_val = left_val - 2 WHERE left_val > %s', $row['right_val']); // update left values of nodes to right
        mysql_safe_query('UPDATE categories SET right_val = right_val - 2 WHERE right_val > %s', $row['right_val']); // update right values of encompassing nodes and nodes to right
    }

    function incPostCount($id, $amount=1) {
        $result = mysql_safe_query('SELECT * FROM categories WHERE id = %s', $id);
        $row = mysql_fetch_assoc($result);
        return mysql_safe_query('UPDATE categories SET num_posts = num_posts + %s WHERE left_val <= %s AND right_val >= %s', 
            $amount, $row['left_val'], $row['right_val']);
    }

    function decPostCount($id, $amount=1) {
        return self::incPostCount($id, -$amount);
    }

    function getPath($id) {
        $result = mysql_safe_query('SELECT * FROM categories WHERE id = %s', $id);
        $row = mysql_fetch_assoc($result);
        return mysql_safe_query('SELECT * FROM categories WHERE left_val <= %s AND right_val >= %s ORDER BY left_val ASC', 
            $row['left_val'], $row['right_val']);
    }

    function printOptions($selected=null, $format=null, $mult=null, $add=null) {
        if(!isset($format)) $format = '<option value="%s" style="padding-left:%spx"%s>%s</option>';
        if(!isset($mult))   $mult = 15;
        if(!isset($add))    $add = 3;
        $depth = 0;
        $result = mysql_safe_query('SELECT * FROM categories ORDER BY categories.left_val ASC');
        while($row = mysql_fetch_assoc($result)) {
            if(isset($last)) {
                if($row['left_val'] == $last['left_val'] + 1) {
                    ++$depth;
                    $next_right = $last_right + 1;
                } elseif($row['left_val'] > $last['right_val'] + 1) {
                    $levels = $row['left_val'] - $last['right_val'] - 1;
                    $depth -= $levels;
                }
            }
            $last = $row;
            if($row['id'] == $selected) $selected_str = ' selected="selected"';
            else $selected_str = '';
            echo sprintf($format, $row['id'], $depth*$mult+$add, $selected_str, $row['name'], $row['num_posts'], $depth);
        }
    }

    function printTree($format=null) {
        if(!isset($format)) $format = '<li><a href="index.php?cat=%s">%s</a> (%s)';
        $depth = 0;
        $result = mysql_safe_query('SELECT * FROM categories ORDER BY categories.left_val ASC');
        echo '<ul>';
        while($row = mysql_fetch_assoc($result)) {
            if(isset($last)) {
                if($row['left_val'] == $last['left_val'] + 1) {
                    echo '<ul>';
                    ++$depth;
                    $next_right = $last_right + 1;
                } elseif($row['left_val'] > $last['right_val'] + 1) {
                    $levels = $row['left_val'] - $last['right_val'] - 1;
                    echo str_repeat('</li></ul></li>', $levels);
                    $depth -= $levels;
                } else {
                    echo '</li>';
                }
            }
            $last = $row;
            echo sprintf($format, $row['id'], $row['name'], $row['num_posts'], $depth);
        }
        echo str_repeat('</li></ul>', $depth+1);
    }

    function printAdminTree() {
        self::printTree('<li><a href="index.php?cat=%s">%s</a> (<a href="category_delete.php?id=%1$s">Delete</a>)');
    }
}

Whether you want to build your own forum, publish the messages from a mailing list on your Website, or write your own cms: there will be a moment that you'll want to store hierarchical data in a database. And, unless you're using a XML-like database, tables aren't hierarchical; they're just a flat list. You'll have to find a way to translate the hierarchy in a flat file.

Created by ThePeppersStudio (271 days, 4.59 hours ago)