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?

Michael Doye · Accepted Answer · 2015-11-11 17:48:48Z

2

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.

answered Nov 11, 2015 at 17:48

Michael Doye

8,2115 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

max Over a year ago

Thank you for your answer, I achieved my goal without your post using hints provided by @SheppardDigital, but this answer is detailed and it answers my question.

max Over a year ago

What do you mean by "Don't forget to sanitize"? Can i use POST instead of GET? I'm not an expert in PHP.

Michael Doye Over a year ago

You can use post yes, that would work as well. By sanitize the inputs I mean escape unwanted user input, such as arbitrary code. You can also look at prepared statements for that.

SheppardDigital · Accepted Answer · 2015-11-11 16:30:11Z

1

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/

answered Nov 11, 2015 at 16:30

SheppardDigital

3,2708 gold badges51 silver badges81 bronze badges

1 Comment

max Over a year ago

Thank you for your answer, I will try to implement this in couple of hours. I let you know :) First thing is done (select field with names and userId as value as you can see).

Collectives™ on Stack Overflow

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

Little example on CODEPEN to see what I mean exactly.

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Little example on CODEPEN to see what I mean exactly.

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related