导航列表正在重复

Question

i was given help previously on how to join multiple tables together to get this navigation list to work, as you can see i have done this, but now i am trying to output the navigation in my list, but it is duplicating the top and bottom categories based on how many products are in those categories: this is previous link that shows my table setup:

Joining 2 tables with foreign key id

Here is my code trying to echo out the navigation correctly.

try
{ 
    $result = $pdo->query('SELECT product.*, bottom_category.bottom_name, top_category.top_name
                            FROM product
                            INNER JOIN bottom_category ON bottom_category.id = product.bottom_category_id 
                            INNER JOIN top_category ON top_category.id = bottom_category.top_category_id
                            ORDER BY top_category.id,bottom_category.id');
} // end try
catch (PDOException $e) 
{ 
    echo 'There was a error fetching the products.' . $e->getMessage();
    exit(); 
} // end catch

$products = array();

foreach ($result as $row)
{
$products[] = array('top_name' => $row['top_name'], 
              'bottom_name' => $row['bottom_name']);
}

?>

<div class="sidebar">
    <h4 class="sidebar-header">Select Products</h4>
    <form class="nav-search-form">
        <input type="search" name="search" placeholder="Search products">
    </form>
    <nav class="sidebar-links"> 
        <ul>
            <li><a id="red" href="/semtronics/index.php">New Products</a></li>
            <?php
            foreach ($products as $product):
            ?>
            <li><a href="#"><?php echo htmlspecialchars($product['top_name']);?></a>

                <ul>
                    <li><a href=""><?php echo htmlspecialchars($product['bottom_name']);?></a></li>
                </ul>
            </li>
            <?php endforeach; ?>        
        </ul>
    </nav>
</div><!-- sidebar -->

Now it all works the only problem is it is duplicating the navigation list based on how many products are linked to that category.

Answer 1

I think you need another solution for this task. You don't need product here

Query what you need is:

select bottom_category.name as bottom_name, top_category.name as top_name
from bottom_category
inner join top_category on top_category.id = bottom_category.top_category_id
order by top_category.id, bottom_category.id

I was thinking that you need something from product table in your code, but if no - use SQL query above.

Or you need data from product table ?

If you need from product you can run

select bottom_category.name as bottom_name, top_category.name as top_name
from product
inner join bottom_category on bottom_category.id = product.bottom_category_id 
inner join top_category on top_category.id = bottom_category.top_category_id
group by product.bottom_category_id
order by top_category.id, bottom_category.id

But be careful, you don't know which row from product would be used in this case