Reputation: 84
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..
Upvotes: 0
Views: 57
Reputation: 84
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).
Upvotes: -1
Reputation: 21
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.
Upvotes: 2