How to generate dynamic dropdown PHP Javascript mySQL

Question

I need help with generating dynamic dropdowns using PHP, Javascript and mySQL. I am not good with AJAX and Javascript and hence I'm asking for help here.

I have a table named Hotel, which contains a list of hotel names and categories. They are categorised by locations such as North, South, East and West. I am trying to allow the user to pick the categories they want, then the second dropdown will generate a list of available hotels under that particular category. As mentioned, I am not good with AJAX or JS.

The question has been solved! I have edited my answer to work with the database, assuming basic user root and no password. the table hotel has 3 columns, id, category and name.

booking.php

<div class="form-group">
<label class="control-label col-sm-3" for="PreferredHotel">Preferred Hotel:</label>
<div class="col-sm-3">
<select class="form-control" name="hotelCategory" onchange="fetchHotelNameByArea(this.value)">
<option value="0">Please select area above first</option>
<?php
mysqli_select_db($dbConn, $database_dbConn);
$query_hotelselect = "SELECT * FROM hotel GROUP BY Category";
$hotelselect = mysqli_query($dbConn, $query_hotelselect) or die(mysqli_error($dbConn));
$row_hotelselect = mysqli_fetch_assoc($hotelselect);
while ($row_hotelselect = mysqli_fetch_assoc($hotelselect)) {
echo "<option value='" . $row_hotelselect['Category'] . "'> " . $row_hotelselect['Category'] . " </option>";
}
?>
</select>
<?php 
echo $row_hotelselect;
?>
</div>
<div class="col-sm-3" id="fetchHotelNameByAreaResult">
<select class="form-control">
<option value="0">Please select area above first</option>
</select>
</div>
<script>
function fetchHotelNameByArea(HotelArea) {
//above (HotelArea) are actually the value (this.value) in the form which will be what the user select (North, South, East or West)
var xhttp = new XMLHttpRequest();
var url = "getter.php";//<- just a sample url
var data = new FormData();
//below will "assign HotelArea to $_POST['SearchValue']"
data.append('SearchValue', HotelArea);
xhttp.open('POST', url, true);
xhttp.send(data);
xhttp.onreadystatechange = function() {
if (xhttp.readyState == 4 && xhttp.status == 200) {
document.getElementById("fetchHotelNameByAreaResult").innerHTML = xhttp.responseText;
}
}
}
</script>
</div>

getter.php

<?php
if ($_POST['SearchValue']) {
$searchname = $_POST['SearchValue'];
require_once('Connections/dbConn.php');

mysqli_select_db($dbConn, $database_dbConn);
$query_preferredhotel = "SELECT * FROM hotel WHERE Category = '$searchname'";
$preferredhotel = mysqli_query($dbConn, $query_preferredhotel) or die("Could not select examples");
$row_preferredhotel = mysqli_fetch_assoc($preferredhotel);
echo'<select class="form-control" name="preferredHotel">';
    while ($row_preferredhotel = mysqli_fetch_assoc($preferredhotel)) {
        echo "<option value='" . $row_preferredhotel['Name'] . "'> " . $row_preferredhotel['Name'] . " </option>";
    }
}echo '</select>';
?>

Kinda got stuck here after making the dropdown list appear. I found an article on https://css-tricks.com/dynamic-dropdowns/ but they do not have the example for the database and I was hoping someone could help me with this as I understand I would most likely need AJAX to request for data from the database/server and populate the second dropdown. Im not asking for spoonfeeding, but I really have very little clues about AJAX. Any guidance would be helpful!

EDITED

The issue with only part of the keywords being passed has been solved, thanks to Mark Ng spotting my markup error! I am really thankful for all your help in answering my questions, thank you!

Answer 1

sample concept. There are 2 select(dropdown), the first will populate the second based on its category.

1st select

<select onchange="fetchHotelNameByArea(this.value)">
    <option value="North">North</option>
    <option value="South">South</option>
    <option value="East">East</option>
    <option value="West">West</option>  
</select>   

2nd select(to be populated by javascript later)

<div id="fetchHotelNameByAreaResult">
<!--For temporary only, this select was purposely placed inside this div id, it will get re-written by javascript when result are generated-->
<select>
    <option value="0">Please select area above first</option>
</select>
</div>

JS(Native)

<script>
function fetchHotelNameByArea(HotelArea) {
    //above (HotelArea) are actually the value (this.value) in the form which will be what the user select (North, South, East or West)
    var xhttp = new XMLHttpRequest();
    var url = "./php/find_hotel_name_by_area.php";//<- just a sample url
    var data = new FormData();
    //below will "assign HotelArea to $_POST['SearchValue']"
    data.append('SearchValue',HotelArea);
    xhttp.open('POST',url,true);
    xhttp.send(data);
    xhttp.onreadystatechange = function() { 
        if(xhttp.readyState == 4 && xhttp.status == 200) {  
            document.getElementById("fetchHotelNameByAreaResult").innerHTML = xhttp.responseText;
        }       
    }   
}
</script>

php query (The select will be returned to div id="fetchHotelNameByAreaResult" by javascript

<?php
if($_POST['SearchValue']) {
    $searchname = $_POST['SearchValue']
    //.... your query
    //"SELECT * FROM hotel WHERE Category = '$searchname'";
    echo '<select class="blablabla">';
    while ($row_hotelselect = mysqli_fetch_assoc($hotelselect)) {
        echo "<option value=" . $row_hotelselect['id'] . "> " . $row_hotelselect['Name'] . " </option>";
        }
    }   
    echo '</select>';
    ?>      

What is going on? 1. Upon 1st select, the onchange gets fired, calling function fetchHotel... 2. JS send data to server, where a php file will process the request, onreadystate... will detect if response is ready, and innerHTML will re-write whatever is in div id="fetchHotelNameByAreaResult" with the resposeText generated by the php script.

There are other ways to do it via jQuery, etc. But once you get the basic concept, you are ready to move on.

EDIT to address this issue.

Hey there again, the codes above works fine. But however, I realised that the dropdown list only passes one part of the value inside the variable (eg. ritz carlton, only passes ritz to the next form). Anyone aware of any solutions?

There is a html markup error.

echo "<option value=" . $var . ">" . $var . "</option>";
//The above will return <option value=ritz carlton>ritz carlton</option> in html.
//the problem lies with value=ritz carlton as there is a space in between.
//html will think that it is value="ritz" while carlton is not a valid attribute, it will simply ignore it and only set the value as ritz, so only the value ritz was posted.

//In order to get the full string parse, you have to quote them like below.
echo "<option value='". $var ."'>" . $var . "</option>"; 
// echo "<option value=" . "'" . $var . "'" . "</option>";
// echo "<option value=/" " . $var . " /"</option>";
//a lot more ways to achieve same result.
//These will return <option value="ritz carlton">ritz carlton</option> in html. This will set the value as ritz carlton and the value "ritz carlton" will be posted.
?>

Answer 2

I read your question and understood your problem. Basic steps :

1. First create a section behind your first drop down in which hotel name will be fetched according to the category selected in first drop down. Example :

       <div class = "hotelDropDown">
          // hotel drop down
       </div>
   

2. After, create an ajax request that will fetch the category name from first drop down and will send the request to the php file to fetch the hotel name correspinding to the selected category name and make the drop down.

   Example :

           $.ajax({
             url : "hotelfetch.php",// function to create hotel dropdown
             data : {categoryName : $('.hotelCategory').val()}
             success :
               function(data){
                  //generate dropdown of hotel name
              });
           });
   

3. Make a view file of hotelfetch.php in which you create a dropdown of hotel name according to fetched category name and replace it in the html of the section created below category drop down which I created for you.