PHP - SQL Dynamic Drop down list for existing dates (Months)

Databases
, ,
1

Aim

Create a dynamic drop down list of months based on existing dates within the database.

Database

enter image description here

Existing User Interface

enter image description here

Codes of Existing (UI)

<div class="col-lg-5 col-md-6 col-sm-6 prod-filter" align="right">
 <font size="4.5" color=""><span class="fa fa-calendar-o fa-1x"></span>
		<b>Year & Month: </b>
	</font>
  <select class="selectpicker" id="selectyear" data-width="100px">
    <?php
      $sqlquery = "SELECT DISTINCT year(MonthYear) as MonthYear FROM dateDatabase";
      $sqltran = mysqli_query($conn, $sqlquery)or die(mysqli_error($conn));
      while ($rowList = mysqli_fetch_array($sqltran)) {
        echo "<option value='".$rowList["MonthYear"]."'>" .$rowList["MonthYear"]. "</option>";
      }
    ?>
  </select>
	<select class="selectpicker" id="selectmonth" data-width="120px">
    <option value="01">January</option>
    <option value="02">February</option>
    <option value="03">March</option>
    <option value="04">April</option>
    <option value="05">May</option>
    <option value="06">June</option>
    <option value="07">July</option>
    <option value="08">August</option>
    <option value="09">September</option>
    <option value="10">October</option>
    <option value="11">November</option>
    <option value="12">December</option>
  </select>

Existing UI Description

The existing user interface only has dynamic drop down for the existing years but not for the Months as the months are hard-coded as shown in the existing codes. The Database contains dates for years 2016, 2017, and 2018, and as shown in the Existing UI Image, only those years are prompted into the drop down.

Desired User Interface

enter image description here

Desired UI Description

The desired user interface will have a dynamic drop down for the existing months. For example, as shown in the database image, 2018 has data for only the first 2 months, which are January and February therefore the after selecting 2018 for the YEAR drop down, the Months drop down should only show January and February.

Another example, if 2017 only has data for the Months, March, July, August, October, and December, the user will only be able to select those months after selecting the Year 2017.

Tried out Codes

<!-- Time period selection -->
        <div class="col-lg-5 col-md-6 col-sm-6 prod-filter" align="right">
         <font size="4.5" color=""><span class="fa fa-calendar-o fa-1x"></span><!-- icon for "Select your department" -->
				<b>Time Period: </b><!-- name of dropdownlist -->
			</font>
          <select class="selectpicker" id="selectyear" data-width="100px">
            <?php
              $sqlquery = "SELECT DISTINCT year(MonthYear) as MonthYear FROM dateDatabase";
              $sqltran = mysqli_query($conn, $sqlquery)or die(mysqli_error($conn));
              while ($rowList = mysqli_fetch_array($sqltran)) {
                echo "<option value='".$rowList["MonthYear"]."'>" .$rowList["MonthYear"]. "</option>";
              }
            ?>
          </select>
    		<select class="selectpicker" id="selectmonth" data-width="120px">
            <?php
              $sqlquery = "SELECT DISTINCT MONTHNAME (month, (MonthYear)) as MonthYear FROM dateDatabase WHERE EXISTS(SELECT month(MonthYear) FROM dateDatabase)";
              $sqltran = mysqli_query($conn, $sqlquery)or die(mysqli_error($conn));
              while ($rowList = mysqli_fetch_array($sqltran)) {
                echo "<option value='".$rowList["MonthYear"]."'>" .$rowList["MonthYear"]. "</option>";
              }
            ?>
          </select>
		</div>

I tried writing the distinct but it didn’t generate the existing months. It still generated all the months in numbers. So I thought of adding in the DATENAME to generate the names but that only displayed “Nothing Select”

enter image description here

2

This query looks wonky. It should be very similar to your Year query. I renamed the field name alias because while the DBMS will allow it, that always causes my brain to short circuit

SELECT DISTINCT MONTHNAME(MonthYear) AS AvailableMonths 
  FROM dateDatabase
1 Like
3

Thank you for your reply. I have just change the codes but it displays it like this:

It still shows every month in a year just not in order. Should I still put in a “EXIST” function?

4

That would make more sense (I blame it on being on a holiday mindset already). You just need to add an order by…

SELECT DISTINCT MONTHNAME(MonthYear) AS AvailableMonths 
  FROM dateDatabase
ORDER BY MONTH(MonthYear)
1 Like
5

um… no!

2 Likes
6

Ahh Holidays :smile: Enjoy and Happy Holidays!!
Okay I have included that but I have included that the Drop Down list is displaying “Nothing Selected”. Should I include Ajax to call from a file after the Year has been selected?

7

Alright noted and Thanks. But should I include Ajax to call from a php file after the year has been selected?

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.