John John - 5 months ago 57
SQL Question

Dynamic Navigation Menu in MySQL, PDO to show one instance

I am building a Dynamic Navigation for a project and i would like to only show the Countries that have a City

parent_id
and also only show one instance of a City (Eg: Bangkok) if more than one.

Note: China has NO
parent_id
so this is the one NOT to show.

Not sure if possible with this setup, but here we go.

Here is the Database Table with the four columns and the
id
is
AUTO_INCREMENT


id | menu | parent_id | type
----|-----------|-----------|------
1 | China | 0 | null
2 | Myanmar | 0 | null
3 | Laos | 0 | null
4 | Thailand | 0 | null
5 | Yangon | 2 | Tour
6 | Champasak | 3 | Tour
7 | Pakse | 3 | Tour
8 | Bangkok | 4 | Tour
9 | Bangkok | 4 | Tour
10 | Bangkok | 4 | Tour


This is a SNIPPET of the HTML where the
class
is instantiated

<li class="dropdown">
<a class="dropdown-toggle" href="#">TOURS</a>
<?php
$menu = new DynamicNavigation();
$menu->navigationTourMenu();
?>
</li>


Here is the
class


class DynamicNavigation {

public function navigationTourMenu() {
$sql = DB::getInstance()->query('SELECT * FROM navigation');

if($sql->count()) {
$rows = $sql->results();
}

$items = $rows;
$id = '';

echo '<ul class="dropdown-menu">';
foreach($items as $item) {
if($item->parent_id == 0) {
echo '<li class="dropdown">';
echo '<a class="dropdown-toggle" href="#"><i class="fa fa-thumb-tack"></i> ' . $item->menu . '</a>';
$id = $item->id;
$this->navigationTourSubMenu($items, $id);
echo '</li>';
}
}
echo '</ul>';
}

public function navigationTourSubMenu($items, $id) {
echo '<ul class="dropdown-menu">';
foreach($items as $item) {
if($item->parent_id == $id && $item->type == 'Tour') {
echo '<li>';
echo '<a href="' . BASE_URL . '/search?searching=' . $item->menu . '" onClick="showProgress()"><i class="fa fa-street-view"></i> ' . $item->menu . '</a>';
$this->navigationTourSubMenu($items, $item->id);
echo '</li>';
}
}
echo '</ul>';
}

}


So is there a
query
that will do this within this setup. Any help would be greatfully received as i am not a MySQL Guru

Answer

try this sql insted of yours

SELECT distinct(nav.menu), nav.id, nav.parent_id, nav.type 
FROM navigation nav
group by  nav.menu ,  nav.parent_id, nav.type 
order by nav.parent_id 
Comments