Search code examples
phphtmlmysqldropdown

How I can make drop down menu from my categories mysql table only show if parent_id is not 0

So hey there as the title said I am looking for away to make my categories with subcategories. I been looking in stackoverflow for what I need but none has help me of the examples..

Here is how my table look like enter image description here So I know what I want and what I need but I have no idea how I can do that possible

  1. I have to SELECT * FROM categories ORDER by position ASC
  2. I have to check if parent_id is bigger then 0.
  3. I have to remove the parent_id from my navbar and show them only under the category name where it should be by dropdown menu .

But I have no idea how I could do all of that ..

Here is how I am selecting only my categories and display them

                  $catsq = mysqli_query($con,"SELECT * FROM categories ORDER by position ASC");
                    while($catinfo=mysqli_fetch_assoc($catsq)) {
                      echo '
                         <li class="nav-item'.(isset($_GET["cat"]) && $_GET["cat"]==$catinfo["id"] ? " active" : "").'">
                           <a class="nav-link" href="./index.php?cat='.$catinfo["id"].'">'.$catinfo["name"].'</a>
                         </li>
                      ';
                    }

and it's look like this

<ul class="nav navbar-nav">
    <li class="nav-item">
    <a class="nav-link" href="cat=1">TestCat</a>
    </li>
    <li class="nav-item">
    <a class="nav-link" href="cat=2">TestCat2</a>
    </li>
    <li class="nav-item">
     <a class="nav-link" href="cat=3">TestSub</a>
    </li>
</ul>

but I want It to look like this

<ul class="nav navbar-nav">
    <li class=""><a href="cat=1">TestCat</a></li>
    <li class="dropdown ">
        //TestCat2 have to doing nothing always.
        <a href="#" class="dropdown-toggle" data-toggle="dropdown">TestCat2</i></a>
        <ul class="dropdown-menu">
            <li><a class="nav-link" href="cat=3">TestSub</a></li>
        </ul>
    </li>
</ul>

when the parent_id is more then 0..

If anyone can help me with this would be great..

Thanks to everybody.

Solution

  • There are several approaches you can take:

    1. Build an array
    2. Nested queries
    3. Recursion

    Array

    This approach builds a data structure that you can iterate through in your view. Working example

    <?php
// get db connection...

// create categories array
$stmt = mysqli_query($con, "SELECT * FROM categories ORDER BY position ASC");
while( $row = mysqli_fetch_assoc($stmt)) {
    // $category[ $row['parent_id] ][ $row['id'] ] = $row; // use if you need to access other fields in addition to name
    $category[ $row['parent_id] ][ $row['id'] ] = $row['name'];
}

// other php stuff...

?>
<html>

... snip ...

<ul class="nav navbar-nav">
    <?php foreach($category[0] as $id => $name): ?>
        <?php if( isset( $category[$id]) ): ?>

          <li class="dropdown ">
                <a href="#" class="dropdown-toggle" data-toggle="dropdown"><?= $name ?></a>
                <ul class="dropdown-menu">
                <?php foreach($category[$id] as $sub_id => $sub_name): ?>

                    <li><a class="nav-link" href="?cat=<?= $sub_id ?>" ><?= $sub_name ?></a></li>
                <?php endforeach; ?>
                </ul>
          </li>

        <?php else: ?>  

            <li class="">
                <a href="?cat=<?= $id ?>"><?= $name ?></a>
            </li>

        <?php endif; ?>
    <?php endforeach; ?>

</ul>

    Nested Queries

    This method is easiest to display using an imaginary class that does all the sql stuff behind the scenes. For the sake of argument, we will assume a class Category that has a method named listByParent($parent_id) which returns a list of rows having the designated parent_id.

    <?php
$cat = new Category();

$topLevel = $cat->listByParent(0);
?>
<html>

... snip ...

<ul class="nav navbar-nav">
    <?php foreach( $topLevel as $topRow ): ?>

        <!-- note, this method is run on every iteration of top level categories -->
        <?php $subRows = $cat->listByParent($topRow['id']) ?>

        <?php if( count($subRows)): ?>
        <li class="dropdown ">
            <a href="#" class="dropdown-toggle" data-toggle="dropdown"><?= $topRow['name'] ?></a>
                <ul class="dropdown-menu">
                <?php foreach($subRows as $row): ?>

                    <li><a class="nav-link" href="?cat=<?= $row['id'] ?>" ><?= $row['name'] ?></a></li>
                <?php endforeach; ?>

                </ul>
          </li>

        <?php else: ?>  

            <li class="">
                <a href="?cat=<?= $topRow['id'] ?>"><?= $topRow['name'] ?></a>
            </li>
        <?php endif; ?>
    <?php endforeach; ?>

</ul>

    Recursion

    Using recursion would allow you to have “unlimited” levels of subcategories. However, it’s a level of complexity that does not seem warranted in this case. But should you want to pursue it, note that the best way to approach it would be to make a template for the html that could be accessed programatically, with $cat->findByParent() being a key player...