使用一个查询的结果进行另一个查询并填充 HTML 选择

Question

我对数据库的结构没有发言权或决定权，所以我必须使用我所拥有的。否则，正如大多数评论所说，通过规范化和清理表格，这个问题将大大简化。我有一个数据库表，其中包含汽车型号和一些“类别”（国产车、进口车、皮卡车、面包车等）。值得注意的是，这些类别以整数代码表示。该表每行有 10 个类别列（Use1、Use2、Use3 等），因此每辆车最多可以有 10 个。然后我有另一个表，其中包含对应于每个类别代码的描述（例如，国产车 = 1，进口车 = 2，等等）。我有一个包含两个 HTML 选择元素的表单，一个用于汽车，另一个用于类别。<div class="form-group row">    <label class="col-sm-2 form-control-label">Cars</label>    <div class="col-sm-6">        <select class="form-control" id="car_model" name="car_model" onchange="fetch_categories(this.value);">            <option>Car 1</option>            <option>Car 2</option>            <option>Car 3</option>        </select>    </div></div><div class="form-group row">    <label class="col-sm-2 form-control-label">Category</label>    <div class="col-sm-4">        <select class="form-control" name="category">            <option>Select a car first...</option>        </select>    </div></div>我需要根据汽车选择的值填充类别选择。这是通过使用 Ajax 完成的（按预期工作的部分可能与主题无关）。Ajax 函数将汽车数据发送到一个 php 脚本，该脚本通过查询成功获取所有相应的类别。我正在努力使用该查询的结果来获取通过另一个查询来自它的所有类别的描述。我试过做一个 foreach 来迭代查询 #1 的所有结果，然后执行查询 #2 然后制定<option>标签，但我没有做对。<?php$car_model = $_POST['get_car'];$query = "SELECT CodUso1,                 CodUso2,                 CodUso3,                 CodUso4,                 CodUso5,                 CodUso6,                 CodUso7,                 CodUso8,                 CodUso9,                 CodUso10 AS categories FROM cars WHERE car_model = '$car_model'";$stmt = $pdo->prepare($query);$stmt->execute();$result = $stmt->fetchAll();foreach ($result['categories'] as $u):    if ($result['CodUso1'] != 0 ||         $result['CodUso2'] != 0 ||         $result['CodUso3'] != 0 ||         $result['CodUso4'] != 0 ||         $result['CodUso5'] != 0 ||         $result['CodUso6'] != 0 ||         $result['CodUso7'] != 0 ||         $result['CodUso8'] != 0 ||         $result['CodUso9'] != 0 || 结果<option>元素必须包含作为值的汽车代码和作为“描述”的描述。如果解释过于复杂，我深表歉意，如果您需要任何进一步的信息或澄清，请告诉我。

Answer 1

这里有很多错误，其中大部分可以通过检查您的数据来解决。利用var_dump并print_r确保变量包含您认为的内容。

首先，如果您为数据库列分配别名，则它只是一列。所以你最终得到数组索引CodUso1,CodUso2等到CodUso9，然后categories作为 . 的别名CodUso10。其次，您尝试循环通过$result["categories"]which 不存在，因为fetchAll将返回一个索引数组。您可以通过使用print_r($result);并查看您正在使用的内容来实现这两者。第三，您将用户提供的数据直接传递到数据库查询中，这是让您的数据库受到攻击的好方法。

试试这个。我们安全地准备一个语句来检索所有十个类别列，然后一次循环遍历它们，执行第二个准备好的语句以获取结果。请注意，除了安全性之外，准备好的语句的好处之一是在重复执行相同的查询时减少了开销。最后，尽可能将代码和演示文稿分开。在循环中间将 HTML 转储到屏幕是丑陋且难以使用的。您应该返回一个 JSON 对象，然后使用您的 Javascript 回调来构建元素。

<?php

$car_model = $_POST['get_car'];

// prepare the first query    

$query = "SELECT CodUso1, CodUso2, CodUso3, CodUso4, CodUso5,

        CodUso6, CodUso7, CodUso8, CodUso9, CodUso10

    FROM cars WHERE car_model = ? LIMIT 1";

$stmt = $pdo->prepare($query);

$stmt->execute([$car_model]);

// there's only one item, no loop needed

$categories = $stmt->fetch(\PDO::FETCH_ASSOC);

// here we prepare the second query

$query = "SELECT description FROM categories WHERE code = ?";

$stmt = $pdo->prepare($query);

// bind the variable to the ? parameter

$stmt->bindParam(1, $c);

$options = [];

foreach ($categories as $c) {

    if ($c == 0) {

        continue;

    }

    $stmt->execute();

    // we can fetch the single column directly

    $description = $stmt->fetchColumn();

    $options[] = ["id"=>$c, "description"=>$description];

}

header("Content-Type: application/json");

echo json_encode($options);

然后在你的 Javascript 回调中，做这样的事情。我假设您使用的是 jQuery：

...

success: function(data, status, xhr) {

    // erase existing values

    $("select[name='category']").html("");

    // jQuery will turn this into an object automatically

    // loop through it and append an option element for each item

    data.each(function(v) {

        var sel = $("<option>").val(v.id).html(v.description);

        $("select[name='category']").append(sel);

    });

}

Answer 2

不工作的原因foreach是因为$result['categories']它不是一个数组。您的查询select子句只是重命名CodUse10为categories.

SELECT CodUso1, 

            CodUso2, 

            CodUso3, 

            CodUso4, 

            CodUso5, 

            CodUso6, 

            CodUso7, 

            CodUso8, 

            CodUso9, 

           CodUso10 AS categories

但真正的问题远不止于此。它回到你的表模式。任何时候你有诸如 field1、field2、field3 之类的东西……你几乎可以肯定有一个未规范化的表。正确的架构看起来像这样：

Car    Car_Category   Category 

id     car_id         id 

model  category_id    description

当您想获取某辆车的类别列表时，只需查询连接表：

select c.model, cat.id, cat.description

from car c

inner join car_category cc on c.id=cc.car_id

inner join category cat on cc.category_id=cat.id

where car.id=?

我不完全理解您是否尝试显示可用选项或选定选项，但无论哪种方式，都将使用相同类型的多对多连接。因此，car_category您可能有一个名为的表available_options和另一个名为selected_options.

...但是我不能更改数据库！

在这种情况下，将没有“正确”的解决方案；只是一些创造性的方法来解决它。目标应该是尽量减少产生的技术债务。可能最大的部分是将模型（逻辑）与视图（html）分开。这样，如果/当架构被更正时，您不必在更改访问方法时深入视图并破坏事物；您所要做的就是插入一个不同的类或函数来获取信息并以相同的方式输出。

定义视图期望如何接收模型的数据

在开始使用模型之前，我需要知道视图将如何使用它。对于我自己的应用程序，我有一个类可以为我格式化输入、下拉菜单和复选框，ala ruby on rails：

<div class="form-group">

  <label for="category">Category</label>

  <?= FormDecorator::showAsDropDown($optionList, $defaultOption,['id'=>'category','name'=>'category','class'=>'form-control', etc...]) ?>

</div>

要使用它，我将提供一个关联数组，其中 options = array('value'=>'description')。关键是我需要模型来提供该数组，但是它可能会获取信息。但模型不负责创建任何html。

使用相同的计划，您的输出可能看起来像

<div class="form-group row">

    <label class="col-sm-2 form-control-label">Category</label>

    <div class="col-sm-4">

        <select class="form-control" name="category">

            <option disabled selected hidden>Select a car first...</option>

            <?php foreach($options as $value => $description): ?>

              <option value="<?= $value ?>"><?= $description ?></option>

            <?php endforeach; ?>

        </select>

    </div>

</div>

创建模型以提供该数据

通常，我会使用一个对象，因为我使用自己的 MVC 框架。但为了简单起见，我将使用一个普通的旧函数（但我强烈建议使用类，因为它们更加灵活，并且方法名称在类的范围内，而不是全局范围内）

一次查找描述

您想要遍历 10 个不同字段的结果，所有字段都命名为CodUso{$number}.

首先，创建一个函数来检索原始数据：

function getCategoriesFor($car_model) {

    global $pdo; // ick, a class would avoid globals.

    $query =<<<CATEGORYQUERY

SELECT CodUso1, CodUso2, CodUso3, CodUso4, CodUso5, 

       CodUso6, CodUso7, CodUso8, CodUso9, CodUso10

FROM cars 

WHERE car_model = ?

CATEGORYQUERY;

    $stmt = $pdo->prepare($query);

    $stmt->execute($car_model);

    return stmt->fetch(\PDO::FETCH_ASSOC);

 }      

现在，创建一个函数来获取类别的描述：

// if this were an object, I would call it $Category->find($id)

function getCategoryDescriptionOf($code) {

    global $pdo;

    $query = "select * from categories where code=?";

    $stmt = $pdo->prepare($query);

    $stmt->execute($id);

    return $stmt->fetchAll();

}

然后创建一个函数来遍历可用的选项：

function getCategoryOptionsByModel($car_model) {

    // get the row containing CodUso1 ... CodUso10

    $categoryRow = getCategoriesFor($car_model);

    // always initialize output before generating its contents

    $out = [];

    // 10 fields, iterate through them.  This is the hacky part...

    for($i=1; $i <= 10; $i++) {

        // generate the field name

        $field = 'CodUso' . $i;

        // get the code from the car_model table row 

        $code = $categoryRow[$field];

        // format the array we will use in the view

        $out[$code] = getCategoryDescriptionOf($code);

    }

    return $out;  // [code => description]

}

哇！现在剩下的就是在视图中使用它：

<?php

$car_model = $_GET['car_model']; // or however it is assigned...

?>

<html> 

  ... snip ...

<div class="form-group row">

    <label class="col-sm-2 form-control-label">Category</label>

    <div class="col-sm-4">

        <select class="form-control" name="category">

            <option disabled selected hidden>Select a car first...</option>

            <?php foreach( getCategoryOptionsByModel($car_model) as $value => $description): ?>

              <option value="<?= $value ?>"><?= $description ?></option>

            <?php endforeach; ?>

        </select>

    </div>

</div>

欢呼！我标准化了表格！

如果您对表进行规范化，只需编写一个新函数来替换当前getCategoriesFor()函数（根据需要替换真实字段名称）并在视图中使用它，而不是getCategoryOptionsByModel($car_model)或者只是更改 getCategoryOptionsByModel($car_model) 以返回 getCategoriesFor($car_model)：

function getCategoriesFor($car_model) {

    global $pdo;

    $query =<<<THISISHOWITSHOULDBEDONEQUERY

select cat.code, cat.description

from cars c

inner join car_category cc on c.id=cc.car_id

inner join category cat on cc.category_id=cat.id

where cars.car_model=?

THISISHOWITSHOULDBEDONEQUERY;

    $stmt = $pdo->prepare($query)->execute($car_model);

    $out = [];

    foreach($stmt as $row) {

        $code = $row['code'];

        $description = $row['description'];

        $out[$code] = $description;

    }

    return $out;

}

好处

通过将逻辑与表示分离，并确保每个函数只做一件小事，您现在可以更轻松地更改数据的编译方式。除了命名不同的数据源之外，您不必对视图进行任何更改。如果你之后的下一个程序员是一个知道你住在哪里的杀人狂，你会安全得多！

免责声明

这一切都在我的脑海中。虽然我推荐 PDO，但我既不使用 MySQL 也不使用 PDO，我的用法可能需要更正。