Using AJAX to Auto-Populate Jquery Form Fields On Dropdown Select Using PHP/MySQL Query Result

dannyg280
dannyg280 used Ask the Experts™
on
In the form below a user selects a repair appointment. They first select the Appointment Type, and then select if it is an new appointment, or an existing appointment. If they select it is an existing appointment then the "existing_appt" div becomes visible which lists the exiting appointments to choose from.

There are input boxes below for the Customer, First Name, Last Name and Email Address.
What I need to happen is when the select an Existing Appointment from the "appt_id" select field it will fire an AJAX request with the value from that field to a PHP file which will query the database to get the Customer, First Name, Last Name and Email Address. I then need it to read the response and populate the input fields with that data. I'm not familiar with how to do the AJAX request and how to process the results. Can someone help with this part?


<form action="php/sendemail.php" method="post" id="emailform" data-ajax="false">
     
		 <select id="appt_type" name="appt_type">
        <option value="None Specified" selected="selected">Select Appointment Type</option>
        <option value="OnSite">On-Site</option>
        <option value="InShop">In-Shop</option>
      </select>
		 <label for="appt_stage">Appointment Stage:</label>
		<select id="appt_stage" name="appt_stage">
			<option value ="0">Please Select Stage</option>
			 <option value="1">Setup New Appoinment</option>
			 <option value="2">Stage 2</option>
			 <option value="3">Stage 3</option>
      </select>
		<div style="display: none" id="existing_appt">
		<select id="appt_id" name="appt_id">
			<option value="0">Select Existing Appointment</option>
		 <option value='10'>Mar 14 12:49PM 2018 - John Smith</option><option value='13'>Mar 15 8:51AM 2018 - John Smith</option>		
		
		</select>
		</div>
		
    <label for="cnametxtbx">Customer:</label>
    <input style="position:absolute right: 320px" type="text" id="cnametxtbx" name="cname" value="" >
    <p>
    <label for="fnametxtbx">First Name:</label>
    <input style="position:absolute left: 320px" type="text" id="fnametxtbx" name="fname" value="">
    <p>
    <label for="lnametxtbx">Last Name:</label>
    <input style="position:absolute left: 320px" type="text" id="lnametxtbx" name="lname" value="">
    <p>
      <label for="emailtxtbx">Email Address:</label>
      <input style="position:absolute left: 320px" type="text" id="emailtxtbx" name="email" value="">
		<p>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
The JS code I have so far is:
$("#appt_id").change(function () {
var aid = $(this).val;
	   $.ajax({
        type: "GET",
        dataType: "json",
        url: "php/getappt.php",
        data: {aid: aid},
        success: function(data) {
           	$('#cnametxtbx').val(data["custname"]);
			$('#fnametxtbx').val(data["firstname"]);
			$('#lnametxtbx').val(data["lastname"]);
			$("#emailtxtbx").val(data["email"]);
        },
        error : function(){
           alert('Some error occurred!');
        }
    });
	
});

Open in new window


and my getappt.php file looks like this at the momment:
$aid = $_GET['aid'];
include("db.php");
 $mysqli = new mysqli("$host", "$username", "$password", "$db_name");
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();

}
else {




$result=$mysqli->query("Select
							  ApptID as apptid
							, CustomerName as customer
							, FirstName as firstname
							, LastName as lastname
							, Email as email
							
							FROM appointments
 where ApptID = '$aid'");

Open in new window


Just not sure what to do with the results from the query to make sure they are returned back correctly...
Top Expert 2010
Commented:
you'll want to echo the result from your PHP page so that it is send back to the JS.  Also, you've specified that the result will be json but it's not.  So, you will want to json encode the result and send the appropriate json header.

however... I also wanted to point out that the way in which you're querying your database is open to SQL Injection.

https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

Author

Commented:
Thank you. I'm not sure of the correct way to echo the results back so they are received back properly...

I tried this:
$aid = $_GET['aid'];
include("../db.php");
 $mysqli = new mysqli("$host", "$username", "$password", "$db_name");
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();

}
else {




$result=$mysqli->query("Select
							  ApptID as apptid
							, CustomerName as customer
							, FirstName as firstname
							, LastName as lastname
							, Email as email
							
							FROM appointments a
							join vCustomers c on a.CustID = c .CustID
 where ApptID = '$aid'");

    while($row=$result->fetch_array())
    {
		
		
		 $data = array(
            "custname"     => $row['customer'],
            "firstname"  => $row['firstname'],
            "lastname"   => $row['lastname'],
            "email"      => $row['email'],
        );
      
    }
    echo json_encode($data);     
	
		
        
		
    }
?>

Open in new window


But it appears I am getting an empty result back..
Hi,

You have return data is correct way but make sure you do not have empty data while return as below.

echo json_encode($data);

Also please correct the data access from response as below

$("#appt_id").change(function () {
var aid = $(this).val;
         $.ajax({
        type: "GET",
        dataType: "json",
        url: "php/getappt.php",
        data: {aid: aid},
        success: function(data) {
                 $('#cnametxtbx').val(data.custname);
                  $('#fnametxtbx').val(data.firstname);
                  $('#lnametxtbx').val(data.lastname);
                  $("#emailtxtbx").val(data.email);
        },
        error : function(){
           alert('Some error occurred!');
        }
    });
      
});

Author

Commented:
Thank you. My empty results were due to a boneheaded mistake on my AJAX call when retrieving the value of the dropdown... I forgot the (). $(this).val; should have been $(this).val();

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial