CODEWITHSUNDEEP
phpmysqldate
Brian Cherdak
Brian Cherdak

Reputation: 109

Add current date to dropdown if doesn't exist in database

I have a table with a date selector that I am trying to add a current date option UNLESS there are rows already present with that date.

Here is how it looks:

enter image description here

Today is the 25th yet it shows the date for the 20th since there are rows present with the 07/20/2013 date in it. How would I do it so it shows the current date?

Here is the code 

<div class="lookup">
<form action="index.php" method="get">
<select name="exam_date" ONCHANGE="location = this.options[this.selectedIndex].value;" id="type" class="neutral">
<?php
$sql_gdate    = "SELECT distinct pat_date from patients ORDER BY pat_date DESC";
$result_gdate = mysql_query($sql_gdate);
while ($row_gdate = mysql_fetch_assoc($result_gdate)) {
    echo '<option value="?exam_date=' . $row_gdate['pat_date'] . '"';
    IF ($exam_date == $row_gdate['pat_date']) {
        echo 'selected="selected"';
    } else {
        echo '';
    }
    echo '>' . $row_gdate['pat_date'] . '</option>';
}
?>
</select>
</form> 
</div>

Upvotes: 0

Views: 241

Answers (2)

user1191247
user1191247

Reputation: 12973

You can use the following SQL to modify your table so that the dates are stored properly -

ALTER TABLE patients
    ADD COLUMN pat_date_new DATE NOT NULL;
UPDATE patients SET pat_date_new = STR_TO_DATE(pat_date,'%m/%d/%Y');
-- NOW CHECK THAT THE VALUES ARE STORED CORRECTLY BEFORE DROPPING THE OLD COLUMN

ALTER TABLE patients
    DROP COLUMN pat_date,
    CHANGE COLUMN pat_date_new pat_date DATE NOT NULL;

Once the dates are stored in the correct format you will be able to use the query posted previously -

SELECT DISTINCT pat_date FROM patients UNION SELECT CURRENT_DATE ORDER BY pat_date DESC

You will need to change the format of the date received form your datepicker. You can do this easily in either PHP or SQL -

INSERT INTO `patients` (`pat_date`) VALUES(STR_TO_DATE('07/25/2013','%m/%d/%Y'));

NOTE: You should not be using the deprecated mysql_* functions. Use either mysqli_* or PDO.

Upvotes: 0

Randy
Randy

Reputation: 16677

select dt from
(
SELECT distinct pat_date as dt from patients 
union
SELECT CURRENT_DATE() from dual
)
ORDER BY dt DESC

Upvotes: 1

Related Questions