How do I DISTINCT a query when using a DATE() query on datetime field

Question

I have the code below and works fine BUT I get multiple duplicate dates, obviously this is because they have many time stamps through the day but I'm only interested in the date, is there a way to DISTINCT the combo box after I have run the query?

 <select name="closeddate" class="box" value="No filter applied" id="closeddate">
<option>No filter applied</option><?php
$sql1= "SELECT * FROM tbl_jobs where STATUS = 'closed'";
$result = mysql_query($sql1);
while($data = mysql_fetch_array($result)){
$datedata = date('d-m-Y', strtotime($data['close_date']));
echo "<option>$datedata</option>";

Example

Answer 1

You could use as the SQL:

select distinct date(close_date) as close_date
from tbl_jobs
where STATUS = 'closed'

And so (updated to use mysqli):

<select name="closeddate" class="box" value="No filter applied" id="closeddate">
<option>No filter applied</option>
<?php
$sql1 = "SELECT distinct date(close_date) as close_date FROM tbl_jobs where STATUS = 'closed'";
$result = mysqli_query($sql1);
while ($data = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    //$datedata = date('d-m-Y, strtotime($data['close_date']));
    $date = new DateTime($data['close_date']);
    $datedata = $date->format('d-m-Y')
    echo "<option>$datedata</option>";
}
?>
</select>

If you want MySQL to do the date formatting, then:

<select name="closeddate" class="box" value="No filter applied" id="closeddate">
<option>No filter applied</option>
<?php
$sql1 = "SELECT distinct date_format(close_date, '%d-%m-%Y') as close_date FROM tbl_jobs where STATUS = 'closed'";
$result = mysqli_query($sql1);
while ($data = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $datedata = $data['close_date'];
    echo "<option>$datedata</option>";
}
?>
</select>