Grouping MySQL Results

[furiousweebee]

Hi,

 

I've created a page of links, each assigned a category. There are two tables - "favourites_links" and "favourites_categories". This is how the information currently appears:

 

Category 1 Name

Link 1

Link 2

Link 3

 

Category 2 Name

Link 1

Link 2

Link 3

 

I have also set my code to automatically start a new column once a certain number of links have been reached, so that my columns end up approximately equal in height. The problem is, if the current column has finished but the current category is still listing its links, then the next column shows those links, but without the category title. What I would like to do is ensure that links ALWAYS stay with their parent category, so that in the end, all columns remain approximately equal in height, except for the last column, which can be any height.

 

Here is my code:

 

<?php
$resource = mysql_query("SELECT l.id id, l.cat_id cat_id, c.title cat_title, l.title title, l.link link, l.home home, l.work work FROM favourites_links l INNER JOIN favourites_categories c ON l.cat_id = c.id ORDER BY cat_id") or die(mysql_error());
                
    // count the rows
    $num_rows = mysql_num_rows($resource);
        
    // maximum number of rows to show per column
    $max_rows = ceil($num_rows / 3);
        
    // starting row count
    $row_count = 1;
        
    echo '<div class="column">'."\n";
        
    while ($current_page = mysql_fetch_assoc($resource)) {    
            
        if ($row_count <= $max_rows) {
            
            if (!isset($category) || $category != $current_page['cat_title']) {                        
                $category = $current_page['cat_title'];
                echo '<h2>'.$category.'</h2>'."\n";            
            }
                
            echo '<a href="http://'.$current_page['link'].'">'.$current_page['title'].' '.$row_count.'</a>'."\n";
                
        }
            
        else {
    
            echo "\n".'</div>'."\n".'<div class="column">'."\n";    
            $row_count = 0;
        }
            
        $row_count++;
            
    }
        
echo '</div>'."\n";

 

Any help would be greatly appreciated!