That’s much better. Thanks!
So I did this:
CREATE TABLE Meeting_Places(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
MeetingPlace varchar(256),
StreetAddress varchar(256),
City varchar(256),
State varchar(256),
ZipCode smallint(6)
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;
Then:
INSERT INTO meeting_places SET
MeetingPlace = "Darcy's Restaurant and Spirits",
StreetAddress = "10502 E Sprague Ave",
City = "Spokane Valley",
State = "WA",
ZipCode = "99206"
INSERT INTO meeting_places SET
MeetingPlace = "Shari's of Spokane Valley",
StreetAddress = "320 N Sullivan Rd",
City = "Veradale",
State = "WA",
ZipCode = "99037"
INSERT INTO meeting_places
SET MeetingPlace = "Elmers Restaurant",
StreetAddress = "290 W Appleway Ave",
City = "Coeur d'Alene",
State = "ID",
ZipCode = "83814"
Now we should be on the same page.
Before we start, I want to point out that it is not good practice to use PHP’s old mysql API to connect to your databases.
You should really migrate to PDO.
See here for more info.
That out of the way, I tidied up your PHP a little. In the code you posted above you were selecting everything twice from the DB. There is no need.
This gives us what we need and outputs a list of meeting places accordingly.:
<?php
# Connect
mysql_connect('localhost', 'user', 'pass') or die('Could not connect: ' . mysql_error());
# Choose a database
mysql_select_db('sitepoint') or die('Could not select database');
# Perform database query
$query = "SELECT * from meeting_places";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8">
<title>Select - onchange AJAX example</title>
</head>
<body>
<label for="meetingPlace">Meeting place: </label>
<select id="meetingPlace">
<option value="0">Please select</option>
<?php
while ($row = mysql_fetch_assoc($result)) {
echo '<option value="' . $row['id'] . '">' . $row['MeetingPlace'] . '</option>';
}
?>
</select>
<div>
<label for="element_5_1">Street Address</label>
<input id="element_5_1" name="element_5_1" class="element text large" type="text">
</div>
<div>
<span class="floatLeft">
<label for="element_5_3">City</label>
<input id="element_5_3" name="element_5_3" class="element text medium" style="width:14em" type="text">
</span>
<span style="float:left">
<label for="element_5_4">State</label>
<input id="element_5_4" name="element_5_4" class="element text medium" style="width:4em" type="text">
</select>
</span>
<span style="float:left">
<label for="element_5_5">Zip Code</label>
<input id="element_5_5" name="element_5_5" class="element text medium" style="width:6em" type="text">
</span>
</div>
</body>
</html>
Now we need to hook into the onchange event of the select element:
$("#meetingPlace").on("change", function(){
var id = $(this).val();
if (id === "0"){
clearForm();
} else {
makeAjaxRequest(id);
}
});
I have given all of the option elements a value. If the value is 0, the user has selected the default and we want to clear the remaining fields.
If the value is something else, we want to fire our AJAX request, passing the function the id of the place that has just been selected.
On to the AJAX:
function makeAjaxRequest(placeId){
$.ajax({
type: "POST",
data: { placeId: placeId },
dataType: "json",
url: "process_ajax.php",
success: function(json) {
insertResults(json);
}
});
}
Nothing new here, except that we are specifying dataType: “json”, as we want json back from the server.
And finally the success callback and the function to clear the fields:
function insertResults(json){
$("#element_5_1").val(json["StreetAddress"]);
$("#element_5_3").val(json["City"]);
$("#element_5_4").val(json["State"]);
$("#element_5_5").val(json["ZipCode"]);
}
function clearForm(){
$("#element_5_1, #element_5_3, #element_5_4, #element_5_5").val("");
}
Nothing exciting here, either. Our server will return a json object representing the database record (as we will see in a minute) and we are setting those values as the values of the input elements.
In the clearForm() function, we are simply setting the values of all inputs to that of an empty string.
And on the serever:
<?php
mysql_connect('localhost', 'user', 'pass') or die('Could not connect: ' . mysql_error());
mysql_select_db('sitepoint') or die('Could not select database');
$placeId = $_POST['placeId'];
$query = "SELECT * from meeting_places";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
if ($placeId == $row['id']){
echo json_encode($row);
}
}
?>
We are retrieving the id of the place selected in the select element from $_POST, then querying the database for all records.
When we have the records, we are looping through them to find the one which matches the place id, encoding it as json and returning it to our script.
Note: you could do this more efficiently if you sanitise the input from the JS and use it directly. This is where PDO and prepared statements shine.
And finally here’s the complete code.
index.php:
<?php
# Connect
mysql_connect('localhost', 'user', 'pass') or die('Could not connect: ' . mysql_error());
# Choose a database
mysql_select_db('sitepoint') or die('Could not select database');
# Perform database query
$query = "SELECT * from meeting_places";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8">
<title>Select - onchange AJAX example</title>
</head>
<body>
<label for="meetingPlace">Meeting place: </label>
<select id="meetingPlace">
<option value="0">Please select</option>
<?php
while ($row = mysql_fetch_assoc($result)) {
echo '<option value="' . $row['id'] . '">' . $row['MeetingPlace'] . '</option>';
}
?>
</select>
<div>
<label for="element_5_1">Street Address</label>
<input id="element_5_1" name="element_5_1" class="element text large" type="text">
</div>
<div>
<span class="floatLeft">
<label for="element_5_3">City</label>
<input id="element_5_3" name="element_5_3" class="element text medium" style="width:14em" type="text">
</span>
<span style="float:left">
<label for="element_5_4">State</label>
<input id="element_5_4" name="element_5_4" class="element text medium" style="width:4em" type="text">
</select>
</span>
<span style="float:left">
<label for="element_5_5">Zip Code</label>
<input id="element_5_5" name="element_5_5" class="element text medium" style="width:6em" type="text">
</span>
</div>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script>
function insertResults(json){
$("#element_5_1").val(json["StreetAddress"]);
$("#element_5_3").val(json["City"]);
$("#element_5_4").val(json["State"]);
$("#element_5_5").val(json["ZipCode"]);
}
function clearForm(){
$("#element_5_1, #element_5_3, #element_5_4, #element_5_5").val("");
}
function makeAjaxRequest(placeId){
$.ajax({
type: "POST",
data: { placeId: placeId },
dataType: "json",
url: "process_ajax.php",
success: function(json) {
insertResults(json);
}
});
}
$("#meetingPlace").on("change", function(){
var id = $(this).val();
if (id === "0"){
clearForm();
} else {
makeAjaxRequest(id);
}
});
</script>
</body>
</html>
process_ajax.php:
<?php
mysql_connect('localhost', 'user', 'pass') or die('Could not connect: ' . mysql_error());
mysql_select_db('sitepoint') or die('Could not select database');
$placeId = $_POST['placeId'];
$query = "SELECT * from meeting_places";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
if ($placeId == $row['id']){
echo json_encode($row);
}
}
?>
I hope that helps. Let me know how you get on 