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

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

dannyg280Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dannyg280Author 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...
0
zephyr_hex (Megan)DeveloperCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dannyg280Author 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..
0
Php_expertCommented:
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!');
        }
    });
      
});
0
dannyg280Author 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();
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.