MYSQL - Remove Duplicates in One Column/Group other Coulmns

[citizenzen]

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!

 

[aarond12]

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...

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.

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.

 

[citizenzen]

Do you want the output in MySQL or in another language (e.g., PHP)?

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 ...

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

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

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