Group mysql results by category and display them into groups under each category

Question

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

My idea is to have menu like this

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

every link has a field named "category". So I want to group and display the links in the menu per category.

I have mysql grouping like

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

and then I have the 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 troubles putting the results in the loop and creating the html by category.

I read many posts here with similar content but couldn't achieve the result I wanted. Any help will be much appreciated. Thanks!

Answer 1

I would use ORDER BY category instead. You can then iterate the result set like

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

Update

There exist three possible solutions until now in this thread to the problem itself.

Original option 1

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

If one does only want to get each parent category once, one should use DISTINCT instead. One should not use GROUP BY without using any aggregation function. Combining GROUP BY with SELECT * is limited to (mostly) MySQL. You cannot select arbitrary columns in this case in ASNI SQL.

A variant of option 1

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

This is the corrected version with DISTINCT instead of GROUP BY.

It still lacks of nested query calls. For 5 parent categories, this leads to 5 queries in the loop. For 10 parent categories, there are already 10 queries inside. One should avoid this kind of growing in general.

Option 3

SELECT * FROM content ORDER BY category, menu_name

usable with the code above.

This is preferable to the other options shown due to different reasons:

• You only need one single database query to gather all data at once. The database spends (on easy queries) most of its time parsing the SQL statement one provided and only a fraction of time to actually gather the data you requested. If you provide lots of SQL code, it has to spend a lot of time parsing it. If you provide less code, it has less to do.
• It is easier for a database to get the data once, sort it once and return it to you once, instead of gather a part, sort a part, return a part and start all over again.

still unstated option 4

There exists an until now unstated further solution. One can use prepared statements, prepare the SQL once and run it with different ids. This would still query all categories inside the loop, but would avoid the necessity to parse SQL code every time.

Actually I do not know if this is better or worse (or sth. in between) than my solution.

Answer 2

SQL GROUP BY statement groups the results so that only one row is returned for each category. This is typically used in conjunction with an aggregate function like count(), to count how many items are in each category. What you need is an either ORDER BY as GhostGambler said, or a separate query for each category as shown bellow. However as you only seem to have 2 categories, this approach seems unnecessarily complicated.

 $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>';
 }

Answer 3

Your query is wrong

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

should be

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

Also since mysql is deprecated avoid using it. Use mysqli or PDO instead