Page 1 of 1

Grouping MySQL Results

Posted: Mon Aug 13, 2007 1:05 am
by 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:

Code: Select all

$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!

Posted: Mon Aug 13, 2007 6:20 pm
by califdon
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.
I'm not clear on whether you want to limit the length of a column or not. First you say that you want the number of links per column to be limited, then you say that you want the links to ALWAYS stay with their parent category. Those two statements appear to be contradictory.

If you want somebody to write the code for you to make the columns (except the last) of equal length, that's fairly easy to do, and if you wrote the code that you quoted, you should be able to do that without asking. If you want to do something else, you better explain it more carefully.

Posted: Mon Aug 13, 2007 6:58 pm
by furiousweebee
I do want to limit the column length, but AFTER grouping links with their parent category. So it needs to calculate the number of links (and category titles) that are going to appear in the first column (due to the grouping), then replicate that in each successive column. Currently it works out the column length before displaying the records. So no, the statements are not contradictory. I explained what the code currently does, and what I would like it to be doing instead.

I didn't write the original code -- most if it was taken from a site I worked on previously, which a friend of mine wrote. I have only changed it slightly.

In short, I want to end up with a page of categorised links, split into columns as equally arranged as possible.

Posted: Mon Aug 13, 2007 7:22 pm
by califdon
Perhaps someone else can understand what you're describing.

Posted: Tue Aug 14, 2007 9:05 am
by feyd
Try reading the first two threads linked from Useful Posts (sticky.)