CODEWITHSUNDEEP
phpjqueryhtmlsqldate
The Employee 123
The Employee 123

Reputation: 504

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

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 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 "<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

Upvotes: 0

Views: 2170

Answers (2)

Junius L
Junius L

Reputation: 16142

When the year select value .change() send ajax request to db and select data base on the selected year.

$('#selectyear').on('change', function () {
    $.post('demo.php', {year: $(this).val()}, function (data) {
        var response = JSON.parse(data);
        $('#selectmonth').empty();
        $.each(response, function(key, value) {
            $('#selectmonth')
                .append($("<option></option>")
                    .attr("value",value)
                    .text(value));
        });
    })
})

Then in your PHP

<?php 
include "dbconnection.php"; 
$year = trim($_POST['year']); 

$sql = "SELECT * FROM dateDatabase WHERE YEAR(MonthYear) = $year"; 

$months = []; 

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
       $months[] = date('F', strtotime($row['MonthYear'])); 
    }
} else {
    $months[] = "0 result";
}
echo json_encode($months);
?>

Upvotes: 1

dotkomm
dotkomm

Reputation: 11

Your Error is within your query. DATENAME is a MSSQL Function and not for Mysql. Try MONTH() and MONTHNAME() instead. As example:

<?php
  $sqlquery = "SELECT DISTINCT MONTH(MonthYear) as MonthNumber,  MONTHNAME(MonthYear) as MonthName FROM dateDatabase";
  $sqltran = mysqli_query($conn, $sqlquery)or die(mysqli_error($conn));
  while ($rowList = mysqli_fetch_array($sqltran)) {
    echo "<option value='".$rowList["MonthNumber"]."'>" .$rowList["MonthName"]. "</option>";

  }
?>

Upvotes: 1

Related Questions