How to get data from database and assign it to inputs values using PHP and jQuery AJAX

Question

In my little application I have owner table in my database. Every owner has his ID, name, address and description (those are columns of this table).

On my website I have select element where you can choose owners.

Under this select I have 3 inputs (one for name, one for address and one for description).

When I change the option in my select element I would like to fill all inputs with the data from my database using AJAX.

My PHP:

<?php
  $sql_select=$mysqli->prepare("SELECT id, name FROM owner ORDER BY id DESC");
  $sql_select->execute();
  $sql_select->store_result();
  $sql_select->bind_result($id_owner, $name_owner);
  $select = $_POST['owner'];
  echo "<option value=0 ".selected.">Select...</option>";
  while($sql_select->fetch()) {
    if($select == $id_owner){
      $selected="selected";
    }
    else {
      $selected="";
    }
    echo "<option value=".$id_owner." ".$selected." data-id=".$id_owner.">".$id_owner." ".$name_owner."</option>";
  }
?>
<label for="owner_name">Owner name</label>
<input id="owner_name" name="owner_name" type="text">
<label for="owner_address">Owner address</label>
<input id="owner_address" name="owner_address" type="text">
<label for="owner_description">Owner description</label>
<input id="owner_description" name="owner_description" type="text">

Rendered HTML:

<select id="owner" name="owner">
  <option value="0" selected>Select...</option>
  <option value="1" data-id="1">1 ABC</option>
  <option value="2" data-id="2">2 DEF</option>
  <option value="3" data-id="3">3 GHI</option>
</select>
<label for="owner_name">Owner name</label>
<input id="owner_name" name="owner_name" type="text">
<label for="owner_address">Owner address</label>
<input id="owner_address" name="owner_address" type="text">
<label for="owner_description">Owner description</label>
<input id="owner_description" name="owner_description" type="text">

This above code reads data from MySQL and creates options for select.

So far so good.

I was able to achieve my goal by setting data-name, data-address and data-description attributes in my HTML and assign to this 3 attributes proper data from the database to every option in my select and then use jQuery to fill the inputs. It looks like this after rendering PHP:

HTML:

<option value="0" selected>Select...</option>
<option value="1" data-id="1" data-name="ABC" data-address="London" data-description="Description of ABC owner">1 ABC</option>
<option value="2" data-id="2" data-name="DEF" data-address="Birmingham" data-description="Description of DEF owner">2 DEF</option>
<option value="3" data-id="3" data-name="GHI" data-address="Manchester" data-description="Description of GH~I owner">3 GHI</option>

JS:

$('#owner').change(function () {
  var changedOption = $(this).find('option:selected');
  var ownerName = changedOption.attr('data-name');
  var ownerAddress = changedOption.attr('data-address');
  var ownerDescription = changedOption.attr('data-description');
  $('#owner_name').val(ownerName);
  $('#owner_address').val(ownerAddress);
  $('#owner_description').val(ownerDescription);
});

But there is a problem, imagine I have 1000 owners and think how much data would have to be assign into HTML. I would like to read data from my database on every change of my select using AJAX to avoid using HTML data-attributes.

Little example on CODEPEN to see what I mean exactly.

Can someone give me a clue where to start?

Answer 1

Say for example you wanted to make a request to a url and get a JSON response back containing the data you need (id, name, etc), something like this:

{
    "ID":"1",
    "name":"Bobby Longsocks",
    "address":"some place",
    "description":"A description here"
}

Using jQuery there are various ways to do what you need to now that you have the data in this format, one way would be to use $.get - which is appropriate for this example as that is what the PHP below will be looking for.

You could do something like this:

$('body').on('change', '#owner', function () {
    var changedOption = $(this).find('option:selected').val();
    var data = {
        "user_id" : changedOption
    };
    $.get('ajax.php', data, function (response) {
        $('#owner_name').empty().html(response.name);
        $('#owner_addr').empty().html(response.address);
        $('#owner_desc').empty().html(response.description);
    }, 'json')
});

If the user selection was this:

<option value="1" data-id="1">1 ABC</option>

The requested url will be ajax.php?user_id=1

---

So... To get that response using PHP you can use json_encode to send back a JSON response to the client:

Lets say you have a file on your server called ajax.php where the following php will live:

(I prefer PDO so I will use that as a quick example)

<?php
try {
    // Set up the response
    $response = array();
    // DB details
    $db = new PDO(your_db_details);
    // Read 
    if ( $_SERVER['REQUEST_METHOD'] == "GET" ) {
        // Dont forget to sanitize!
        $user_id = $_GET['user_id'];
        // Build the query
        $stmt = $db->query("SELECT `ID`, `name`, `address`, `description` FROM `your_table` WHERE `id` = '$user_id'");
        if ($user_id) {
            // If the ID is set, fetch the record
            $response = $stmt->fetch(PDO::FETCH_ASSOC);
        }
        else {
            // Do something if no id was set
        }
        // Encode it as JSON and ship it back
        echo json_encode($response);
    }

} catch(Exception $e) {
    // Do some error handling
}
?>

Which will return the response we started with.

Answer 2

First, create a select field with a list of users so it shows their name, and uses the userId as the value.

Create a PHP file that can receive the userId ($_POST['user_id']), grab the user from the database, put the details you need for the user into an array. Then convert the array to JSON and echo it. echo json_encode($userArray);

In your HTML, use jQuery/Ajax to post the value of the select field to your PHP page. It will response with some JSON containing the users details, then you can put these values into the relevant fields.

See the jquery ajax documentation http://api.jquery.com/jquery.getjson/