duanqie5741 2016-12-14 16:47
浏览 52

PHP MYSQL - 下拉选择取决于先前的下拉菜单

As I've tried to describe in the title I have an issue in selecting rows from a MYSQL database depending on the id of another drop down list on the same page. I have only been using mysql and php for 2 months or so now and need help.

I have a table of categories with the below headers.

|id | name | parent_id|

There are parent categories, with a parent_id of 0. And Sub categories with the id of the parent as their parent_id, to a maximum depth of 1 child category. For example:

Software Development is a parent category with id = 18 and parent_id = 0. PHP Developer is a subcategory which has id = 30 and parent_id = 18.

I have a drop down list where I can select the category I work in as follows:

$p_query = "SELECT * FROM categories WHERE parent_id = 0 ORDER by id ASC";

$p_result = mysqli_query($con, $p_query) or die(mysqli_error($con));

$categories ='';
while($p_row = mysqli_fetch_assoc($p_result))
        {
        $categories .='<option class="option" value="p::'.$p_row['id'].'">' .$p_row['category_name'].'</option>';
        }

<select name="categories[]" class="categories form-control" id="categories" style="width:100%"  multiple>
        <?php echo $categories;?>
</select>

This is working, no problem. However, when I try to get a second drop down list to show the possible categories whom have their parent_id as the id of any selected parent category I retrieve a drop down list with 'No Search Results found'. The code below is what I am using : 

     $subcategories ='';
     while($p_row = mysqli_fetch_assoc($p_result))
           {
              $c_query = "SELECT * FROM categories WHERE parent_id = ".$categories['id']." ORDER by id ASC";

              $c_result = mysqli_query($con, $c_query) or die(mysqli_error($con));

              while($c_row = mysqli_fetch_assoc($c_result))
                 {
                    $subcategories .='<option class="option" value="c::'.$c_row['id'].'">' .$c_row['category_name'].'</option>';
                 }

            }

             <select name="subcategories[]" class="categories form-control" id="subcategories" style="width:100%"  multiple>

                            <?php echo $subcategories ?>
                </select>

Is there something that I am missing? As a relative beginner to both PHP and MYSQL, I would be very appreciative of any help or advice.

  • 写回答

1条回答 默认 最新

  • douyi8315 2016-12-14 22:49
    关注

    There is nothing wrong with your second query to retrieve sub-categories based on their parent_id so you're good there. You can test easily like so:

    SELECT * FROM categories WHERE parent_id = 1 ORDER by id ASC

    How are you providing the value to $categories['id'] as there is nothing in your code creating this array with an 'id' index? Further you already are using a variable called $categories as a string so you shouldn't re-use that variable name without good reason.

    Since it appears you want to populate the second multiple select box with subcategories based on the selection of the first you will need to use some javascript+AJAX to submit the second query and write the results in the second selector element.

    Using some jquery should help a bit. Try these examples and you'll get the idea.

    myselect.php which contains the interface for selecting the category:

    <?php
$con = mysqli_connect("host", "username", "password", "database");

$p_query = "SELECT * FROM categories WHERE parent_id = 0 ORDER by id ASC";

$p_result = mysqli_query($con, $p_query) or die(mysqli_error($con));

$categories ='';

while($p_row = mysqli_fetch_assoc($p_result))
{
    $categories .='<option class="option" value="p::'.$p_row['id'].'">' .$p_row['category_name'].'</option>';
}
?>
<html>
<head>
    <script
            src="https://code.jquery.com/jquery-3.1.1.min.js"
            integrity="sha256-hVVnYaiADRTO2PzUGmuLJr8BLUSjGIZsDYGmIJLv2b8="
            crossorigin="anonymous">
    </script>
</head>
<body>
Shift or Ctrl + Click to pick more than one<br />
<form id="categoryform" method="POST">
<select name="categories[]" class="categories form-control" id="categories" style="width:100%"  multiple>
        <?php echo $categories;?>
</select>
</form>
Here's what it contains<br />
<form method="POST">
<select name="subcategories[]" class="categories form-control" id="subcategories" style="width:100%"  multiple DISABLED>
</select>

</form>

<script>

    $(document).ready(function() {
        $('#categories').click(function(){
            $('#subcategories').children().remove().end();
            var data = $('#categoryform').serialize();

            $.post("mysubselect.php", data).done(function(data){
                var response = JSON.parse(data);

                for (var k in response){
                    $('#subcategories').append('<option class="option" value="c::' + response[k]['id'] + '">' + response[k]['category_name'] + '</option>');
                }
            });
        });
    })

</script>

</body>
</html>

    And here's an example of the script returning data from your AJAX request.

    mysubselect.php:

    <?php
$con = mysqli_connect("host", "username", "password", "database") or die(mysqli_error());
$result = array();

foreach ($_POST["categories"] as $k => $v) {
    $category_token = explode('::', $v);
    $category_id = mysqli_real_escape_string($con, $category_token[1]);

    $query = mysqli_query($con, "SELECT * FROM categories WHERE parent_id = " . $category_id . " ORDER BY id ASC") or die(mysqli_error());

    while($r = mysqli_fetch_assoc($query)){
       $result[] = $r;
    }
}

    print json_encode($result);
    评论

报告相同问题？

悬赏问题

  • ¥50 导入文件到网吧的电脑并且在重启之后不会被恢复
  • ¥15 （希望可以解决问题）ma和mb文件无法正常打开，打开后是空白，但是有正常内存占用，但可以在打开Maya应用程序后打开场景ma和mb格式。
  • ¥20 ML307A在使用AT命令连接EMQX平台的MQTT时被拒绝
  • ¥20 腾讯企业邮箱邮件可以恢复么
  • ¥15 有人知道怎么将自己的迁移策略布到edgecloudsim上使用吗？
  • ¥15 错误 LNK2001 无法解析的外部符号
  • ¥50 安装pyaudiokits失败
  • ¥15 计组这些题应该咋做呀
  • ¥60 更换迈创SOL6M4AE卡的时候，驱动要重新装才能使用，怎么解决？
  • ¥15 让node服务器有自动加载文件的功能