How to count sub category level using PHP & MySQL.

Question

I want to be able to count which sub category my new category is so i can give it its proper url value for example sub category 3 url value will be saved as &sub3=category

Here is an category example.

Web Design //parent category 0
    Programming //sub-cat 1
        HTML //sub-cat 2
            Basics //sub-cat 3
SEO
Networking

The PHP code I have so far that deals with this part is below.

if(isset($_POST['url']) && $parent_id >= 1){
    $url = '&sub=' . $_POST['url'];
}

Here is my full PHP script.

if (isset($_POST['submitted']) && !empty($_POST['category']) && !empty($_POST['url'])) {

    if (isset($_POST['parent_id'])) {
        $parent_id = (int) $_POST['parent_id'];
    } else {
        $parent_id = 0;
    }

    if(isset($_POST['url']) && $parent_id == 0){
        $url = '?cat=' . $_POST['url'];
    }

    if(isset($_POST['url']) && $parent_id >= 1){
        $url = '&sub=' . $_POST['url'];
    }

    $q = sprintf("INSERT INTO categories (parent_id, category, url) VALUES (%d, '%s', '%s')", $parent_id, mysqli_real_escape_string($mysqli, $_POST['category']), mysqli_real_escape_string($mysqli, $url)); 
    $r = mysqli_query($mysqli, $q);

    if (mysqli_affected_rows($mysqli) == 1) {
        echo '<p>The category has been added!</p>';
    } else {
        echo '<p>The category could not be added!</p>';
    }

}
echo '<form action="index.php" method="post">
<fieldset>
<legend>Add a category</legend>
<p>Category: <input name="category" type="text" size="60" maxlength="255" /></p>
<p>Parent category:';
echo '<select name="parent_id">
      <option value="0">None</option>';

    function make_list ($parent, $depth) {
        global $option;     
        foreach ($parent as $id => $cat) {
            $indent = str_repeat('&nbsp; ', $depth * 2);
            echo '<option value="' . $cat['id'] . '">';
            if($cat['parent_id'] != 0){
                echo $indent;
            }

            echo $cat['category'] . '</option>';                
            if (isset($option[$id])) {              
                make_list($option[$id], $depth+1);              
            }                       
        }       
    }

    $mysqli = mysqli_connect("localhost", "root", "", "sitename");
    $dbc = mysqli_query($mysqli,"SELECT * FROM categories ORDER BY parent_id, category ASC");
    if (!$dbc) {
        print mysqli_error();
    } 

    $option = array();

    while (list($id, $parent_id, $category) = mysqli_fetch_array($dbc, MYSQLI_NUM)) {
        $option[$parent_id][$id] =  array('category' => $category, 'id' => $id, 'parent_id' => $parent_id);
    }

    $depth = 0;

    make_list($option[0], $depth+1);

echo '</select></p>
<p>Category URL: <input name="url" type="text" size="60" maxlength="255" /></p>
<input name="submitted" type="hidden" value="true" />
<input name="submit" type="submit" value="Add This Category" />
</fieldset>
</form>
';

Answer 1

If you don't want to loop through results to find the depth of your categories, just add another column called 'depth' to the categories table, and give each category its parent's depth + 1.