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

Question

Aim

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

Database

Existing User Interface

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

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"

This is a specification and not a question We are very willing to help you fix your code, but we dont write code for you — RiggsFolly
– RiggsFolly, Commented Dec 20, 2017 at 14:30
@RiggsFolly Thanks for commenting and not down voting straight away. I'll add in the codes that I tried out. — The Employee 123
– The Employee 123, Commented Dec 20, 2017 at 14:32
And WHO thinks a specification question deserves an upvote. Please get real — RiggsFolly
– RiggsFolly, Commented Dec 20, 2017 at 14:34
Seems like you have already coded a HUGH CLUE for yourself. So for your month dropdown do the same as for the Year but use DISTINCT MONTH(MonthYear) as validMonths — RiggsFolly
– RiggsFolly, Commented Dec 20, 2017 at 14:37

Junius L · Accepted Answer · 2017-12-21 02:31:52Z

1

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);
?>

edited Dec 21, 2017 at 2:31

answered Dec 20, 2017 at 15:51

Junius L

16.2k6 gold badges61 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

The Employee 123 Over a year ago

Hi Julekgwa thanks for helping out and sorry for the late reply but unfortunately it didn't work....I guess I've made a mistake somewhere. Should I replace the value at .attr("value",value) with $months at echo json_encode($months); ?

The Employee 123 Over a year ago

Well the only error was that the dropdown list for months was "Nothing Selected"

Junius L Over a year ago

can you add console.log(data); in the ajax code, and see what it prints.

The Employee 123 Over a year ago

Alright, I've added it and it says that my PHP file can't be found. But I did replace the ("demo.php', with my PHP file which is existingMonths.php. POST http://localhost/projecthealthassist/existingMonths.php 404 (Not Found)

Junius L Over a year ago

double check your spelling, and check if the file exist, paste your code here codeshare.io/GkKyoA, use $.post('existingMonths.php', if the files are in the same directory.

|

dotkomm · Accepted Answer · 2017-12-20 14:56:19Z

1

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

  }
?>

answered Dec 20, 2017 at 14:56

dotkomm

115 bronze badges

2 Comments

The Employee 123 Over a year ago

Thank you for correcting me on that. I've just checked it out. But no months was generated, only "Nothing Selected"

dotkomm Over a year ago

That is because you are actually nothing selecting. I miss the following in your code: the jquery call, and the actually response for it. this looks like a possible duplicate of stackoverflow.com/questions/40154810/…

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related