Grouping MySQL Results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
furiousweebee
Forum Commoner
Posts: 69
Joined: Sun Jul 11, 2004 7:38 am
Location: Brisbane, Australia
Contact:

Grouping MySQL Results

Post 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!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
furiousweebee
Forum Commoner
Posts: 69
Joined: Sun Jul 11, 2004 7:38 am
Location: Brisbane, Australia
Contact:

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Perhaps someone else can understand what you're describing.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Try reading the first two threads linked from Useful Posts (sticky.)
Post Reply