Fetch data from mysql database based on multiple dropdown selections

Question

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.

What have you tried to resolve the problem? Where are you stuck? — Nico Haase
– Nico Haase, Commented Aug 23, 2021 at 15:02
Also, is this a JS problem, a jQuery problem, a PHP problem, or a MySQL problem? — Nico Haase
– Nico Haase, Commented Aug 23, 2021 at 15:03
@NicoHaase I tried the solution given by Prakhar Singh below and I am not getting the undefined array key error anymore. I guess it's a PHP and jQuery problem, but I wanted to know what would be the best way to solve it using PHP or jQuery, or JS or using all of them. — Sachin Jadhav
– Sachin Jadhav, Commented Aug 24, 2021 at 11:09
The solution given by @PrakharSingh works but I have a different issue now, for example, if there is info about counsellors in my database and the user selects 2 filters from the dropdowns such as counsellor and male which are in 2 separate dropdowns, I am getting data which shows all males even if they aren't a counsellor. I want the query to fetch only the data which matches whatever dropdown options the user selects. — Sachin Jadhav
– Sachin Jadhav, Commented Aug 24, 2021 at 11:30

Prakhar Thakur · Accepted Answer · 2021-08-23 12:28:04Z

2

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.

answered Aug 23, 2021 at 12:28

Prakhar Thakur

1,2403 gold badges15 silver badges39 bronze badges

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

2 Comments

Sachin Jadhav Over a year ago

Thank you, your solution works perfectly and I am not getting the undefined array key error anymore.

Prakhar Thakur Over a year ago

That's great 👍 you may consider accepting the answer then 🙂

Mohammed Khurram · Accepted Answer · 2021-08-24 04:21:48Z

1

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.

edited Aug 24, 2021 at 4:21

answered Aug 23, 2021 at 14:41

Mohammed Khurram

6361 gold badge8 silver badges15 bronze badges

3 Comments

Sachin Jadhav Over a year ago

Thanks for the solution I will try this too, but your solution requires me to add a button which the user has to click after selecting options from different dropdowns, I want the filters to get applied automatically every time a user selects an option from any of the 5 dropdowns. By I need to find a way to make the query work even if no option is selected in 1 or more dropdowns. I meant that I want the query to work even if the user selects options from dropdown1 and dropdown2 and doesn't select any options from dropdown3, 4 and 5.

Mohammed Khurram Over a year ago

Is it some kind of sorting thing if I understood is correctly?, if yes, you want to sort the output on user checking the selector?, then do what I have mentioned in last, since your query uses $state1,..$state4 with LIKE clause, you need to define separate queries for each of the selected option.

Sachin Jadhav Over a year ago

Yes, it's sorting. Your solution works too.

Collectives™ on Stack Overflow

Fetch data from mysql database based on multiple dropdown selections

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related