[php] selecting unique values from a column

I have a MySQL table which contains the following type of information:

    Date            product 
2011-12-12           azd
2011-12-12           yxm
2011-12-10           sdx
2011-12-10           ssdd  

Here is an example of a script I use to get data from this table:

<?php

$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("db", $con);
$sql=mysql_query("SELECT * FROM buy ORDER BY Date");
while($row = mysql_fetch_array($sql))
{

 echo "<li><a href='http://www.website/". $row['Date'].".html'>buy ". date("j, M Y", strtotime($row["Date"]))."</a></li>";

    }
    mysql_close($con);
?> 

This script displays every date from the table, e.g.

12.dec 2011
12.dec.2011
10.dec.2011
10.dec.2011

I would like to only display unique dates, e.g.

12.dec.2011
10.dec.2011

DISTINCT is always a right choice to get unique values. Also you can do it alternatively without using it. That's GROUP BY. Which has simply add at the end of the query and followed by the column name.

SELECT * FROM buy GROUP BY date,description

Use something like this in case you also want to output products details per date as JSON and the MySQL version does not support JSON functions.

SELECT `date`,
CONCAT('{',GROUP_CONCAT('{\"id\": \"',`product_id`,'\",\"name\": \"',`product_name`,'\"}'),'}') as `productsJSON`
FROM `buy` group by `date` 
order by `date` DESC

 product_id product_name     date  
|    1     |     azd    | 2011-12-12 |
|    2     |     xyz    | 2011-12-12 |
|    3     |     ase    | 2011-12-11 |
|    4     |     azwed  | 2011-12-11 |
|    5     |     wed    | 2011-12-10 |
|    6     |     cvg    | 2011-12-10 |
|    7     |     cvig   | 2011-12-09 |

RESULT
       date                                productsJSON
2011-12-12T00:00:00Z    {{"id": "1","name": "azd"},{"id": "2","name": "xyz"}}
2011-12-11T00:00:00Z    {{"id": "3","name": "ase"},{"id": "4","name": "azwed"}}
2011-12-10T00:00:00Z    {{"id": "5","name": "wed"},{"id": "6","name": "cvg"}}
2011-12-09T00:00:00Z    {{"id": "7","name": "cvig"}}

Try it out in SQL Fiddle

If you are using a MySQL version that supports JSON functions then the above query could be re-written:

SELECT `date`,JSON_OBJECTAGG(CONCAT('product-',`product_id`),JSON_OBJECT('id', `product_id`, 'name', `product_name`)) as `productsJSON`
FROM `buy` group by `date`
order by `date` DESC;

Try both in DB Fiddle

use

SELECT DISTINCT Date FROM buy ORDER BY Date

so MySQL removes duplicates

BTW: using explicit column names in SELECT uses less resources in PHP when you're getting a large result from MySQL

Another DISTINCT answer, but with multiple values:

SELECT DISTINCT `field1`, `field2`, `field3` FROM `some_table`  WHERE `some_field` > 5000 ORDER BY `some_field`

The rest are almost correct, except they should order by Date DESC

SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;

Depends on what you need.

In this case I suggest:

SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;

because there are few fields and the execution time of DISTINCT is lower than the execution of GROUP BY.

In other cases, for example where there are many fields, I prefer:

SELECT * FROM buy GROUP BY date ORDER BY date DESC;

Use this query to get values

SELECT * FROM `buy` group by date order by date DESC

There is a specific keyword for the achieving the same.

SELECT DISTINCT( Date ) AS Date 
FROM   buy 
ORDER  BY Date DESC;