Click here to Skip to main content
14,876,938 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi guys,
Today I was trying this Dynamic listbox using PHP and SQL. I have searched the internet regarding my issue but couldn't find any solutions. Can someone help me with this?

I have a form having Employee nos. in a column as a dropdown list and another column which contains the names of those Employees. I wanted the second listbox to automatically point to the name of the Employee when I select their number.


I have seen some tutorials achieiving this with jquery and Ajax. I have tried their methods. But I couldn't get the results. Then I got a question, whether we can achieve it with the SQL Statement with where clause. But I don't know how to proceed, since I'm new to PHP. Can anyone tell me whether we can do it with SQL statement alone?

If not it is done with only Jquery or Ajax please tell me where I did wrong.

Thanks in advance.

What I have tried:

Here is my code:

HTML:
<label for = "empid">
     <div  id="form-control"> EMPLOYEE NO.:</div>
     <select name="empid" id="empid" >
     <option value=""></option>
      <?php
       $sql = mysqli_query($con,"SELECT * FROM emp_mas");
       while($row=mysqli_fetch_array($sql))
       {
       echo '<option value="'.$row['id'].'">'.$row['empid'].'</option>';
       } ?>
      </select> </label>

       <label for = "e_name">
       <div id="form-control"> EMPLOYEE NAME:</div>
         <SELECT name="e_name">

         </SELECT></label><br>



SCRIPT:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js">
</script>
<script type="text/javascript">
$(document).ready(function()
{
$(".empid").change(function()
{
var id=$(this).val();
var post_id = 'id='+ id;
 
$.ajax
({
type: "POST",
url: "ajax.php",
data: post_id,
cache: false,
success: function(e_name)
{
$(".e_name").html(e_name);
} 
});
 
});
});
</script>



AJAX.php
<?php
include('connection.php');
if($_POST['id']){
$id=$_POST['id'];
if($id==0){
 echo "<option></option>";
}else{
 $sql = mysqli_query($con,"SELECT * FROM emp_mas WHERE id='$id'");
 while($row = mysqli_fetch_array($sql)){
 echo '<option value="'.$row['id'].'">'.$row['e_name'].'</option>';
 }
 }
}
?>
Posted
Updated 10-Apr-21 2:29am

First, from what I can see, your design (data content) for the drop-lists is really weird. Nonetheless, will work with it.

Let's think about how the data is logically linked: the employee number is their common value. Thus, the way you can transfer knowledge between the lists is by using this employee number.

When you select an <option> from a <select> the value of the element is that of the value of the option. Thus, your first list should not use the record number as its value but the employee number (same value for for value= and the visible part. You need to fix this.

Now, the next thing to do is to add an onchange= event to the first list so that when you select a value it will do something with it. You'll need a function for this and possible a good way to do this is:
JavaScript
<select onchange='getName(value)'>


Now your second list can exist as it is except it needs an id= attribute. Let's use the same one as the name= so we'll add id='e_name' . This is so we can directly access this element on the page. ID's are very important and useful.

Now for your function:
JavaScript
function getName(val) {
  document.getElementById('e_name').value = val;
}


That will thus update the second list.

If you load the list when your page is first generated there's no need for AJAX: all the data you need is available on the page.



   
v3
Comments
Bhavanisankar V 9-Apr-21 9:14am
   
Hi, @balboos.
First of all, thanks for your reply. I have tried your solution, but I think I am missing something. Then onchange event is not happening even after adding the javascript and onchange property. Can you please tell me is there anything to be added.
Thank you.
W Balboos, GHB 9-Apr-21 9:36am
   
The best option is for you to make a very simple page to work out what you're missing. Create two lists - only a few hand-coded entries each - where the values for both lists correspond. Add the onchange event.

If it works then you can look for error in your original - if it doesn't work on it until it does work and then update your original.

ALSO: in the function for the onchange event, put in an alert(val), especially if it doesn't work, to see what is actually being sent to the function. Also lets you know if it even gets to your function. You can even try this in your original version. Just have the alert() and comment out the document.getElement...().value.
Bhavanisankar V 9-Apr-21 10:10am
   
Ok thanks, will surely work on it.
Bhavanisankar V 9-Apr-21 10:14am
   
And finally I have an doubt. In second entry do I need to insert the SQL statement with where clause. If not, then which to write in it. If so, then how to write in the where clause, is it like '....where val;'. Is this correct?
Bhavanisankar V 10-Apr-21 8:21am
   
Hi @balboos,
Finally solved it using javascript. Thanks for your guide.
Here is the code I have used:

function getId(val){
            //We create ajax function
            $.ajax({
                type: "POST",
                url: "data.php",
                data: "emp_id="+val,
                success: function(data){
                    $("#e_name").html(data);
                   
                }
            });
        }
W Balboos, GHB 11-Apr-21 17:32pm
   
First, that's not quite javaScript - it's jQuery (which I do not use).

However, it's great that you worked it out. You get two lessons out of it. One is the techniques that worked are now deeply learned. The other is the way to test you code to find out where it goes wrong (if you don't have a debugger then you can use alert() in javaScript and similar things in other languages).

Now - one thing to remember (here and in the future): if you get a satisfactory answer in Q&A then mark it accepted so that the question is marked "solved" and helpers can put their help into new questions.
And finally solved it with @Balboos idea of using javascript.

Code:
              <label for = "emp_id">
              <div id="form-control"> EMPLOYEE ID:</div></label>
            <SELECT name="emp_id" id="emp_id" onchange="getId(this.value)">
            <option value=""></option>
              <?php
              $query = "select * From con_mas order by emp_id";
              $results = mysqli_query($con, $query);
               foreach ($results as $ecd){
              ?>
                      <option value="<?php echo $ecd["emp_id"];?>"><?php echo
                              $ecd["emp_id"];?></option>
              <?php
                  }
              ?>
             </SELECT>

              <label for = "e_name">
               <div id="form-control"> EMPLOYEE NAME:</div></label>
            <SELECT name="e_name" id="e_name">
            <option value=""></option>
             </SELECT>



    function getId(val){
    //We create ajax function
    $.ajax({
        type: "POST",
        url: "data.php",
        data: "emp_id="+val,
        success: function(data){
            $("#e_name").html(data);

        }
    });
}


data.php:

<?php
    include_once "connection.php";
    if (!empty($_POST["emp_id"])) {
        $eid = $_POST["emp_id"]; 
        $query="SELECT * FROM con_mas WHERE emp_id='$eid'";
        $results = mysqli_query($con, $query);
        
        foreach ($results as $ename){
?>
            
            <option value="<?php echo $ename["e_name"];?>"><?php echo $ename["e_name"];?>
    </option>       
<?php
        }
  
    }
    
?>  
   
Comments
W Balboos, GHB 11-Apr-21 17:36pm
   
Glad you solved it - for future reference:
If you get a solution that works the mark it "accepted" to close the question.
I understand your confusion. The "Solution" area is for solutions offered to the problems and not posting your solution to your own question (even though it seems to make sense). If you wish to post it, you can Edit your original, or, more commonly, put it in the comment thread of the solution that worked.

No harm done - but do remember to mark the Solution (Solution 1) as "accepted" to close the question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900