dtf76989 2016-09-18 09:49
浏览 51

从同一个表的两个不同COLUMNS中选择DISTINCT值

I have a sql table looks like this.

id     name     cname
1      Ash      abc       
2      Ash      abc
3      Ashu     abc
4      Ashu     xyz
5      Yash     xyzz
6      Ash      xyyy

I want user to select a value from first select drop down list that shows DISTINCT name values and its working fine.

1st Select:

<select id="select1" required="required" class="custom-select standard">
<option value="0" selected="selected">Choose Category</option>
<?php 
    $resultd = mysqli_query($mysqli,"SELECT DISTINCT name FROM advertise");
    if ($resultd)
        {
              while($tier = mysqli_fetch_array($resultd)) 
                {
                    echo '<option value="' .$tier['name'] . '">' . $tier['name'] . '</option>';
                }
        }
?>
</select>

now i want to show values of second select drop down box based on first. Jquery i am using for this is :

<script>
    $(function(){
    var conditionalSelect = $("#select2"),
    // Save possible options
    options = conditionalSelect.children(".conditional").clone();

    $("#select1").change(function(){
    var value = $(this).val();                  
    conditionalSelect.children(".conditional").remove();
    options.clone().filter("."+value).appendTo(conditionalSelect);
}).trigger("change");
});
</script>

2nd Select Box

<select id="select2" required="required" class="custom-select standard">
<option value="0" selected="selected">Choose Location</option>
<option class="conditional name" value="">cname</option>
</select>

All i want to know is what php query should i need to use to get values in 2nd select box based on first. I tried a lot to find solutions but i didn't find any solution that gets its values from database...Thanks in advance...

  • 写回答

1条回答 默认 最新

  • douba1214 2016-09-18 10:07
    关注

    You may not need a 2nd Query and all those complexities if you do things a little differently. This means, you could achieve your goal using one single Query. The Code below demonstrates how. Note: This Solution uses JQuery to make things simpler.

    To test this; just copy and paste the Code AS IS into a new File and see if it works as you had expected.

    CHEERS & GOOD-LUCK!!!

    <?php

    // USE YOUR CONNECTION DATA... PDO WOULD BE HIGHLY RECOMMENDED.
    // INTENTIONALLY USING mysqli (NOT RECOMMENDED) TO MATCH YOUR ORIGINAL POST.
    $conn       = mysqli_connect("localhost", "root", 'root', "test");
    if (mysqli_connect_errno()){
        die("Failed to connect to MySQL: " . mysqli_connect_error());
    }

    $resourceID = mysqli_query($conn, "SELECT * FROM advertise");
    $all        = mysqli_fetch_all($resourceID, MYSQLI_ASSOC);
    $uniques    = [];
    $options1   = "";

    if( !empty($all) ){
        foreach($all as $intKey=>$advertiseData) {
            $key    = $advertiseData['name'];
            $cName  = getCNameForName($all, $key);
            if (!array_key_exists($key, $uniques)) {
                $uniques[$key] = $advertiseData;
                $options1 .= "<option value='{$key}' data-cname='{$cName}'>";
                $options1 .= $key . "</option>";
            }
        }
    }

    function getCNameForName($all, $name){
        $result = [];
        foreach($all as $iKey=>$data){
            if($data["name"] == $name){
                $result[] = $data['cname'];
            }
        }
        return $result ? implode(", ", array_unique($result)) : "";
    }
?>

<html>
<body>
<div>
    <select id="select1" required="required" class="custom-select standard">
        <option value="0" selected="selected">Choose Category</option>
        <?php echo $options1; ?>
    </select>
    <select id="select2" required="required" class="custom-select standard">
        <option value="0" selected="selected">Choose Location</option>
    </select>
</div>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script type="text/javascript">

    (function($) {
        $(document).ready(function(){
            var firstSelect     = $("#select1");
            var secondSelect    = $("#select2");

            firstSelect.on("change", function(){
                var main        = $(this);
                var mainName    = main.val();
                var mainCName   = main.children('option:selected').attr("data-cname");
                var arrCName    = mainCName.split(", ");
                var options2    = "<option value='0' selected >Choose Location</option>";

                for(var i in arrCName){
                    options2   += "<option value='" + arrCName[i]  + "' ";
                    options2   += "data-name='" + mainName + "'>" + arrCName[i] + "</option>
";
                }

                secondSelect.html(options2);
            });

        });
    })(jQuery);
</script>

</body>
</html>
    评论

报告相同问题？

悬赏问题

  • ¥15 #MATLAB仿真#车辆换道路径规划
  • ¥15 java 操作 elasticsearch 8.1 实现 索引的重建
  • ¥15 数据可视化Python
  • ¥15 要给毕业设计添加扫码登录的功能！！有偿
  • ¥15 kafka 分区副本增加会导致消息丢失或者不可用吗？
  • ¥15 微信公众号自制会员卡没有收款渠道啊
  • ¥100 Jenkins自动化部署—悬赏100元
  • ¥15 关于#python#的问题：求帮写python代码
  • ¥20 MATLAB画图图形出现上下震荡的线条
  • ¥15 关于#windows#的问题：怎么用WIN 11系统的电脑 克隆WIN NT3.51-4.0系统的硬盘