MYSQL - Remove Duplicates in One Column/Group other Coulmns

Discussion in 'Web Design and Development' started by citizenzen, Jun 13, 2012.

  1. citizenzen macrumors 65816

    Joined:
    Mar 22, 2010
    #1
    Apologies for my newbieness.

    I need direction for displaying the following ...

    +---+----------+--------+-------+
    | id | section | item | price |
    +---+----------+--------+-------+
    | 1 | section A | item A | 1,000 |
    | 2 | section A | item B | 1,000 |
    | 3 | section A | item C | 1,000 |
    | 4 | section B | item D | 1,000 |
    | 5 | section B | item E | 1,000 |
    | 6 | section B | item F | 1,000 |
    | 7 | section C | item G | 1,000 |
    | 8 | section C | item H | 1,000 |
    | 9 | section C | item I | 1,000 |
    +--+-----------+--------+-------+

    I want the result to display as ...

    section A
    item A 1,000
    item B 1,000
    item C 1,000

    section B
    item D 1,000
    item E 1,000
    item F 1,000

    section C
    item G 1,000
    item H 1,000
    item I 1,000

    Any help or direction is greatly appreciated!
     
  2. aarond12 macrumors 65816

    aarond12

    Joined:
    May 20, 2002
    Location:
    Dallas, TX USA
    #2
    Do you want the output in MySQL or in another language (e.g., PHP)?

    If I were doing this (in PHP), I would select all the unique values in the section column...

    Code:
    mysql> select distinct section from items;
    +-----------+
    | section   |
    +-----------+
    | Section A |
    | Section B |
    | Section C |
    +-----------+
    3 rows in set (0.00 sec)
    
    ...then I would look through those columns with a query for each unique row I found.

    Code:
    mysql> select item, price from items where section='Section A';
    +--------+-------+
    | item   | price |
    +--------+-------+
    | Item A |  1000 |
    | Item B |  1000 |
    | Item C |  1000 |
    +--------+-------+
    3 rows in set (0.00 sec)
    
    You can get a query to do this all at once, but depending on what you're doing with it (using the results in PHP, for example), you probably want to break it apart like this.
     
  3. citizenzen thread starter macrumors 65816

    Joined:
    Mar 22, 2010
    #3
    PHP.

    I just found a sample of code that I've tested and it seems to do what I need.

    The code was found here.

    Here's the snippet that I was able to modify and have work ...

    PHP:
    $sql "SELECT * FROM products WHERE category IN (10,120,150,500) GROUP BY category ORDER BY category, id";
    $res mysql_query($sql);

    $list = array();
    while (
    $r mysql_fetch_object($res)) {
      
    $list[$r->category][$r->id]['name'] = $r->name;
      
    $list[$r->category][$r->id]['whatever'] = $r->whatever;
      
    // etc
    }
    And then loop through the array. Example:

    PHP:
    foreach ($list as $category => $products) {
      echo 
    '<h1>' $category '</h1>';

      foreach (
    $products as $productId => $productInfo) {
        echo 
    'Product ' $productId ': ' $productInfo['name'];
        
    // etc
      
    }
    }
     

Share This Page