6. [PHP] DB와 연동된 SelectBox(셀렉트박스) 동적 구성 / SelectBox 선택에 따라 다음 SelectBox 값 변화

안녕하세요. 

오늘은 DB와 연동된 selectBox를 동적으로 구성하는 방법을 고민한 결과 해결해서, 같은 고민을 가지신 분들께서 참고하실 수 있으면 좋겠습니다.

---

연결 DB = MSSQL

DB 테이블 이름 = tblCategory

---

코드

<label>카테고리</label>
<br />
<select name = "LCategory" id = "LCategory" class="form-control" onchange="selectLCategory();">       <!--대-->
    <option value=''>선택해주세요.</option>
    <?php
        $Lquery = "SELECT DISTINCT LargeCategory FROM tblCategory ORDER BY LargeCategory DESC";
        $Lresult = sqlsrv_query($conn, $Lquery);

        while ($Ldata = sqlsrv_fetch_array($Lresult, SQLSRV_FETCH_ASSOC)) {
    ?>
            <option value = "<?php echo $Ldata["LargeCategory"]?>"><?php echo $Ldata["LargeCategory"]?></option>
    <?php
        }
    ?>
</select>

<br /> 

<select name = "MCategory" id = "MCategory" class="form-control"  onchange="selectMCategory();">      <!--중-->
	<option value=''>선택해주세요.</option>
</select>

<br />  

<select name = "SCategory" id = "SCategory" class="form-control">        <!--소-->
	<option value=''>선택해주세요.</option>
</select>

<!-- 첫번째 select 값 가져오기 -->
<script>
    function selectLCategory() {
        var L = document.getElementById("LCategory"); 
        var Large = L.options[L.options.selectedIndex].value;

        $.ajax({
            url: "select_MCategory_value.php",
            method:"POST",
            data:{Large:Large},
            dataType:"json",
            success:function(data) {
                $("select[name='MCategory']").empty();          // 두번째 select 박스 비우기
                $("select[name='SCategory']").empty();          // 세번째 select 박스 비우기

                $("select[name='MCategory']").append("<option value=''>선택해주세요.</option>");
                $("select[name='SCategory']").append("<option value=''>선택해주세요.</option>");

                $.each(data, function(key, obj){
                    $.each(obj,function(k,value){
                        $("select[name='MCategory']").append("<option value = '" + obj.MiddleCategory + "'>" + obj.MiddleCategory + "</option>");
                    });
                });                                    
            }
        })
    }
</script>

 <!-- 두번째 select 값 가져오기 -->
 <script>
    function selectMCategory() {
        var M = document.getElementById("MCategory"); 
        var Middle = M.options[M.options.selectedIndex].value;

        $.ajax({
            url: "select_SCategory_value.php",
            method:"POST",
            data:{Middle:Middle},
            dataType:"json",
            success:function(data) {
                console.log(data);

                $("select[name='SCategory']").empty();          // 세번째 select 박스 비우기

                $("select[name='SCategory']").append("<option value=''>선택해주세요.</option>");

                $.each(data, function(key, obj){
                    $.each(obj,function(k,value){
                        $("select[name='SCategory']").append("<option value = '" + obj.SCategoryName + "'>" + obj.SCategoryName + "</option>");
                    });
                });                                    
            }
        })
    }
</script>​

 

 

select_MCategory_value.php

<?php
    $DB_IP = "";
    $DB_ID = "";
    $DB_PWD = "";
    $DB_NAME = "";
    $DB_ConnectInfo = array("UID"=>$DB_ID, "PWD"=>$DB_PWD, "Database"=>$DB_NAME, "CharacterSet" => "UTF-8");

    date_default_timezone_set('Asia/Seoul');
    
    $connect = sqlsrv_connect($DB_IP, $DB_ConnectInfo);

    if(isset($_POST["Large"]))  
    {
        $query = "SELECT DISTINCT MiddleCategory 
                    FROM tblCategory 
                    WHERE MiddleCategory IS NOT NULL
                    AND LargeCategory = '".$_POST["Large"]."'";

        $result = sqlsrv_query($connect, $query);
        $json_array = array();

         while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
             $json_array[] = $row;
        }
        echo json_encode($json_array);
    }
?>

 

 

select_SCategory_value.php

<?php
    $DB_IP = "";
    $DB_ID = "";
    $DB_PWD = "";
    $DB_NAME = "";
    $DB_ConnectInfo = array("UID"=>$DB_ID, "PWD"=>$DB_PWD, "Database"=>$DB_NAME, "CharacterSet" => "UTF-8");

    date_default_timezone_set('Asia/Seoul');
    
    $connect = sqlsrv_connect($DB_IP, $DB_ConnectInfo);

    if(isset($_POST["Middle"]))  
    {
        $query = "SELECT DISTINCT SCategoryName
                    FROM tblCategory 
                    WHERE MiddleCategory = '".$_POST["Middle"]."'";

        $result = sqlsrv_query($connect, $query);
        $json_array = array();

         while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
             $json_array[] = $row;
        }
        echo json_encode($json_array);
    }
?>