Populating dropdown - PHP Ajax MySQL

2023-02-17 12:39 问答作者：

I have 2 dropdowns on my HTML page : The first drop down contains the database column names based on which the the second dropdown would be populated i.e.

I have a single table with fields: <Student Name, Degree, City> and following would be the entries;

1. "A", "BS", "New York"
2. "B", "BS", "Chicago"
3. "C", "MS", "Boston"
4. "D", "MS", "New York"

So my first dr开发者_如何学编程opdown would contain the column names i.e. "Degree" and "City".

If I select "Degree", the 2nd dropdown should populate "BS" and "MS" and if I select "City", the 2nd dropdown should select "New York", "Boston" and "Chicago".

How can I go about with the implementation?

[Adding my code]:

the changeSecond(first) method remains exactly the same as you suggested

<body>
   <form method="POST" action="" name="mainForm">
      <table>
         <tr>
            <td> Filter by: </td>
            <td>
            <div id="first">
               <select onChange="changeSecond(this.value)">
                  <option value="1">All</option>
                  <option value="2">Degree</option>
                  <option value="3">City</option>
               </select>
            </td>
         </tr>
         <tr>
            <td>&nbsp</td>
            <td>
               <div id="second">
                  <select name="val">
                     <option value=""></option>
                  </select>
               </div>
            </td>
         </tr>
      </table>
   </form>
</body>

And this is the second_script.php as you suggested:

<?
   $link = mysql_connect("localhost", "root", "");

   if (!$link)
   {
      die('Could not connect: ' . mysql_error());
   }
   if (mysql_select_db("myDatabase", $link))
   {
      $first=mysql_real_escape_string($_REQUEST["first"]);
      $query="SELECT ".$first." FROM myTable GROUP BY ".$first;
      $data=mysql_query($query);

      echo "<select id=\"second\">";
      while($row=mysql_fetch_row($data))
      {
         echo "<option value=\"".$row[0]."\">".$row[0]."</option>";
      }
      echo "</select>";
   }
   echo mysql_error();
?>

code for the main file in which you want to populate drop down based on other in below example i show localities in child dropdown for that city that is selected in parent drop down

    <script type="text/javascript">
    function city_locality(val)
    {
    // alert (val);
    url="<?php echo $this->baseurl ?>/components/com_ezportal/includes/query.php";
    data="stid="+val;
$.post(url,data,function(data){
$("#locid").html(data);
});     
    }
    </script>
    <select name="filter_1state" id="filter_1state" onChange="city_locality(this.value)">
      <option value="0">-- Select City --</option>
      <option value="1">Lahore</option>
      <option value="2">Karachi</option>
      <option value="3">Islamabad</option>
      <option value="4">Quetta</option>
      <option value="5">Multan</option>
    </select>

code for the file query.php

    <?php 
    $link = mysql_connect('localhost', 'root', '');
    mysql_select_db('mydatabase');
    if (isset($_POST['stid']))
    {
        $stid = $_POST['stid'];
        $query= mysql_query("SELECT id,ezcity FROM tbl_locality WHERE stateid = '".$stid."' GROUP BY ezcity"); 
    ?>
        <option value="0">-- Select Locality --</option>
    <?php
        while($row = mysql_fetch_array($query))
        {
    ?>
        <option value="<?php echo $row['id']?>"><?php echo $row['ezcity']?>        </option> 
        <?php
        }
    }
    ?>

If you want a more dynamic solution (that will accommodate changes to the background DB) you can do something like this on your page:

<script>
        function changeSecond(first){
        var xmlhttp;
        if (window.XMLHttpRequest)
          {// code for IE7+, Firefox, Chrome, Opera, Safari
          xmlhttp=new XMLHttpRequest();
          }
        else
          {// code for IE6, IE5
          xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
          }
        xmlhttp.onreadystatechange=function()
        {
          if (xmlhttp.readyState==4 && xmlhttp.status==200)
            {
            var res=xmlhttp.responseText;
            document.getElementById("second").innerHTML=res;
            }
          }
        xmlhttp.open("GET","second_script.php?first="+first,true);
        xmlhttp.send();
        }
        </script>
...
<select onChange="changeSecond(this.value)">
<option value="Degree">Degree</option>
<option value="City">City</option>
</select>
<div id="second"><select><option value=""></option></select></div>

and then a script similar to:

<?php
//database connection
$first=mysql_real_escape_string($_REQUEST["first"]);
$query="SELECT ".$first." FROM tablename GROUP BY ".$first;
$data=mysql_query($query);
echo "<select>";
while($row=mysql_fetch_row($data)){
echo "<option value=\"".$row[0]."\">".$row[0]."</option>";
}
echo "</select>";
?>

I guess for real flexibility you'd also want to dynamically populate that first one using mysql_field_name in another script similar to above

You could either have all the dropdown needed preloaded and hidden, and show them when the 'change' event is triggered in the first dropdown, or have two dropdowns and empty it on that same 'change' event.

If you choose the second approach you should buffer the data to insert in the dropdown list, partially or totally

You can use ajax for this purpose

eg1.html

    <html>
    <head><title></title>
    </head>
    <body>
    <form method="POST" action="" name="mainForm">
    <table>
        <tr>
            <td> Filter by: </td>
            <td>
                <div id="first">
                   <select id="first_dropdown">
                      <option value="All">All</option>
                      <option value="Degree">Degree</option>
                      <option value="City">City</option>
                   </select>
            </td>
         </tr>
         <tr>
            <td>&nbsp</td>
            <td>
               <div id="second">
                  <select id="second_dropdown">

                  </select>
               </div>
            </td>
         </tr>
      </table>
   </form>
</body>
</html>

    <script src = "https://code.jquery.com/jquery-3.1.0.min.js"></script>
    <script type="text/javascript">
    $(document).ready(function(){
        $('#first_dropdown').change(function(){
            $.ajax({
                'type' : 'post',
                'url' : 'getDropdownOptions.php',
                'data': 'first=' + $(this).val(),
                'success' : function(data) {
                    $('#second_dropdown').html(data);
                }
             });
          });
        });
    </script>

getDropdownOptions.php

    <?php

    $link = mysql_connect("localhost", "root", "");

    if (!$link) {
        die('Could not connect: ' . mysql_error());
    }
    if (mysql_select_db("myDatabase", $link)) {
        $first = mysql_real_escape_string($_REQUEST["first"]);
        $query = "SELECT " . $first . " FROM myTable GROUP BY " . $first;
        $data  = mysql_query($query);

        $rtn_data = '';
        while ($row = mysql_fetch_row($data)) {
            $rtn_data .= "<option value=\"" . $row[0] . "\">" . $row[0] . "        </option>";
        }
        echo $rtn_data;
        exit;
    }
    echo mysql_error();

    ?>

Divyesh here, your answer is
==============================
edit.php
==============================
<!-- edit.php -->
<?php
include("config.php");
$id=$_REQUEST['id'];
echo $id;
$query=mysqli_query($con,"SELECT * FROM register r
INNER JOIN country c ON r.cuid = c.cuid
INNER JOIN state s ON r.sid = s.sid
INNER JOIN city ct ON r.cid = ct.cid where id='$id'");

while($r=mysqli_fetch_array($query))
{

     $fn=$r['firstname'];
     $add=$r['address'];
     $gn=$r['gender'];
     $hobby=$r['hobby'];
     $h=explode(',',$hobby);
     $q=array('reading','traveling','cricket','drawing');
     $country=$r['cuid'];
     $state=$r['sid'];
     $city=$r['cid'];
     echo $gn;
     $edu= $r['education'];
     $email=$r['email'];
     $pass=$r['password'];
     $conpass=$r['conpassword'];
     $phno=$r['phoneno'];
}

?>
<html>
<head>
    <script src="jquery.js"></script>
<script type="text/javascript">
$(document).ready(function(){

    $('#country').on('change',function(){
        var countryID = $(this).val();
        if(countryID){
            $.ajax({
                type:'POST',
                url:'ajaxData.php',
                data:'cuid='+countryID,
                success:function(html){
                    $('#state').html(html);
                    $('#city').html(html); 
                }
            }); 
        }else{
            $('#state').html(html);
            $('#city').html(html); 
        }
    });

    $('#state').on('change',function(){
        var stateID = $(this).val();
        if(stateID){
            $.ajax({
                type:'POST',
                url:'ajaxData.php',
                data:'sid='+stateID,
                success:function(html){
                    $('#city').html(html);
                }
            }); 
        }else{
            $('#city').html(html); 
        }
    });
});
</script>
</head>
    <body>
        <form method="post" action="update.php">
            <table border="1" align="center">
            <caption>Edit user data</caption>
            <tr>
                <td>First name</td>
                <td><input type="text" name="fn" value="<?php echo $fn;?>"></td>
            </tr>
            <tr>
                <td>Address</td>
                <td><input type="text" name="add" value="<?php echo $add;?>"></td>
            </tr>
            <tr>
                <td>Gender</td>
                <td><input type="radio" name="gn" value="male" <?php echo ($gn=='male')?'checked':'' ; ?> size="17">Male
                <input type="radio" name="gn" value="female" <?php echo ($gn=='female')?'checked':'' ; ?> size="17">Female
                </td>
            </tr>
            <tr>
                <td>Hobby</td>
                <td><input type="checkbox" name="hobby[]" value="reading" <?php if(in_array('reading',$h)){echo ("checked:'checked'");}?> >reading
                <input type="checkbox" name="hobby[]" value="traveling" <?php if(in_array('traveling',$h)){echo ("checked:'checked'");}?> >traveling
                <input type="checkbox" name="hobby[]" value="cricket" <?php if(in_array('cricket',$h)){echo ("checked:'checked'");}?> >cricket
                <input type="checkbox" name="hobby[]" value="drawing" <?php if(in_array('drawing',$h)){echo ("checked:'checked'");}?> >drawing</td>
            </tr>
            <?php
            $query = mysqli_query($con,"SELECT * FROM country");

            //Count total number of rows
            $rowCount = mysqli_num_rows($query);
            ?>
            <td>Country</td>
            <td><select name="country" id="country">
                <option value="<?php echo $country;?>"><?php echo $country;?></option>
                <?php
                if($rowCount > 0)
                {
                    while($row = mysqli_fetch_array($query))
                    { 
                        echo '<option value="'.$row['cuid'].'">'.$row['country'].'</option>';
                    }
                }
                else
                {
                    echo '<option value="">Country not available</option>';
                }
                ?>
            </select>
            </td></tr>
            <tr>
            <td>State</td>
            <td>
            <select name="state" id="state">
                <option value="<?php echo $state;?>"><?php echo $state;?></option>

            </select>
            </td></tr>
            <tr>
            <td>City</td>
            <td>
            <select name="city" id="city">
                <option value="<?php echo $city;?>"><?php echo $city;?></option>

            </select>
            </td>
            </tr>
            <tr>
                <td>Education</td>
                <td><input type="text" name="edu" value="<?php echo $edu;?>"></td>
            </tr>
                <td>Email</td>
                <td><input type="text" name="email" value="<?php echo $email;?>"></td>
            </tr>
            <tr>
                <td>Password</td>
                <td><input type="text" name="pass" value="<?php echo $pass;?>"></td>
            </tr>
            <tr>
                <td>Confirm password</td>
                <td><input type="text" name="conpass" value="<?php echo $conpass;?>"></td>
            </tr>
            <tr>
                <td>Phone no</td>
                <td><input type="text" name="phno" value="<?php echo $phno;?>"></td>
            </tr>
            <tr>
                <td><input type="hidden" name="id" value="<?php echo $id;?>">
                <input type="submit" name="update" value="update"></td>
            </tr>
            </table>
        </form>
    </body>
</html>

================
<h3>ajaxData.php</h3>
================
<!--ajaxData.php-->
<?php
//Include database configuration file
include("config.php");

if(isset($_POST["cuid"]) && !empty($_POST["cuid"]))
{
    //Get all state data
    $query = mysqli_query($con,"SELECT * FROM state WHERE cuid = ".$_POST['cuid']."");

    //Count total number of rows
    $rowCount = mysqli_num_rows($query);

    //Display states list
    if($rowCount > 0)
    {
        echo '<option value="">Select state</option>';
        while($row =  mysqli_fetch_array($query))
        { 
            echo '<option value="'.$row['sid'].'">'.$row['state'].'</option>';
        }
    }
    else
    {
        echo '<option value="">State not available</option>';
    }
}

if(isset($_POST["sid"]) && !empty($_POST["sid"]))
{
    //Get all city data
    $query = mysqli_query($con,"SELECT * FROM city WHERE sid =".$_POST['sid']."");

    //Count total number of rows
    $rowCount = mysqli_num_rows($query);

    //Display cities list
    if($rowCount > 0)
    {
        echo '<option value="">Select city</option>';
        while($row = mysqli_fetch_array($query))
        { 
            echo '<option value="'.$row['cid'].'">'.$row['city'].'</option>';
        }
    }
    else
    {
        echo '<option value="">City not available</option>';
    }
}
?>

继续阅读：php

Populating dropdown - PHP Ajax MySQL

eg1.html

getDropdownOptions.php

更多精彩内容

精彩评论

最新问答

央视是哪个频道？

请问买过的朋友，舒提啦旅行箱实际使用体验如何？？

检查不孕不育需要的费用？

海信ULED电视画质有什么不同的地方?？

钉子可以挂的住画框幕布吗？

问答排行榜

王昌瑞《潜梦追凶》剧组庆生新锐演员未来可期？

Is it allowed to ask users to enter credit card details for own payment method?

Escaping "<" in Perl-generated XML

imessage会显示已读吗？

微信重新建群怎么建？

eg1.html

getDropdownOptions.php

更多精彩内容

精彩评论

最新问答

央视是哪个频道？

请问买过的朋友，舒提啦旅行箱实际使用体验如何？？

检查不孕不育需要的费用？

海信ULED电视画质有什么不同的地方?？

钉子可以挂的住画框幕布吗？

问答排行榜

王昌瑞《潜梦追凶》剧组庆生 新锐演员未来可期？

Is it allowed to ask users to enter credit card details for own payment method?

Escaping "<" in Perl-generated XML

imessage会显示已读吗？

微信重新建群怎么建？

王昌瑞《潜梦追凶》剧组庆生新锐演员未来可期？