php - 从用户表单定义值 php/sql 表单返回客户列表

Question

目前我正在编写一些代码，允许用户输入两个值(在本例中是两个邮政编码)，提交后，程序将返回等于代码且介于代码之间的值。例如，如果分别输入 123456 和 234567 作为开始和结束邮政编码，则将返回仅具有该范围内邮政编码的客户。

我已经能够通过直接在 SQL 命令中在预定范围内进行硬编码来使用 SQL 来完成此操作。但我想用用户输入的变量来做到这一点。我相信这将在最终的 fetch 命令中完成。

$SQL = "SELECT LastName, FirstName, Address1, City, State, Zip FROM Customers ORDER BY LastName ASC;";
try {
    $sth = $conn->prepare($SQL);
    $sth->execute();
} catch (PDOException $e) {
    echo "Error selecting customer records: " . $e->getMessage();
    die;
}

echo "Query excecuted successfully. <br />";

if($sth->rowCount()==0) {
    echo "No records returned. <br />";
    die;
} else {
    echo $sth->rowCount() . " records returned. <br /> <br />";
}

while($result = $sth->fetch()){
    //in an array, refer to column with string inside brackets ['rid']
    echo "LastName: " . $result['LastName'] . "<br />";
    echo "FirstName: " . $result['FirstName'] . "<br />";
    echo "Address1: " . $result['Address1'] . "<br />";
    echo "City: " . $result['City'] . "<br />";
    echo "State: " . $result['State'] . "<br />";
    echo "Zip: " . $result['Zip'] . "<br /> <br />";
}

在最初的 $SQL 语句中，我可以使用 WHERE Zip BETWEEN ## AND ## 来获取我想要的内容，但我想使用用户定义的变量。我该怎么办呢？我已经进行了相当广泛的研究，但我一定没有使用正确的术语。

Answer 1

使用占位符 (?) 并在 ->execute() 中将用户输入隐式绑定(bind)到它们:

$zipFrom = $_POST['zipFrom'];
$zipTo   = $_POST['zipTo'];
// TODO: validate input data

$SQL = "SELECT LastName, FirstName, Address1, City, State, Zip 
        FROM Customers 
        WHERE Zip BETWEEN ? AND ?
        ORDER BY LastName ASC";
$sth = $conn->prepare($SQL);
$sth->execute([$zipFrom, $zipTo]);

您还可以使用命名占位符来使查询更具可读性:

$SQL = "SELECT LastName, FirstName, Address1, City, State, Zip 
        FROM Customers 
        WHERE Zip BETWEEN :zip_from AND :zip_to
        ORDER BY LastName ASC";
$sth = $conn->prepare($SQL);
$sth->execute([
    'zip_from' => $zipFrom,
    'zip_to'   => $zipTo
]);

您可以将代码放入 try-catch- block 中。但如果您只想转储错误消息，那么这样做是没有意义的。