SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grouping records by category name

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the "grouping" you are referring to is not something that you would do with sql

    the query you want is simple:
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,095
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    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...

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,827
    Mentioned
    142 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ggeiger View Post
    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....
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    PHP Code:
    $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
      
    ...



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •