Grouping records by category name

[ggeiger]

Table data structure is:

link_id | name | url | category
--------------------------------------------------------------------
1 | SitePoint Forums | http://sitepoint.com/forums | Programming

I'd like to produce a list of links, grouped by category, such as

Programming Links
> link 1
> link 2

Music Links
> link 1
> link 2

Can you help with the query I need that would help produce this?

[r937]

the "grouping" you are referring to is not something that you would do with sql

the query you want is simple:

SELECT category
     , link_id 
     , name 
     , url
  FROM daTable
ORDER
    BY category

you can optionally add a second sort column

in your application language (php or whatever), loop over the query results and use previous/current logic to determine when a control break on category occurs, and print out the category herader at that time

[K. Wolfe]

We just had a discussion over a situation where someone wanted unlimited subcategories: @http://www.sitepoint.com/forums/show...nd-subcategory ;

Not sure if that's the proper way to tag a thread...

[ggeiger]

Thanks, Guys. Initially I wanted to see if there was some MySQL syntax I wasn't aware of (because there there often is!) that would do this right in the statement.

I like the idea of writing a recursive PHP function to handle the data once the I have it grouped by category. When I've got it working I'll post it here.

[cpradio]

I like the idea of writing a recursive PHP function to handle the data once the I have it grouped by category. When I've got it working I'll post it here.

Recursion doesn't seem to apply to your situation since you can't have nested categories with your setup....

[guido2004]

$saveCategory = '';
while ($row = mysqli_fetch_assoc($result)) {
  if ($row['category'] != $saveCategory) {
    // if it's a new category, print header
    ...
    // then save the new category in $saveCategory
    $saveCategory = $row['category'];
  }
  // print other row data
  ...
}