如何使用PHP和jQuery AJAX从数据库中获取数据并将其赋值给输入值

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).

在我的小应用程序中，我的数据库中有owner表。每个所有者都有自己的ID、名称、地址和描述(这些是该表的列)。

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).

在这个select中，我有3个输入(一个表示名称，一个表示地址，一个表示描述)。

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

当我更改select元素中的选项时，我希望使用AJAX将数据库中的数据填充到所有输入中。

My PHP:

我的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:

呈现的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.

上面的代码从MySQL中读取数据并为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中设置数据名称、数据地址和数据描述属性，并将数据库中正确的数据分配给select中的每个选项，然后使用jQuery填充输入，我实现了我的目标。它在呈现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.

但有一个问题，假设我有1000个所有者，并考虑需要向HTML分配多少数据。为了避免使用HTML数据属性，我希望在每次选择更改时从数据库中读取数据。

Little example on CODEPEN to see what I mean exactly.

Can someone give me a clue where to start?

谁能告诉我从哪里开始吗?

2 个解决方案

#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:

例如，您希望向url发出请求，并得到包含所需数据(id、名称等)的JSON响应，类似如下:

{
    "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.

使用jQuery现在有了这种格式的数据，有多种方法可以完成所需的工作，一种方法是使用$。get——这对于这个示例来说是合适的，因为下面的PHP将会寻找它。

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

所请求的url将是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:

所以…要使用PHP获得响应，可以使用json_encode向客户端发送JSON响应:

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

假设服务器上有一个名为ajax的文件。以下php将位于的php:

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

(我更喜欢PDO，所以我会用它作为一个简单的例子)

<?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.

它将返回我们开始时的响应。

#2

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

首先，创建一个带有用户列表的select字段，以显示它们的名称，并使用userId作为值。

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);

创建一个PHP文件，该文件可以接收userId ($_POST['user_id'])，从数据库中获取用户，将用户所需的详细信息放入数组中。然后将数组转换为JSON并回传。回声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.

在HTML中，使用jQuery/Ajax将select字段的值发布到PHP页面。它将使用一些包含用户详细信息的JSON进行响应，然后您可以将这些值放入相关字段中。

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

参见jquery ajax文档http://api.jquery.com/jquery.getjson/

#1