title: PHP - SQL Dynamic Drop down list for existing dates (Months)
body: <p><strong>Aim</strong></p>
  
  <p>Create a dynamic drop down list of months based on existing dates within the database.</p>
  
  <p><strong>Database</strong></p>
  
  <p><a href="https://i.stack.imgur.com/4JJHj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4JJHj.png" alt="enter image description here"></a></p>
  
  <p><strong>Existing User Interface</strong></p>
  
  <p><a href="https://i.stack.imgur.com/VphUA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VphUA.png" alt="enter image description here"></a></p>
  
  <p><strong>Codes of Existing (UI)</strong></p>
  
  <pre><code>&lt;div class="col-lg-5 col-md-6 col-sm-6 prod-filter" align="right"&gt;
   &lt;font size="4.5" color=""&gt;&lt;span class="fa fa-calendar-o fa-1x"&gt;&lt;/span&gt;
          &lt;b&gt;Year &amp; Month: &lt;/b&gt;
      &lt;/font&gt;
    &lt;select class="selectpicker" id="selectyear" data-width="100px"&gt;
      &lt;?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 "&lt;option value='".$rowList["MonthYear"]."'&gt;" .$rowList["MonthYear"]. "&lt;/option&gt;";
        }
      ?&gt;
    &lt;/select&gt;
      &lt;select class="selectpicker" id="selectmonth" data-width="120px"&gt;
      &lt;option value="01"&gt;January&lt;/option&gt;
      &lt;option value="02"&gt;February&lt;/option&gt;
      &lt;option value="03"&gt;March&lt;/option&gt;
      &lt;option value="04"&gt;April&lt;/option&gt;
      &lt;option value="05"&gt;May&lt;/option&gt;
      &lt;option value="06"&gt;June&lt;/option&gt;
      &lt;option value="07"&gt;July&lt;/option&gt;
      &lt;option value="08"&gt;August&lt;/option&gt;
      &lt;option value="09"&gt;September&lt;/option&gt;
      &lt;option value="10"&gt;October&lt;/option&gt;
      &lt;option value="11"&gt;November&lt;/option&gt;
      &lt;option value="12"&gt;December&lt;/option&gt;
    &lt;/select&gt;
  </code></pre>
  
  <p><strong>Existing UI Description</strong></p>
  
  <p>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.</p>
  
  <p><strong>Desired User Interface</strong></p>
  
  <p><a href="https://i.stack.imgur.com/kVct6.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kVct6.png" alt="enter image description here"></a></p>
  
  <p><strong>Desired UI Description</strong></p>
  
  <p>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 <strong>YEAR</strong> drop down, the Months drop down should only show January and February. </p>
  
  <p>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.</p>
  
  <p><strong>Tried out Codes</strong></p>
  
  <pre><code>&lt;!-- Time period selection --&gt;
          &lt;div class="col-lg-5 col-md-6 col-sm-6 prod-filter" align="right"&gt;
           &lt;font size="4.5" color=""&gt;&lt;span class="fa fa-calendar-o fa-1x"&gt;&lt;/span&gt;&lt;!-- icon for "Select your department" --&gt;
                  &lt;b&gt;Time Period: &lt;/b&gt;&lt;!-- name of dropdownlist --&gt;
              &lt;/font&gt;
            &lt;select class="selectpicker" id="selectyear" data-width="100px"&gt;
              &lt;?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 "&lt;option value='".$rowList["MonthYear"]."'&gt;" .$rowList["MonthYear"]. "&lt;/option&gt;";
                }
              ?&gt;
            &lt;/select&gt;
              &lt;select class="selectpicker" id="selectmonth" data-width="120px"&gt;
              &lt;?php
                $sqlquery = "SELECT DISTINCT DATENAME (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 "&lt;option value='".$rowList["MonthYear"]."'&gt;" .$rowList["MonthYear"]. "&lt;/option&gt;";
                }
              ?&gt;
            &lt;/select&gt;
          &lt;/div&gt;
  </code></pre>
  
  <p>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"</p>
  
  <p><a href="https://i.stack.imgur.com/7AK5I.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7AK5I.png" alt="enter image description here"></a></p>