Geek asks and answers

Grouping mysql into categories and displaying them in groups for each category

I am trying to create a simple css menu that gets data from mysql table.

My idea is to have such a menu.

    Category 1
    - link 1
    - link 2
    - link 3
    Category 2
    - link 1
    - link 2
    - ect...

Each link has a field called a category. Therefore, I want to group and display links in the menu for each category.

I have mysql grouping like

$sql = "SELECT * FROM content group by category";
$result = mysql_query($sql);

and then i have html like this

<ul class="menu">
    <li id="category1" class="files">
         <a href="#category1">Category 1</a>
         <ul class="sub-menu">
             <li><a href="#">link 1</li>
             <li><a href="#">link 2</li>
             <li><a href="#">link 3</li>
         </ul>
     </li>
    <li id="category2" class="files">
         <a href="#category2">Category 2</a>
         <ul class="sub-menu">
             <li><a href="#">link 1</li>
             <li><a href="#">link 2</li>
             <li><a href="#">link 3</li>
         </ul>
     </li>
 </ul>

The db table looks like this:

CREATE TABLE IF NOT EXISTS `content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `menu_name` text,
  `menu_name_en` text,
  `menu_url` varchar(255) NOT NULL DEFAULT '',
  `header_name` text,
  `header_name_en` enum('MEDIA','GENERAL') NOT NULL DEFAULT 'MEDIA',
  `text` longtext NOT NULL,
  `text_en` text,
  `category` enum('Category 1', 'Category 2') NOT NULL DEFAULT 'Category 1',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `visible` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

INSERT INTO content (id, menu_name, menu_name_en, menu_url, header_name, header_name_en, text, text_en, category, date, visible) VALUES (26, 'test name', '', 'test_url', 'test name', '', '<p>test text</p>', '<p>text text</p>', 'MEDIA', '2014-02-23', '1');

So, I am having problems that put the results in a loop and create html by category.

I read a lot of posts here with similar content, but could not achieve the desired result. Any help would be much appreciated. Thank!

+4
source share
4 answers

ORDER BY category . ,

$old = null;
foreach ($st as $s) {
  if $old != $s['id']
    echo 'Main category';
    $old = $s['id'];
  echo 'subcategory'

.

1

SELECT * FROM content group by category
foreach
  SELECT * FROM content WHERE category=$cat['category']

, DISTINCT. GROUP BY - . GROUP BY SELECT * ( ) MySQL. ASNI SQL.

1

SELECT DISTINCT category FROM content ORDER BY category
foreach
  SELECT * FROM content WHERE category=$cat['category']

DISTINCT GROUP BY.

. 5 5 . 10 10 . .

3

SELECT * FROM content ORDER BY category, menu_name

.

, :

  • , . ( ) SQL, , . SQL, . , .
  • , , , , .

4

. , SQL . , SQL.

, ( - ), .

+6

SQL GROUP BY , . , count(), . ORDER BY, GhostGambler, , . , 2 , .

 $q=$db->query("SELECT DISTINCT category FROM content ORDER BY category");
 foreach($q as $cat){
    echo '<li id="'.$cat['category'].'" class="files">';  
    echo '<a href="'.$cat['category'].'">'.$cat['category'].'</a>'; 
    echo '<ul class="sub-menu">';
    $linkq=$db->query("SELECT * FROM content WHERE category='" . $cat['category'] . "'"); 
    foreach($linkq as $link){
       echo '<li><a href="#">'.$link['menu_name'].'</a></li>';
    }
    echo '</ul></li>';
 }
+4

$sql = "SELECT * FROM content WHERE group by category";


$sql = "SELECT * FROM content group by category";

, mysql , . mysqli PDO

+2

, . , .

$select=$connection->query("select * from menus");
    while($result=$select->fetch_assoc()){
    echo "<li>";        
    echo "<a href=\"#\">".$result['MenuTitle']."</a>";
    $menuid=$result['MenuId'];
    if($result==true){
    $selectsubmenu=$connection->query("Select * from submenu where menuid='$menuid'");
        echo "<ul>";
            while($resultsubmenu=$selectsubmenu->fetch_assoc()){     
                    echo "<li><a href=\"Services.php?submenu=".$resultsubmenu['SubMenuId']."\">".$resultsubmenu['SubMenuTitle']."
                    </a></li>";
                    }
        echo "</ul>";
                }
            echo "</li>";   
}
0

More articles:


All Articles