Selecting from different columns

Question

I'm trying to select from different columns in a table and then have MYSQL search for the field value. I have searched google but the most I managed to get was using IN and LIKE queries which didn't help much with what I'm trying to achieve.

<?php
    //get rows query
    $query = $db->query("SELECT * FROM categories WHERE cat_parent = 'Root'");
    while($row = $query->fetch_assoc()){
        $cat_name = $row["cat_name"];
?>
                <li><a href="$id"><?php echo $cat_name; ?></a>
    </li>
<?php } ?>

<?php
    //get rows query
    $query = $db->query("SELECT * FROM categories WHERE cat_parent = cat_name");
    while($row = $query->fetch_assoc()){
         $cat_name = $row["cat_name"];
?>
   <li class="dropdown"><a href="$cat_id" class="dropdown-toggle" data-toggle="dropdown"><?php echo $cat_name; ?></a>
      <div class="dropdown-menu">
        <div class="dropdown-inner">
                        <ul class="list-unstyled">
                            <li><a href="$cat_id">$cat_name</a></li>
                        </ul>
              </li>
                      </div>
                     </div>
    </li>
<?php } ?>

this code will select from the same row, which is not what I need, I need it to select from all the table where the cat_name = cat_parent so for example if cat parent is Electronics and the cat_name is laptops then the laptops must be displayed under the electronics.

I hope my question is clear.

UPDATE: I managed to display all categories correctly and filtered except they are repeating each other.. Here is the new code:

<?php
$query = $db->query("SELECT * FROM categories WHERE cat_parent = 'Root' AND cat_parent NOT IN (SELECT cat_name FROM categories)");
while($row = $query->fetch_assoc()){
    $parent_cat_name = $row["cat_name"];

    ?>
  <li>
    <a href="$id">
      <?php echo $parent_cat_name; ?>
    </a>
  </li>
  <?php
    $query1 = $db->query("SELECT * FROM categories WHERE cat_parent = '".$parent_cat_name."'");
    while($row = $query1->fetch_assoc()){
        $cat_name = $row["cat_name"];
        ?>
    <li class="dropdown">
      <a href="$cat_id" class="dropdown-toggle" data-toggle="dropdown">
        <?php echo $parent_cat_name; ?>
      </a>
      <div class="dropdown-menu">
        <div class="dropdown-inner">
          <ul class="list-unstyled">
            <li><a href="$cat_id"><?php echo $cat_name?></a></li>
          </ul>
        </div>
      </div>
    </li>
    <?php
    }
}
?>

I am trying to find a MYSQL query to filter all categories that have the value of 'Root' and are not inside the column cat_name..

Answer 1

Figured it out. Had to create another MYSQL table called category_sub and do some workaround in my queries. So the final outcome should be

<?php
$query = $db->query("SELECT * FROM categories WHERE cat_name NOT IN (SELECT sub_cat_parent FROM categories_sub) AND cat_parent = 'Root'");
while($row = $query->fetch_assoc()){
    $parent_cat_name = $row["cat_name"];

    ?>
  <li>
    <a href="$id">
      <?php echo $parent_cat_name; ?>
    </a>
  </li>
  <?php }

    $query1 = $db->query("SELECT * FROM categories_sub ");
    while($row = $query1->fetch_assoc()){
        $cat_name = $row["sub_cat_name"];
        $cat_parent = $row["sub_cat_parent"];
        ?>
    <li class="dropdown">
      <a href="$cat_id" class="dropdown-toggle" data-toggle="dropdown">
        <?php echo $cat_parent; ?>
      </a>
      <div class="dropdown-menu">
        <div class="dropdown-inner">
          <ul class="list-unstyled">
            <li><a href="$cat_id"><?php echo $cat_name?></a></li>
          </ul>
        </div>
      </div>
    </li>
    <?php

}
?>

I know this is not the best approach and there must be a very easier approach (but this was the best I can do).

Answer 2

Based on the information in your question this should do what you are asking.

<?php
$query = $db->query("SELECT * FROM categories WHERE cat_parent = 'Root'");
while($row = $query->fetch_assoc()){
    $parent_cat_name = $row["cat_name"];

    ?>
  <li>
    <a href="$id">
      <?php echo $parent_cat_name; ?>
    </a>
  </li>
  <?php
    $query = $db->query("SELECT * FROM categories WHERE cat_parent = '".$parent_cat_name."'");
    while($row = $query->fetch_assoc()){
        $cat_name = $row["cat_name"];
        ?>
    <li class="dropdown">
      <a href="$cat_id" class="dropdown-toggle" data-toggle="dropdown">
        <?php echo $cat_name; ?>
      </a>
      <div class="dropdown-menu">
        <div class="dropdown-inner">
          <ul class="list-unstyled">
            <li><a href="$cat_id">$cat_name</a></li>
          </ul>
        </div>
      </div>
    </li>
    <?php
    }
}
?>

The code above places the sub category while() loop inside of the parent loop so that it will retrieve all of the sub categories where cat_parent match the parent in which it is in.