英文:
Fetch hidden product_id from database to jquery autocomplete plugin list
问题
我正在使用 jQuery 自动完成插件来使用 PHP、MySQL 和 Ajax 从数据库中选择数据。该插件运行良好，除了获取 product_id。当插件获取自动完成列表时，我还想附加一个隐藏的 product_id 到产品上，以区分产品，例如在同一 product_name 下有多个产品的情况。
下面是仅使用 product_name 功能的代码。
function select_name(){
$("[id^='product_name']").focus(function() {
var id = $(this).attr('id');
id = id.replace("product_name",'');
$("[id^='product_name']").autocomplete({
source: 'store_supply/fetch_autocomplete_name.php',
select: function (event, ui) {
var pro_nm = ui.item.value;
$.ajax({
url:"store_supply_manage/fetch_product_code.php",
method:"POST",
data:{pro_nm:pro_nm},
//在选择 product_name 时，我想要发布一个 product_id
dataType:"json",
success:function(data){
$('#mu_emri_'+id).val(data.mu_name);
$('#product_code_'+id).val(data.barCode);
$('#vat_vlera_'+id).val(data.vat_value_4);
$('#product_id'+id).val(data.product_id);
calculateTotal();
}
});
}
});
});
}
//fetch_autocomplete.php
if (isset($_GET['term'])) {
$term = $_GET['term'];
$query = $db->prepare("SELECT product_name FROM products
WHERE product_name LIKE '%$term%' LIMIT 10");
$query->execute();
$nr = $query->rowCount();
if ($nr > 0) {
while ($row = $query->fetch()) {
$result[] = $row['product_name'];
}
}
else {
$result = array();
}
//返回 JSON 结果
echo json_encode($result);
}
<details>
<summary>英文:</summary>
I am using jquery autocomplete plugin for selecting data from database using PHP, MySql and Ajax.
The plugin operates good except fetching the product_id. When the plugin fetches the autocomplete list I want also to attach a hidden product_id to the products to differentiate the products for example in case of multiple products with the same product_name.
Below is the code that functions only with product_name.
function select_name(){
$("[id^='product_name']").focus(function() {
var id = $(this).attr('id');
id = id.replace("product_name",'');
$("[id^='product_name']").autocomplete({
source: 'store_supply/fetch_autocomplete_name.php',
select: function (event, ui) {
var pro_nm = ui.item.value;
$.ajax({
url:"store_supply_manage/fetch_product_code.php",
method:"POST",
data:{pro_nm:pro_nm},
//here I want to post a product_id when selecting the product_name
dataType:"json",
success:function(data){
$('#mu_emri_'+id).val(data.mu_name);
$('#product_code_'+id).val(data.barCode);
$('#vat_vlera_'+id).val(data.vat_value_4);
$('#product_id'+id).val(data.product_id);
calculateTotal();
}
});
}
});
});
}
//fetch_autocomplete.php
if (isset($_GET['term'])) {
$term = $_GET['term'];
$query = $db->prepare("SELECT product_name FROM products
WHERE product_name LIKE '%$term%' LIMIT 10");
$query->execute();
$nr = $query->rowCount();
if ($nr > 0) {
while ($row = $query->fetch()) {
$result[] = $row['product_name'];
}
}
else {
$result = array();
}
//return json result
echo json_encode($result);
}
</details>
# 答案1
**得分**: 1
在你的代码中，你正在准备你的SQL语句，但是在查询时插入了变量`$term`，而没有对查询进行参数化。在下面的示例中，我已经对你的查询进行了参数化。
如[文档](https://api.jqueryui.com/autocomplete/#option-source)所示，数据可以是以下之一：
> * 字符串数组：["Choice1", "Choice2"]
> * 带有标签和值属性的对象数组：[{ label: "Choice1", value: "value1" }, ...]
因此，你可以将你的`fetch_autocomplete.php` 更改为以下内容：
```php
if (isset($_GET['term'])) {
$term = '%' . $_GET['term'] . '%';
// 使用nowdoc语法的参数化查询
$sql = <<<SQL
SELECT id AS `value`, product_name AS `label`
FROM products
WHERE product_name LIKE :term
LIMIT 10
SQL;
// 准备查询
$query = $db->prepare($sql);
// 绑定变量并执行查询
$query->execute(['term' => $term]);
// 由于fetchAll()在没有匹配行的情况下返回空数组，因此无需检查返回的行
$result = $query->fetchAll(PDO::FETCH_OBJ);
// 返回JSON结果
echo json_encode($result);
}
将id更改为你的产品ID列的名称。现在，在你的select处理程序中，ui.item.value将是产品ID，而不是产品名称。
英文:
In your code you are preparing your SQL statement but interpolating the $term variable instead of parameterizing your query. In the example below I have parameterized your query.
As shown in the documentation, the data can be either:
> * An array of strings: [ "Choice1", "Choice2" ]
> * An array of objects with label and value properties: [ { label: "Choice1", value: "value1" }, ... ]
So you can just change your fetch_autocomplete.php to something like:
if (isset($_GET['term'])) {
$term = '%' . $_GET['term'] . '%';
// parameterized query in nowdoc*
$sql = <<<'SQL'
SELECT id AS `value`, product_name AS `label`
FROM products
WHERE product_name LIKE :term
LIMIT 10
SQL;
// prepare the query
$query = $db->prepare($sql);
// bind variables and execute
$query->execute(['term'] => $term);
// As fetchAll() returns an empty array if there are no matching
// rows we do not need to check rows returned
$result = $query->fetchAll(PDO::FETCH_OBJ);
// return json result
echo json_encode($result);
}
Change id to whatever the name of your product id column is. Now, inside your select handler, ui.item.value will be the product id instead of its name.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论