How do I autofill textbox values from mysql query result onChange of dropdown?

Question

I would like customer_address, customer_city, customer_state and customer_zip to autofill onChange of customer_name dropdown. I've looked around, but I'm stumped.

Please help.

<form action="" method="post" name="booking_form" class="booking" style="width: auto">
<select name="customer_name" onChange="???????">
<option value="index.php">Click to select your school </option>
<?php

// Make a MySQL Connection

$username="";

$password="";

$database="";

mysql_connect('localhost',$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT * FROM table"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){

$id = $row['id'];
$school = $row['customer_name'];
$phone = $row['customer_phone'];
$address = $row['customer_address'];
$city = $row['customer_city'];
$state = $row['customer_state'];
$zip = $row['customer_zip'];    
$notes = $row['customer_notes'];
?>
<option value="<?php echo $school; ?>"><?php echo "$school".", "."$city"; ?></option>
<?php } 

?></select>
<input type="text" name="customer_address">
<input type="text" name="customer_city">
<input type="text" name="customer_state">
<input type="text" name="customer_zip">
</form>

Answer 1

You can generate an javascript object containing all sql results like this:

var schoolInfo = { id1 : {name:"Name 1", address:"address line" ...}, 
id2: {} .... }

Then set each to include customized attribute 'sid':

<option value="school" sid="id1">school, city</option>

For element's onchange event handler using jQuery:

.change(function(){
     var sid = $(this).attr('sid');
     $("input[name='customer_address']").val(schoolInfo[sid].address);
     $("input[name='customer_city']").val(schoolInfo[sid].city);
     $("input[name='customer_zip']").val(schoolInfo[sid].zip);
});

Answer 2

A few things since my original post...mysql_ is no longer safe. Also it's not good to have everything in one file, best to separate the sql & business logic from your view, lookup MVC pattern. However, here is the quick & dirty method: you can see end result here: http://jsfiddle.net/webaholik/kcce6Ls9/

<?php
$sql = "SELECT id, 
    customer_name AS name, 
    customer_phone AS phone, 
    customer_address AS address, 
    customer_city AS city, 
    customer_state AS state, 
    customer_zip AS zip, 
    customer_notes AS notes 
    FROM table";
$schoolInfo = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

$array = array();
foreach ($schoolInfo as $item) {
    $array[$item['id']] = $item;
}
$json = 'var SchoolInfo = ' . json_encode($array) . ';';
?>
<form action="" method="post" name="booking_form" class="booking"
style="width: auto">
<select id="customer_name" name="customer_name">
    <option value="index.php">Click to select your school</option>
<?php foreach($schoolInfo as $row){?>
    <option value="<?php echo $row['id']; ?>"><?php echo $row['name'].", ".$row['city']; ?></option>
<?php } ?>
</select>
<input type="text" name="customer_address">
<input type="text" name="customer_city">
<input type="text" name="customer_state">
<input type="text" name="customer_zip">
</form>
<script>
<?php echo $json;?>

$("#customer_name").change(function(){
    var sid = $(this).val();
    $("input[name='customer_address']").val(SchoolInfo[sid].address);
    $("input[name='customer_city']").val(SchoolInfo[sid].city);
    $("input[name='customer_zip']").val(SchoolInfo[sid].zip);
});
</script>

Answer 3

Try to post a php script and make connection with Mysql then return values.

<select name="customer_name">
<option id='selects' value="index.php">Click to select your school </option>

Javascript

       $("select[name='customer_name']").change(function(){
            $.post("-YOUR PHP FILE-", customer_name: $(this).val(), function(data){
               schools = data.split(",");
               for(var i in schools){
               $("select[name='customer_name']").append("<option>"+schools[i]+"</option>");
               }
            })
       })

PHP

       <?php
          //You connected and got an array contains your school names($schools)
          $schools = implode(",",$schools); 
          echo $schools;
       ?>

I think you are trying to do something like that.