PHP – How to Select Distinct One Column and Return the Other Column Data

PHP

I am newbie to SQL and PHP programming so please be patient with me. 🙂

For example I have one table with three fields: ID, ITEMNAME, SIZE, and data like this:

+----+--------------+-------+
| ID |   ITEMNAME   | SIZE1 |
+----+--------------+-------+
|  1 | Blue T-shirt |   M   |
+----+--------------+-------+
|  2 | Blue T-shirt |   L   |
+----+--------------+-------+
|  3 |  Red T-shirt |   M   |
+----+--------------+-------+

And I want to display it using SQL command as:

+--------------+-----------------+
|   ITEMNAME   |       SIZE      |
+--------------+-----------------+
| Blue T-shirt | select tag(M/L) |
+--------------+-----------------+
|  Red T-shirt |        M        |
+--------------+-----------------+

The items with the same name will merged and create a select tag with the option of the size column data. How can I do that?

Any other way will be appreciated.

Best Answer

Assuming you are using MySQL

Here is how I'd do the SQL:

SELECT
ID,
ITEMNAME,
CONCAT("select tag(",GROUP_CONCAT(DISTINCT size
                                  ORDER BY size DESC
                                  SEPARATOR "/"), ')') AS SIZE
FROM items
GROUP BY ITEMNAME

Results:

| ID |      ITEMNAME |            SIZE |
|----|---------------|-----------------|
|  1 |  Blue T-shirt | select tag(M/L) |
|  3 |   Red T-shirt |   select tag(M) |

SQL Fiddle

Once you have it in PHP you can then iterate it out.

Related Question