Question posted in
You can find the official documentation here.

Fetch data from mysql database based on multiple dropdown selections – Jquery ajax

I am working on a project where data about therapists is stored in a database, I have 5 dropdowns on my page and I want the data to be fetched based on the dropdown values selected by the user.

<div id="dropdowns"> 
        <form method="post">    
            <?php
                session_start();
                
                $username = "root";
                $password = "";
                $database = "align";
                $mysqli = new mysqli("localhost", $username, $password, $database);

                $sql = "SELECT DISTINCT `Designation` FROM `therapists`"; 
                if($res = $mysqli->query($sql))
                {

                    echo '<div class="select">
                                <select id="profession" name="profession" onchange="getSelectDesignation(this.value);">
                                <option selected disabled>Filter by Profession</option> ';              
                    while ($row = $res->fetch_assoc()) {
                        echo "<option value='" . $row['Designation'] ."'>" . $row['Designation'] ."</option>";
                    }
                    echo '      </select>
                        </div>';
                    $res->free();
                
                } 

                $ids = "SELECT DISTINCT `Identifies As` FROM `therapists`";
                if($res = $mysqli->query($ids))
                {
                    echo '<div class="select">
                                <select id="idas" name="idas" onchange="getSelectIdentifiesas(this.value);">
                                <option selected disabled>Identifies as</option> ';              
                    while ($row = $res->fetch_assoc()) {
                        echo "<option value='" . $row['Identifies As'] ."'>" . $row['Identifies As'] ."</option>";
                    }
                    echo '      </select>
                        </div>';
                    $res->free();
                
                }
                
                $clgr = "SELECT DISTINCT `Client Group` FROM `therapists`";
                if($res = $mysqli->query($clgr))
                {

                    echo '<div class="select">
                            <select id="clgr" name="clgr" onchange="getSelectClientGroup(this.value);">
                                <option selected disabled>Client Group</option> ';

                    while ($row = $res->fetch_assoc()) {
                        echo "  <option value='" . $row['Client Group'] ."'>" . $row['Client Group'] ."</option>";
                    } 
                    echo '   </select>
                        </div>';
                    $res->free();
                }

                $istr = "SELECT DISTINCT `Issues Treated` FROM `therapists`";
                if($res = $mysqli->query($istr))
                {

                    echo '<div class="select">
                            <select id="istr" name="istr" onchange="getSelectIssuesTreated(this.value);">
                                <option selected disabled>Issues treated</option> ';

                    while ($row = $res->fetch_assoc()) {
                        echo "  <option value='" . $row['Issues Treated'] ."'>" . $row['Issues Treated'] ."</option>";
                    } 
                    echo '   </select>
                        </div>';
                    $res->free();
                } 
                
                $lan = "SELECT DISTINCT `Languages` FROM `therapists`";
                if($res = $mysqli->query($lan))
                {

                    echo '<div class="select">
                            <select id="idas" name="lan" onchange="getSelectLanguages(this.value);">
                                <option selected disabled>Languages</option> ';

                    while ($row = $res->fetch_assoc()) {
                        echo "  <option value='" . $row['Languages'] ."'>" . $row['Languages'] ."</option>";
                    } 
                    echo '   </select>
                        </div>';
                    $res->free();
                }
              
            ?>
        </form>      
    </div>

Whenever a user selects a dropdown option, a specific function for each dropdown gets called which takes the value of the option selected and sends it to a different page called fetch_data which displays the information about the therapists.

function getSelectDesignation(val1)
{
    $.ajax({
        type: 'post',
        url: 'fetch_data.php',
        data: {
            get_option1:val1,
        },
        success: function (response) {
            document.getElementById("boxes").innerHTML=response; 
        }
    });
}

function getSelectIdentifiesas(val2)
{
    $.ajax({
        type: 'post',
        url: 'fetch_data.php',
        data: {
            get_option2:val2,
        },
        success: function (response) {
            document.getElementById("boxes").innerHTML=response; 
        }
    });
}

function getSelectClientGroup(val3)
{
    $.ajax({
        type: 'post',
        url: 'fetch_data.php',
        data: {
            get_option3:val3,
        },
        success: function (response) {
            document.getElementById("boxes").innerHTML=response; 
        }
    });
}

function getSelectIssuesTreated(val4)
{
    $.ajax({
        type: 'post',
        url: 'fetch_data.php',
        data: {
            get_option4:val4,
        },
        success: function (response) {
            document.getElementById("boxes").innerHTML=response; 
        }
    });
}

function getSelectLanguages(val5)
{
    $.ajax({
        type: 'post',
        url: 'fetch_data.php',
        data: {
            get_option5:val5,
        },
        success: function (response) {
            document.getElementById("boxes").innerHTML=response; 
        }
    });
}

Below is the code in my fetch_data page :

<?PHP
   session_start();
   
   $username = "root";
   $password = "";
   $database = "align";
   
   
   $mysqli = new mysqli("localhost", $username, $password, $database);

   $state1 = $_POST['get_option1'];
   $state2 = $_POST['get_option2'];
   $state3 = $_POST['get_option3'];
   $state4 = $_POST['get_option4'];
   $state5 = $_POST['get_option5'];
   $loc = $_SESSION['location'];

   $find="SELECT * FROM `therapists` AS `T` inner join `personal details` as `P` ON `T`.`Therapist ID` = `P`.`Therapist ID` WHERE(`Location` LIKE '%".$loc."%' AND  `Designation` LIKE '%".$state1."%' AND  `Identifies As` LIKE '%".$state2."%' AND  `Client Group` LIKE '%".$state3."%' AND  `Languages` LIKE '%".$state5."%'  AND  `Issues Treated` LIKE '%".$state4."%')";
   if ($result = $mysqli->query($find)) {
                   
       while ($row = $result->fetch_assoc()) {
           $field1name = $row["Therapist ID"];
           $field2name = $row["Name"];
           $field3name = $row["Designation"];
           $field4name = $row["Identifies As"];
           $field5name = $row["Client Group"];
           $field6name = $row["Languages"];
           $field7name = $row["Issues Treated"];
           $field8name = $row["Location"];
           $field9name = $row["Phone Number"];
           $field10name = $row["Intro"];
           $field11name = $row["Instagram Link"];
           $field12name = $row["Linkedin Link"];
           $field13name = $row["Aasha URL"];
           
           echo '<div id="profile-card">
           <div id="info">
               <div class="name-desig-img">
                   <div class="name-desig">          
                       <a class="therapist-name" href="http://localhost/aasha/profile.php/'. $field2name .'">';echo $field2name;echo'</a>
                       <p>';echo $field3name;echo'</p>
                   </div>
                   <div class="p-img">
                       <img class="prof-img" src="';echo $field14name;echo'">
                   </div>  
               </div>
               <div class="intro">
                   <p>';echo $field10name;echo'</p>
               </div>
               <div class="location">  
                   <p>';echo $field8name;echo'</p><p>
               </div>
           </div>    
           <div id="links">
               <div id="t-socials">
                   <div class="tp"><a class="t-links" href="';echo $field13name;echo'">';echo' Profile </a></div>
                   <div class="tli">|</div>
                   <div class="tli"><a class="t-links" href="';echo $field12name;echo '"><i class="fab fa-linkedin">';echo'</i></a> </div>  
                   <div class="tli">|</div>
                   <div class="tli"><a class="t-links" href="';echo $field11name;echo '"><i class="fab fa-instagram-square">';echo'</i></a></div>
               </div>  
               <p class="showphone">
                   <span class="clickshow" style="display: inline;"><b>Show Phone Number</b></span>
                   <span class="hiddenphone" style="display: none;">
                       <span>';echo $field9name;echo'</span>
                   </span>
               </p>
           </div>
           </div>';       
       }
       
   /*freeresultset*/
   $result->free();
   }

?>

Now the issue is whenever I select a dropdown option the value for the other dropdowns is empty and I get the undefined array key error in PHP. For example, if I select option1 which is the first dropdown, I get undefined array key error for option2, option3, option4, and option5. If I select option2 I get undefined array key error for option1, option3, option4, and option, and so on. I need to find a way to make the query work even if no option is selected in 1 or more dropdowns.

Tags:

2

Answers


  1. 0

    You can check if the options are set by using
    isset() and if the value is not set, Just assign '' a string of length zero to those variables.

    if(isset($_POST['get_option1'])){
    $state1 = $_POST['get_option1'];
}else{
    $state1 = '';
}

    Similarly for all other variables.

    Login or Signup to reply.
  2. 0

    First of all, only 1 request can be made in ajax at once, according to your code, the request is sent when onchange() so only 1 request will be made.

    Everything looks fine but you are fetching all variables from _POST where as only 1 is sent at a time, so, you need to send all the variables at a time for your query $find="SELECT * FROM `therapists` AS `T` inner join `personal details` as `P` ON `T`.`Therapist ID` = `P`.`Therapist ID` WHERE(`Location` LIKE '%".$loc."%' AND `Designation` LIKE '%".$state1."%' AND `Identifies As` LIKE '%".$state2."%' AND `Client Group` LIKE '%".$state3."%' AND `Languages` LIKE '%".$state5."%' AND `Issues Treated` LIKE '%".$state4."%')"; to work.

    Do something like this…..

    <div id="dropdowns"> 
        <form>   
         <!-- Your selectors here -->
            <select id="idas" name="lan">
                <option> ...... </option>
            </select>
            <select id="clgr" name="lan">
                <option> ...... </option>
            </select>
            <select id="istr" name="lan">
                <option> ...... </option>
            </select>
            <input type="button" onclick = "gettheValues()" value="GetIt"> 
        </form>      
    </div>

    Java script

    function gettheValues()
{
    var val1 = document.getElementById("idas").value;
    var val2 = document.getElementById("clgr").value;
    var val3 = document.getElementById("istr").value;
    $.ajax({
        type: 'post',
        url: 'fetch_data.php',
        data: {
            get_option1:val1,
            get_option2:val2,
            get_option3:val3
        },
        success: function (response) {
            document.getElementById("boxes").innerHTML=response; 
        }
    });//Send values all together...
}

    I guess nothing need to be changed in php code.

    And I need to find a way to make the query work even if no option is selected in 1 or more dropdowns. what you meant by this?

    If those states state1 - state5 are important for your query then it is not possible to run that query.
    It is you who know the importance of the states in that query so it won’t be appropriate to answer.

    If you think you can query as per different selectors do like this.

    <?php 
if(isset($_POST['get_option1']))
{ 
 //appropriate query here

$query = "Your query with option1";
}
if(isset($_POST['get_option2']))
{ 
 //appropriate query here
$query = "Your query with option2";
}
...
..etc to option5

//Here your result of query with html
?>

    For any queries comment below, if you need a working example I will make one.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search