Auto populate text fields based on dropdown selection where all values come from a MYSQL table

1.8k views Asked by At

I'm trying to auto populate some text fields (rate1 and rate2) based on a dropdown selection (instid and instfirstname). The values are all in one mysql table called tbl_insts. I'm trying the solution found in How to Get Content in text field dynamically, based on dropdown selection in php, but can't get it to work. Does anyone have any suggestions? Thanks in advance.

This is my mysql table called tbl_insts

instid instfirstname rate1 rate2

1       john              50       45

2       eric               25       45


This is my html form. I'm able to populate the dropdown correctly but not the text fields.

<select name="instfilter" id="instfilter">
<?php
    if ($stmt = $conn->prepare("select instid, instfirstname from tbl_insts order by instid")) {
    $stmt->execute();
    $stmt->bind_result($instid, $instfirstname);
    echo '<option value="-1" selected="selected">Please select...</option>';
    while ($stmt->fetch()) {
        echo '<option value="'.$instid.'">'.$instfirstname.'</option>';
    }
    $stmt->close();
}
?>
</select>

<!-- Fields that I want to populate based on the selection on top -->
<input type="text" name="rate1" id="rate1" /> 
<input type="text" name="rate2" id="rate2" />

This is my code before the tag

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js">
</script>
<script>
$('#instfilter').change(function(){
var inst = $(this).val();
$.ajax({
type:'POST',
data:{inst:inst},
url:'getrates.php',
success:function(data){
   $('#rate1').val(data);
   $('#rate2').val(data);
} 
});
});
</script>

This is my code in getrates.php in the same directory as the html file above.

<?php 
if (isset($_POST['inst'])) {
    $qry = "select rate1, rate2 from tbl_insts where instid = ".
$_POST['inst'];
    $rec = mysql_query($qry);
    if (mysql_num_rows($rec) > 0) {
        while ($res = mysql_fetch_array($rec)) {
            echo $res['rate1'];
            echo $res['rate2'];
        }
    }
}
die();
?>
2

There are 2 answers

1
Ovis On BEST ANSWER
  1. try change data:{inst:inst} to data:{'inst':inst}
  2. now you returning a string from getrates.php. From you code structure, you can do like this:

    if (mysql_num_rows($rec) > 0) {
        while ($res = mysql_fetch_array($rec)) {
            echo $res['rate1']."|".$res['rate2'];
        }
    }
    

..and

success:function(data){
 var inputs = data.split('|');
   $('#rate1').val(inputs[0]);
   $('#rate2').val(inputs[1]);
} 

hope this helps.

1
Tom On

Untested, but I think these changes should do the job.

while ($res = mysql_fetch_array($rec)) {
    $result = [
        'rate1' => $res['rate1'],
        'rate2' => $res['rate2']
    ];
}
.......
die(json_encode($result));

and

success: function(data){
    data = $.parseJSON(data);
    $('#rate1').val(data.rate1);
    $('#rate2').val(data.rate2);
}