How to get selected value in one select option and based on that, fetch data from MySQL table to show another select option in the same form

Question

In my web page I have two select options in a single form. first select option is categories which is fetched from MySQL table using PHP and works fine. But second select option shows sub-categories which needs to be fetched from MySQL table based on user selected categories option. I dont know ajax, jQuery codes to use with MySQL database. Here is my code

first select option(works fine)

<select class="form-control"  name="catid" required>
    <option value="">select</option>
       <?php 
        $sql="select cat_id,cat_name from tbl_categories";
        $query=mysqli_query($conn,$sql);
        while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ ?>
       <option value="<?php echo $row['cat_id'];?>"><?php echo ($row['cat_name']); ?></option>
       <?php 
         }
         if(isset($_POST['catid'])){
          $cid=$_POST['catid'];
         }
        ?>
</select>

second select option(not working)

<select class="form-control"  name="subcatid" required>
    <option value="">select</option>
        <?php 
            $sql2="select s_id,subcat_name from tbl_subcategories where cat_id='$cid' order by subcat_name";
             $query2=mysqli_query($conn,$sq2);
             while($row2 = mysqli_fetch_array($query2, MYSQLI_ASSOC)){ ?>
             <option value="<?php echo $row2['s_id'];?>"><?php echo ($row2['subcat_name']); ?></option>
         <?php } ?>
  </select>

Welcome to SO. Please try searching before posting a new question, this question has been answered many times here already, eg: https://stackoverflow.com/q/17367332/6089612, https://stackoverflow.com/q/42524122/6089612, https://stackoverflow.com/q/32389767/6089612, https://stackoverflow.com/q/54622998/6089612, https://stackoverflow.com/q/10788726/6089612, https://stackoverflow.com/q/8749326/6089612 ... — Don't Panic, Dec 26 '21 at 21:39
Does this answer your question? [Using Ajax To Populate A Select Box](https://stackoverflow.com/questions/17367332/using-ajax-to-populate-a-select-box) — Don't Panic, Dec 26 '21 at 21:39

score 0 · Accepted Answer · answered Dec 26 '21 at 16:58

The idea of "chained select menus" has been covered many times before and can be accomplished in different ways but the essence of it is displayed below. The primary select menu has a change event handler assigned to it which fires an ajax request to some PHP script somewhere. In the code below the ajax request is sent to the same page but it could be a separate file. The target php script intercepts the POST request and uses the supplied POST data to build a new SQL query which is executed and the results processed to generate, in this case, a string of HTML data which is sent back to the ajax callback. Rather than using the now old fashioned XMLHttpRequest the below uses the fetch api It must also be noted that your code was vulnerable to SQL injection - thus you must learn to use prepared statements

None of the below has been tested so there might be small mistakes.. but it should point you in the right direction. Merry Xmas.

<?php

    if( $_SERVER['REQUEST_METHOD']=='POST' && isset(
        $_POST['catid'],
        $_POST['action']
    )){
        ob_clean();
        
        if( $_POST['action']=='get-sub-category' ){
            $html='';
        
            $sql='select `s_id`, `subcat_name` from `tbl_subcategories` where `cat_id`=? order by `subcat_name`';
            $stmt=$conn->prepare( $sql );
            $stmt->bind_param('s',$_POST['catid']);
            $stmt->execute();
            $stmt->store_result($sid,$name);
            
            
            while( $stmt->fetch() ) $html.=sprintf('<option value="%s">%s',$sid,$name);
            $stmt->free_result();
            $stmt->close();
            
            exit( $html );
        }
    }
?>
<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title></title>
    </head>
    <body>
        <select class="form-control"  name="catid" required>
            <option selected hidden disabled>select
            <?php
                $sql='select `cat_id`, `cat_name` from `tbl_categories`';
                $res=$conn->query( $sql );
                
                while( $row = $res->fetch_assoc() ){
                    printf(
                        '<option value="%s">%s',
                        $row['cat_id'],
                        $row['cat_name']
                    );
                }
            ?>
        </select>

        <select name='subcatid' class='form-control'>
            <option selected hidden disabled>Please select
        </select>
        
        <script>
            document.querySelector('select[name="catid"]').addEventListener('change',function(e){
                
                let fd=new FormData();
                    fd.set('catid',this.value);
                    fd.set('action','get-sub-category');
                    
                fetch(location.href,{method:'post',body:fd })
                    .then(r=>r.text())
                    .then(html=>{
                        let oSelect=document.querySelector('select[name="subcatid"]');
                        let options=oSelect.querySelector('option:not(disabled)');
                            options.forEach(option=>oSelect.removeChild(option))
                        
                        oSelect.inserAdjacentHTML('beforeend',html);
                    })

            });
        </script>
    </body>
</html>

Thank you. I found an accurate solution in a website with your same concept https://www.webslesson.info/2020/04/dynamic-dependent-select-box-with-search-option-in-php-using-jquery-ajax.html — none, Dec 27 '21 at 04:42
Alas the code you found is vulnerable to sql injection and should not be used as it is without modification — Professor Abronsius, Dec 27 '21 at 09:05
@dumbguy Do none of the links I posted help? There are so many solutions to this problem here on SO already, if none of them help, edit your question and describe what you've tried and what happened. — Don't Panic, Dec 27 '21 at 22:13

How to get selected value in one select option and based on that, fetch data from MySQL table to show another select option in the same form

1 Answers1