PHP8-解决方案-八-

布客飞龙
77 阅读58分钟

PHP8 解决方案（八）

原文：PHP 8 Solutions

协议：CC BY-NC-SA 4.0

十七、从多个表中提取数据

正如我在第十三章中解释的，关系数据库的主要优势之一是能够通过使用一个表中的主键作为另一个表中的外键来链接不同表中的数据。phpsols数据库有两个表:images和blog。是时候再添加一些并加入它们了，这样你就可以给博客条目分配类别，并将图片与单独的文章关联起来。

您并不实际连接多个表，而是通过 SQL 来连接。通常，可以通过识别主键和外键之间的直接关系来连接表。但是，在某些情况下，这种关系更加复杂，需要通过第三个表，作为另外两个表之间的交叉引用。

在本章中，您将学习如何建立表之间的关系，以及如何将一个表中的主键作为外键插入到另一个表中。虽然从概念上听起来很难，但实际上非常简单——使用数据库查询在第一个表中查找主键，保存结果，然后在另一个查询中使用该结果将它插入到第二个表中。

特别是，您将了解以下内容:

  • 了解不同类型的表关系

  • 对多对多关系使用交叉引用表

  • 改变表的结构以添加新列或索引

  • 将主键作为外键存储在另一个表中

  • 用INNER JOIN和LEFT JOIN链接表格

了解表关系

最简单的关系类型是一对一(通常表示为 1:1 )。这种类型的关系通常出现在包含只有某些人应该看到的信息的数据库中。例如，公司通常将员工的工资明细和其他机密信息存储在一个表中，与更容易访问的员工列表分开。将每个员工记录的主键作为外键存储在 tables 表中，可以在两个表之间建立直接关系，从而允许会计部门查看所有信息，同时限制其他人只能查看公共信息。

在phpsols数据库中没有机密信息，但是你可以在images表中的一张图片和blog表中的一篇文章之间建立一对一的关系，如图 17-1 所示。

img/332054_5_En_17_Fig1_HTML.jpg

图 17-1

一对一关系将一个记录与另一个记录直接链接起来

这是在两个表之间创建关系的最简单的方法，但并不理想。随着更多文章的加入，这种关系的性质可能会改变。与图 17-1 中的第一篇文章相关的图片显示枫叶漂浮在水面上，因此它可能适合于说明一篇关于季节变化或秋天色调的文章。清澈的水、竹制的水瓢和竹制的烟斗也暗示了图片可以用来说明的其他主题。因此，你很容易就会发现同一张图片被用于几篇文章，或者是一个一对多(或 1:n )的关系，如图 17-2 所示。

img/332054_5_En_17_Fig2_HTML.jpg

图 17-2

一对多关系将一个记录与其他几个记录联系起来

正如您已经了解到的，主键必须是唯一的。因此，在一个1:n关系中，您将关系的1端的表中的主键(主表或父表)作为外键存储在n端的表中(辅表或子表)。在这种情况下，images表中的image_id需要作为外键存储在blog表中。关于1:n关系，重要的是要理解它也是1:1关系的集合。从右向左看图 17-2 每篇文章都与一个单独的图像有关系。如果没有这种一对一的关系，您将无法识别哪个图像与特定的文章相关联。

如果你想给每篇文章关联多个图片，会发生什么？您可以在blog表中创建几列来保存外键，但是这很快就变得难以处理了。你可能以image1、image2和image3开始，但是如果大多数文章只有一张图片，那么两栏在很多时候是多余的。你会为那篇需要四张图片的特别文章增加一个额外的专栏吗？

当面临适应多对多(或 n:m )关系的需求时，您需要一种不同的方法。images和blog表没有包含足够的记录来展示n:m关系，但是您可以添加一个categories表来标记单独的文章。大多数文章可能属于多个类别，每个类别都与几篇文章相关。

解决复杂关系的方法是通过一个交叉引用表(有时称为链接表)，它在相关记录之间建立一系列一对一的关系。这是一个特殊的表，只包含两列，这两列都声明为联合主键。图 17-3 显示了其工作原理。交叉引用表中的每条记录都存储了blog和categories表中各个文章之间关系的详细信息。要查找属于Kyoto类别的所有文章，您需要将categories表中的cat_id 1与交叉引用表中的cat_id 1进行匹配。这将blog表中带有article_id 2、3和4的记录标识为与Kyoto相关联。

img/332054_5_En_17_Fig3_HTML.jpg

图 17-3

交叉引用表将多对多关系解析为 1:1

通过外键建立表之间的关系对于如何更新和删除记录有着重要的意义。如果你不小心，你会以断链而告终。确保依赖关系不被破坏被称为维护参照完整性。我们将在下一章讨论这个重要的课题。首先，让我们集中检索存储在通过外键关系链接的单独表中的信息。

将图像链接到文章

为了演示如何使用多个表，让我们从图 17-1 和 17-2 中概述的简单场景开始:通过将一个表(父表)中的主键存储为另一个表(子表或依赖表)中的外键，可以将关系解析为1:1。这涉及到在子表中添加一个额外的列来存储外键。

改变现有表的结构

理想情况下，您应该在用数据填充数据库之前设计好数据库结构。然而，关系数据库，如 MySQL，非常灵活，允许您添加、删除或更改表中的列，即使它们已经包含记录。要将图像与数据库phpsols中的单个文章相关联，您需要向blog表中添加一个额外的列，以将image_id存储为外键。

PHP 解决方案 17-1:向表中添加额外的列

这个 PHP 解决方案展示了如何使用 phpMyAdmin 向现有的表中添加额外的列。它假设您在第十五章的phpsols数据库中创建了blog表。

  1. 在 phpMyAdmin 中，选择phpsols数据库，并单击blog表的Structure链接。

  2. 在blog表格结构下面是一个允许您添加额外列的表单。您希望只添加一列，所以在Add column(s)文本框中使用默认值就可以了。通常的做法是将外键放在表的主键之后，所以从下拉菜单中选择after article_id，如下图所示。然后点击Go:

img/332054_5_En_17_Figa_HTML.jpg

  1. 这将为您打开定义列属性的屏幕。使用以下设置:

    • 名称:image_id

    • 类型:INT

    • 属性:UNSIGNED

    • 空:已选择

    • Index: INDEX(不需要在弹出的模态对话框中给它命名)

不要选择A_I ( AUTO_INCREMENT)复选框。你不希望image_id自动递增。其值将从images表中插入。

选择Null复选框是因为并非所有文章都与图像相关联。点击Save。

  1. 选择Structure选项卡，检查blog表结构现在看起来像这样:

img/332054_5_En_17_Figb_HTML.jpg

  1. 如果您单击屏幕左上方的Browse选项卡，您将看到在每条记录中image_id的值为空。现在的挑战是插入正确的外键，而不需要手动查找数字。我们接下来会解决这个问题。

在表中插入外键

在另一个表中插入外键的基本原理非常简单:查询数据库以找到想要链接到另一个表的记录的主键。然后，您可以使用一个INSERT或UPDATE查询将外键添加到目标记录中。

为了演示基本原理，您将修改第 15 ( blog_update_mysqli.php或blog_update_pdo.php)章中的更新表单，以添加一个下拉菜单，列出已经在图像表中注册的图像(参见图 17-4 )。

img/332054_5_En_17_Fig4_HTML.jpg

图 17-4

一个动态生成的下拉菜单插入适当的外键

菜单是由一个显示SELECT查询结果的循环动态生成的。每个图像的主键存储在<option>标签的value属性中。提交表单时，选择的值作为外键被合并到UPDATE查询中。

PHP 解决方案 17-2:添加图像外键(MySQLi)

这个 PHP 解决方案展示了如何通过添加选定图像的主键作为外键来更新blog表中的记录。它改编自第十五章中的admin/blog_update_mysqli.php。使用您在第十五章中创建的版本。或者，将blog_update_mysqli_03.php从ch15文件夹复制到admin文件夹，并从文件名中删除_03。

  1. 检索要更新的文章的细节的现有的SELECT查询需要修改，以便它包括外键image_id，并且结果需要绑定到新的结果变量$image_id。然后，您需要运行第二个SELECT查询来获取images表的细节。在这样做之前，您需要通过调用准备好的语句的free_result()方法来释放数据库资源。将下面以粗体突出显示的代码添加到现有脚本中:

    if (isset($_GET['article_id']) && !$_POST) {
    // prepare SQL query
    $sql = 'SELECT article_id, image_id, title, article FROM blog
        WHERE article_id = ?';
    if ($stmt->prepare($sql)) {
        // bind the query parameter
        $stmt->bind_param('i', $_GET['article_id']);
        // execute the query
        $OK = $stmt->execute();
        // bind the results to variables and fetch
        $stmt->bind_result($article_id, $image_id, $title, $article);
        $stmt->fetch();
        // free the database resources for the second query
        $stmt->free_result();
    }
}

您可以在调用fetch()方法后立即释放结果，因为结果集中只有一条记录，并且每一列中的值都被绑定到一个变量。

  1. 在表单内部，您需要显示存储在images表中的文件名。因为第二个SELECT语句不依赖于外部数据，所以使用query()方法比使用预准备语句更简单。在article文本区域后添加以下代码(这都是新代码，但是为了便于参考，PHP 部分用粗体突出显示):

    <p>
    <label for="image_id">Uploaded image:</label>
    <select name="image_id" id="image_id">
        <option value="">Select image</option>
        <?php
        // get the list images
        $getImages = 'SELECT image_id, filename
                      FROM images ORDER BY filename';
        $images = $conn->query($getImages);
        while ($row = $images->fetch_assoc()) {
            ?>
            <option value="<?= $row['image_id'] ?>"
                <?php
                 if ($row['image_id'] == $image_id) {
                     echo 'selected';
                 }
                 ?>><?= safe($row['filename']) ?></option>
        <?php } ?>
    </select>
</p>

第一个<option>标签被硬编码为标签Select image，它的value被设置为空字符串。剩余的<option>标签由一个while循环填充，该循环将每条记录提取到一个名为$row的数组中。

条件语句检查当前的image_id是否与已经存储在articles表中的相同。如果是，selected被插入到<option>标签中，以便在下拉菜单中显示正确的值。

确保不要省略下一行中的第三个字符:

?>><?= safe($row['filename']) ?></option>

它是<option>标签的结束尖括号，夹在两个 PHP 标签之间。

  1. 保存页面并将其加载到浏览器中。您应该会被自动重定向到blog_list_mysqli.php。选择其中一个编辑链接，确保你的页面看起来如图 17-4 所示。检查浏览器源代码视图，确认<option>标签的value属性包含每张图片的主键。

    Tip

    如果<select>菜单没有列出图像，那么几乎可以肯定第 2 步中的SELECT查询有错误。在调用query()方法后立即添加echo $conn->error;，并重新加载页面。您需要查看浏览器源代码来查看错误消息。如果消息是“命令不同步；您现在不能运行这个命令”，问题在于在步骤 1 中没有用free_result()释放数据库资源。

  2. 最后一步是将image_id添加到UPDATE查询中。因为一些博客条目可能与图像没有关联，所以您需要创建替代的准备好的语句，如下所示:

    // if form has been submitted, update record
if (isset($_POST ['update'])) {
    // prepare update query
    if (!empty($_POST['image_id'])) {
        $sql = 'UPDATE blog SET image_id = ?, title = ?, article = ?
                    WHERE article_id = ?';
        if ($stmt->prepare($sql)) {
            $stmt->bind_param('issi', $_POST['image_id'], $_POST['title'],
                $_POST['article'], $_POST['article_id']);
            $done = $stmt->execute();
        }
    } else {
        $sql = 'UPDATE blog SET image_id = NULL, title = ?, article = ?
                   WHERE article_id = ?';
        if ($stmt->prepare($sql)) {
            $stmt->bind_param('ssi', $_POST['title'], $_POST['article'],
                $_POST['article_id']);
            $done = $stmt->execute();
        }
    }
}

如果$_POST['image_id']有一个值，您可以用占位符问号将它作为第一个参数添加到 SQL 中。因为它必须是一个整数，所以您将i添加到bind_param()的第一个参数的开头。

但是，如果$_POST['image_id']不包含值，您需要创建一个不同的预准备语句，在 SQL 查询中将image_id的值设置为NULL。因为它有一个显式值，所以你不把它加到bind_param()里。

  1. 再次测试页面，从下拉菜单中选择一个文件名，然后单击Update Entry。您可以通过刷新 phpMyAdmin 中的Browse或者选择相同的文章进行更新来验证外键是否已经被插入到了articles表中。这一次，正确的文件名应该显示在下拉菜单中。

如有必要，对照ch17文件夹中的blog_update_mysqli_04.php检查您的代码。

PHP 解决方案 17-3:添加图像外键(PDO)

这个 PHP 解决方案使用 PDO 通过添加一个选中图像的主键作为外键来更新blog表中的记录。与 MySQLi 的主要区别在于，PDO 可以使用bindValue()方法将null值绑定到占位符。这些说明改编自第十五章中的admin/blog_update_pdo.php。使用您在第十五章中创建的版本。或者，将blog_update_pdo_03.php从ch15文件夹复制到admin文件夹，并从文件名中删除_03。

  1. 将image_id添加到检索待更新文章详细信息的SELECT查询中，并将结果绑定到$image_id。这包括对作为第一个参数传递给$title和$article的bindColumn()的列进行重新编号。修改后的代码如下所示:

    if (isset($_GET['article_id']) && !$_POST) {
    // prepare SQL query
    $sql = 'SELECT article_id, image_id, title, article FROM blog
               WHERE article_id = ?';
    $stmt = $conn->prepare($sql);
    // pass the placeholder value to execute() as a single-element array
    $OK = $stmt->execute([$_GET['article_id']]);
    // bind the results
    $stmt->bindColumn(1, $article_id);
    $stmt->bindColumn(2, $image_id);
    $stmt->bindColumn(3, $title);
    $stmt->bindColumn(4, $article);
    $stmt->fetch();
}

  2. 在表单内部，您需要显示存储在images表中的文件名。因为第二个SELECT语句不依赖于外部数据，所以使用query()方法比使用预准备语句更简单。在article文本区域后添加以下代码(这都是新代码，但是为了便于参考，PHP 部分用粗体突出显示):

    <p>
    <label for="image_id">Uploaded image:</label>
    <select name="image_id" id="image_id">
        <option value="">Select image</option>
        <?php
        // get the list images
        $getImages = 'SELECT image_id, filename
                      FROM images ORDER BY filename';
        foreach ($conn->query($getImages) as $row) {
            ?>
            <option value="<?= $row['image_id'] ?>"
                <?php
                if ($row['image_id'] == $image_id) {
                    echo 'selected';
                }
                ?>><?= safe($row['filename']) ?></option>
        <?php } ?>
    </select>
</p>

第一个<option>标签被硬编码为标签Select image，它的value被设置为空字符串。剩余的<option>标记由一个foreach循环填充，该循环执行$getImages SELECT查询并将每条记录提取到一个名为$row的数组中。

条件语句检查当前的image_id是否与已经存储在articles表中的相同。如果是，selected被插入到<option>标签中，以便在下拉菜单中显示正确的值。

确保不要省略下一行中的第三个字符:

?>><?= safe($row['filename']) ?></option>

它是<option>标签的结束尖括号，夹在两个 PHP 标签之间。

  1. 保存页面并将其加载到浏览器中。您应该会被自动重定向到blog_list_pdo.php。选择其中一个EDIT链接，确保你的页面看起来如图 17-4 。检查浏览器源代码视图，验证<option>标签的值属性包含每个图像的主键。

  2. 最后一步是将image_id添加到UPDATE查询中。当一个博客条目不与图像相关联时，您需要在image_id栏中输入null。这包括更改值绑定到预准备语句中的匿名占位符的方式。您需要使用bindValue()和bindParam()，而不是将它们作为数组传递给execute()方法。修改后的代码如下所示:

    // if form has been submitted, update record
if (isset($_POST['update'])) {
    // prepare update query
    $sql = 'UPDATE blog SET image_id = ?, title = ?, article = ?
               WHERE article_id = ?';
    $stmt = $conn->prepare($sql);
    if (empty($_POST['image_id'])) {
        $stmt->bindValue(1, NULL, PDO::PARAM_NULL);
    } else {
        $stmt->bindParam(1, $_POST['image_id'], PDO::PARAM_INT);
    }
    $stmt->bindParam(2, $_POST['title'], PDO::PARAM_STR);
    $stmt->bindParam(3, $_POST['article'], PDO::PARAM_STR);
    $stmt->bindParam(4, $_POST['article_id'], PDO::PARAM_INT);
    // execute query
    $done = $stmt->execute();
}

使用数字将这些值绑定到匿名占位符，从 1 开始计数，以标识它们应该应用到哪个占位符。条件语句检查$_POST['image_id']是否为空。如果是，bindValue()将值设置为null，使用关键字NULL作为第二个参数，使用 PDO 常数作为第三个参数。正如在第十三章的“在 PDO 预准备语句中嵌入变量”中所解释的，当被绑定的值不是变量时，你需要使用bindValue()。

其余的值都是变量，所以它们使用bindParam()绑定。我用 PDO 常数表示整数，用字符串表示剩余的值。这不是绝对必要的，但它使代码更清晰。

最后，从execute()方法的括号中删除了值数组。

  1. 再次测试页面，从下拉菜单中选择一个文件名，然后单击Update Entry。您可以通过刷新 phpMyAdmin 中的Browse或者选择相同的文章进行更新来验证外键是否已经被插入到了articles表中。这一次，正确的文件名应该显示在下拉菜单中。

如有必要，对照ch17文件夹中的blog_update_pdo_04.php检查您的代码。

从多个表中选择记录

有几种方法可以在一个SELECT查询中链接表，但是最常用的是列出表名，用INNER JOIN隔开。INNER JOIN独自产生所有可能的行组合(笛卡尔连接)。要仅选择相关值，您需要指定主键/外键关系。例如，要从blog和images表中选择文章及其相关图像，您可以使用一个WHERE子句，如下所示:

SELECT title, article, filename, caption
FROM blog INNER JOIN images
WHERE blog.image_id = images.image_id

title和article列只存在于blog表中。同样，filename和caption只存在于images表中。他们是明确的，不需要被限定。但是，image_id在两个表中都存在，所以您需要在每个引用前面加上表名和句点。

多年来，用逗号代替INNER JOIN是一种常见的做法，就像这样:

SELECT title, article, filename, caption
FROM blog, images
WHERE blog.image_id = images.image_id

Caution

使用逗号连接表会导致 SQL 语法错误，因为从 MySQL 5.0.12 开始，连接的处理方式发生了变化。用INNER JOIN代替。

你可以用ON代替WHERE子句，就像这样:

SELECT title, article, filename, caption
FROM blog INNER JOIN images ON blog.image_id = images.image_id

当两列具有相同的名称时，您可以使用下面的语法，这是我个人的偏好:

SELECT title, article, filename, caption
FROM blog INNER JOIN images USING (image_id)

Note

USING后的列名必须在括号中。

PHP 解决方案 17-4:构建详细页面

这个 PHP 解决方案展示了如何连接blog和images表来显示一篇选中的文章及其相关的图片。MySQLi 和 PDO 的代码几乎相同，所以这个解决方案涵盖了这两者。

img/332054_5_En_17_Fig5_HTML.jpg

图 17-5

详细信息页面包含一个占位符图像和文本

  1. 将ch17文件夹中的details_01.php复制到php8sols站点根目录，并重命名为details.php。如果编辑环境提示您更新链接，请不要这样做。确保footer.php和menu.php在includes文件夹中，并在浏览器中加载页面。它看起来应该如图 17-5 所示。

img/332054_5_En_17_Fig6_HTML.jpg

图 17-6

不与图像相关联的项目的外键被设置为空

  1. 将blog_list_mysqli.php或blog_list_pdo.php加载到浏览器中，并通过指定所示的图像文件名来更新以下三篇文章:

    • 满足的盆地:basin.jpg

    • 拥挤在一起的小餐馆:menu.jpg

    • 见习艺妓逛街:maiko.jpg

  2. 导航到 phpMyAdmin 中的blog表，并单击Browse选项卡检查外键是否已经注册。至少有一条image_id的值为NULL，如图 17-6 所示。

  3. 在试图显示一幅图像之前，我们需要确保它来自我们期望的地方，并且它确实是一幅图像。在 details.php 的顶部创建一个变量来存储图像目录的相对路径(以斜杠结束),如下所示:

    // Relative path to image directory
$imageDir = './img/';

  4. 接下来，包含上一章的utility_funcs.php(如有必要，将其从ch16文件夹复制到includes文件夹)。然后包含数据库连接文件，创建一个只读连接，并在DOCTYPE声明上方的 PHP 代码块中准备 SQL 查询，如下所示:

require_once './includes/utility_funcs.php';
require_once './includes/connection.php';
// connect to the database
$conn = dbConnect('read');  // add 'pdo' if necessary
// check for article_id in query string
$article_id = isset($_GET['article_id']) ? (int) $_GET['article_id'] : 0;
$sql = "SELECT title, article,DATE_FORMAT(updated, '%W, %M %D, %Y') AS updated,
            filename, caption
            FROM blog INNER JOIN images USING (image_id)
            WHERE blog.article_id = $article_id";
$result = $conn->query($sql);
$row = $result->fetch_assoc();  // for PDO use $result->fetch();

该代码使用三元运算符检查URL查询字符串中的article_id。用isset()测试其存在的条件。如果返回true，使用(int)转换操作符将值分配给$article_id，以确保它是一个整数。否则，$article_id被设置为0。你可以选择一篇默认的文章，但是暂时把它放在0上，因为我想说明一个重要的观点。

SELECT查询从blog表中检索title、article和updated列，从images表中检索filename和caption列。如第十六章所述，使用DATE_FORMAT()函数和别名对updated的值进行格式化。因为只检索一条记录，所以使用原始列名作为别名不会导致排序顺序问题。

使用与两个表中的image_id列中的值相匹配的INNER JOIN和USING子句来连接这两个表。WHERE子句选择由$article_id标识的商品。因为已经检查了$article_id的数据类型，所以在查询中使用它是安全的。没有必要使用预先准备好的语句。

注意，该查询用双引号括起来，以便解释$article_id的值。为了避免与外面的一对引号冲突，在作为参数传递给DATE_FORMAT()的格式字符串周围使用了单引号。

  1. 既然我们已经查询了数据库，我们可以检查图像。为了确保它在我们期望的地方，将$row['filename']的值传递给basename()函数，并将结果连接到图像目录的相对路径。然后我们可以检查文件是否存在并且可读。如果是，使用getimagesize()得到它的宽度和高度。在上一步插入的代码后立即添加以下代码:

    if ($row && !empty($row['filename'])) {
    $image = $imageDir . basename($row['filename']);
    if (file_exists($image) && is_readable($image)) {
        $imageSize = getimagesize($image)[3];
    }
}

正如 PHP 解决方案 10-1 第十章 10 中所解释的那样，getimagesize()返回一个关于图像的信息数组，包括索引 3 处的一个字符串，该字符串包含准备插入到<img>标签中的正确的宽度和高度属性。这里，我们使用数组解引用将它直接赋给$imageSize。

  1. 其余代码在页面主体中显示 SQL 查询的结果。替换<h2>标签中的占位符文本，如下所示:

    <h2><?php if ($row) {
        echo safe($row['title']);
    } else {
        echo 'No record found';
    }
    ?>
</h2>

如果SELECT查询没有找到结果，$row将为空，PHP 将其解释为false。因此，如果结果集为空，将显示标题或“未找到记录”。

  1. 像这样替换占位符日期:

  2. 紧跟在日期段落之后的是一个包含占位符图像的<figure>元素。并非所有文章都与图像相关联，因此需要将<figure>包装在一个条件语句中，以检查$imageSize是否包含值。这样修改<figure>:

    <?php if (!empty($imageSize)) { ?>
   <figure>
       <img src="<?= $image ?>" alt="<?= safe($row['caption']) ?>" <?= $imageSize ?>>
   </figure>
<?php } ?>

  3. 最后，你需要展示文章。删除占位符文本的段落，并将以下代码添加到上一步中最后一个代码块末尾的右大括号和右 PHP 标记之间:

<p><?php if ($row) { echo $row['updated']; } ?></p>


<?php } if ($row) { echo convertToParas($row['article']); } ?>

它使用utility_funcs.php中的convertToParas()函数将博客条目包装在<p>标签中，并用结束和开始标签替换换行符序列(参见第十六章中的“显示段落”)。

  1. 保存页面并将blog.php加载到浏览器中。点击文章的More链接，该文章具有通过外键分配的图像。你应该可以看到details.php，文章和图片的全文如图 17-7 所示。

如有必要，用ch17文件夹中的details_mysqli_01.php或details_pdo_01.php检查你的代码。

img/332054_5_En_17_Fig7_HTML.jpg

图 17-7

详细信息页面从一个表中提取文章，从另一个表中提取图像

img/332054_5_En_17_Fig8_HTML.jpg

图 17-8

缺少关联的图像会导致选择查询失败

  1. 点击返回blog.php的链接，测试其他项目。每篇有相关图片的文章都应该正确显示。点击没有图片的文章的More链接。这次你应该会看到如图 17-8 所示的结果。

您知道文章在数据库中，因为前两个句子不会显示在blog.php中。为了理解这种突然的“消失”，请参考图 17-6 。对于没有相关图像的记录，image_id的值是NULL。因为images表中的所有记录都有一个主键，所以USING子句找不到匹配项。下一节将解释如何处理这种情况。

查找没有匹配外键的记录

从 PHP 解决方案 17-4 中复制SELECT查询，并删除搜索特定文章的条件，剩下如下内容:

SELECT title, article, DATE_FORMAT(updated, '%W, %M %D, %Y') AS updated, filename, caption
FROM blog INNER JOIN images USING (image_id)

如果您在 phpMyAdmin 的 SQL 选项卡中运行这个查询，它会产生如图 17-9 所示的结果。

img/332054_5_En_17_Fig9_HTML.jpg

图 17-9

内部联接只查找在两个表中都匹配的记录

使用INNER JOIN,SELECT查询只成功找到完全匹配的记录。其中一篇文章没有相关联的图像，因此articles表中的image_id的值是NULL，它与images表中的任何内容都不匹配。

在这种情况下，你需要使用LEFT JOIN而不是INNER JOIN。使用LEFT JOIN，结果包括在左表中匹配，但在右表中不匹配的记录。“左”和“右”是指执行连接的顺序。像这样重写SELECT查询:

SELECT title, article, DATE_FORMAT(updated, '%W, %M %D, %Y') AS updated, filename, caption
FROM blog LEFT JOIN images USING (image_id)

当您在 phpMyAdmin 中运行它时，您会得到所有四篇文章，如图 17-10 所示。

img/332054_5_En_17_Fig10_HTML.jpg

图 17-10

左连接包括在右表中没有匹配项的记录

如您所见，右表中的空字段(images)显示为NULL。

如果两个表中的列名不同，请像这样使用 ON:

FROM table_1 LEFT JOIN table_2 ON table_1.col_name = table_2.col_name

所以现在您可以像这样重写details.php中的 SQL 查询:

$sql = "SELECT title, article, DATE_FORMAT(updated, '%W, %M %D, %Y') AS updated,
            filename, caption
            FROM blog LEFT JOIN images USING (image_id)
            WHERE blog.article_id = $article_id";

如果您点击More链接查看没有关联图片的文章，您现在应该看到文章正确显示，如图 17-11 所示。其他文章也应该仍然可以正确显示。完成的代码可以在details_mysqli_02.php和details_pdo_02.php中找到。

img/332054_5_En_17_Fig11_HTML.jpg

图 17-11

LEFT JOIN 还检索没有匹配外键的文章

创建智能链接

details.php底部的链接直接回到blog.php。在blog表中只有四个条目没问题，但是一旦你开始在数据库中获得更多的记录，你就需要建立一个导航系统，正如我在第十四章中向你展示的那样。导航系统的问题是，你需要一种方法让访问者返回到他们来自的结果集中的同一点。

PHP 解决方案 17-5:回到导航系统中的同一点

这个 PHP 解决方案检查访问者来自内部还是外部链接。如果引用的页面在同一个站点内，链接会将访问者返回到同一个地方。如果引用的页面是一个外部站点，或者服务器不支持必要的超级全局变量，脚本会用一个标准链接来代替。这里显示的是details.php的上下文，但是它可以用在任何页面上。

代码不依赖于数据库，所以它对 MySQLi 和 PDO 是一样的。

  1. 在details.php的主体中找到后连杆。看起来是这样的:

  2. 将光标放在第一个引号的右侧，插入以粗体突出显示的以下代码:

    <p><a href="
<?php
// check that browser supports $_SERVER variables
if (isset($_SERVER['HTTP_REFERER']) && isset($_SERVER['HTTP_HOST'])) {
    $url = parse_url($_SERVER['HTTP_REFERER']);
    // find if visitor was referred from a different domain
    if ($url['host'] == $_SERVER['HTTP_HOST']) {
        // if same domain, use referring URL
        echo $_SERVER['HTTP_REFERER'];
    }
} else {
    // otherwise, send to main page
    echo 'blog.php';
} ?>">Back to the blog</a></p>


<p><a href="blog.php">Back to the blog</a></p>

$_SERVER['HTTP_REFERER']和$_SERVER['HTTP_HOST']是超全局变量，包含引用页面的 URL 和当前主机名。你需要用isset()检查它们的存在，因为不是所有的服务器都支持它们。此外，浏览器可能会阻止引用页面的 URL。

parse_url()函数创建一个包含 URL 的每个部分的数组，所以$url['host']包含主机名。如果它与$_SERVER['HTTP_HOST']匹配，您就知道该访问者是由一个内部链接推荐的，所以内部链接的完整 URL 被插入到href属性中。这包括任何查询字符串，因此链接会将访问者送回导航系统中的相同位置。否则，将创建一个指向目标页面的普通链接。

完成的代码在ch17文件夹的details_mysqli_03.php和details_pdo_3.php中。

第三章回顾

使用INNER JOIN和LEFT JOIN检索存储在多个表中的信息相对简单。成功使用多个表的关键在于构建它们之间的关系，这样复杂的关系总是可以通过1:1来解决，如果需要的话，可以通过交叉引用(或链接)表来解决。下一章继续探索使用多个表，向您展示在插入、更新和删除记录时如何处理外键关系。

十八、管理多个数据库表

前一章向您展示了如何使用INNER JOIN和LEFT JOIN来检索存储在多个表中的信息。您还了解了如何通过向子表添加额外的一列来链接现有的表，并单独更新每条记录以插入外键。但是，大多数情况下，您会希望在两个表中同时插入数据。这带来了挑战，因为INSERT命令一次只能在一个表上操作。您需要以正确的顺序处理插入操作，从父表开始，这样您就可以获得新记录的主键，并将其与其他细节同时插入到子表中。在更新和删除记录时，也需要考虑类似的因素。所涉及的代码并不难，但是在构建脚本时，您需要清楚地记住事件的顺序。

本章将指导您在blog表格中插入新文章，选择相关图片或上传新图片，并将文章分配到一个或多个类别，所有这些都在一个操作中完成。然后，您将构建脚本来更新和删除文章，而不会破坏相关表的引用完整性。

您还将了解如何使用事务将多个查询作为一个批处理来处理，如果批处理的任何部分失败，将数据库回滚到其原始状态，以及外键约束，外键约束控制在您试图删除另一个表中仍然具有外键关系的记录时会发生什么。并不是所有的数据库都支持事务和外键约束，所以检查您的远程服务器是否支持是很重要的。本章还解释了如果您的服务器不支持外键约束，您可以采取什么措施来保持数据的完整性。

特别是，您将了解以下内容:

  • 在相关表中插入、更新和删除记录

  • 创建记录后立即查找记录的主键

  • 将多个查询作为单个批处理进行处理，并在任何部分失败时回滚

  • 转换表的存储引擎

  • 在 InnoDB 表之间建立外键约束

维护引用完整性

对于单表，更新一条记录的频率或删除多少条记录都无关紧要；对其他记录的影响为零。一旦将记录的主键作为外键存储在不同的表中，就创建了一个需要管理的依赖项。例如，图 18-1 显示了来自blog表的第二篇文章(“见习艺妓去购物”)通过article2cat对照表链接到Kyoto和People类别。

img/332054_5_En_18_Fig1_HTML.jpg

图 18-1

您需要管理外键关系以避免孤立记录

如果您删除了文章，但未能删除交叉引用表中的article_id 2条目，则在Kyoto或People类别中查找所有文章的查询会尝试匹配blog表中不存在的记录。同样，如果您决定删除其中一个类别，而不删除交叉引用表中的匹配记录，则查找与文章关联的类别的查询会尝试匹配一个不存在的类别。

不久之后，您的数据库中就会充斥着孤立的记录。幸运的是，维护引用完整性并不困难。SQL 通过建立称为外键约束的规则来做到这一点，外键约束告诉数据库当您更新或删除在另一个表中有依赖记录的记录时该做什么。

支持事务和外键约束

MySQL 5.5 和更高版本中的默认存储引擎 InnoDB 支持事务和外键约束。MariaDB 中的等效存储引擎是 Percona XtraDB，但它将自己标识为 InnoDB，并具有相同的功能。即使你的远程服务器运行的是 MySQL 或 MariaDB 的最新版本，也不能保证 InnoDB 是受支持的，因为你的托管公司可能已经禁用了它。

如果您的服务器运行的是旧版本的 MySQL，默认的存储引擎是 MyISAM，它不支持事务或外键约束。但是，您仍然可以访问 InnoDB，因为它从 4.0 版开始就是 MySQL 不可或缺的一部分。将 MyISAM 表转换成 InnoDB 非常简单，只需要几秒钟。

如果您不能访问 InnoDB，那么您需要通过在 PHP 脚本中构建必要的规则来维护引用完整性。本章展示了这两种方法。

Note

MyISAM 表的优点是速度非常快。它们需要较少的磁盘空间，非常适合存储不经常更改的大量数据。然而，MyISAM 引擎已经不再被积极开发，所以不建议将其用于新项目。

PHP 解决方案 18-1:检查是否支持 InnoDB

这个 PHP 解决方案解释了如何检查您的远程服务器是否支持 InnoDB 存储引擎。

img/332054_5_En_18_Fig2_HTML.jpg

图 18-2

通过 phpMyAdmin 检查存储引擎支持

  1. 如果你的托管公司提供 phpMyAdmin 来管理你的数据库，在你的远程服务器上启动 phpMyAdmin，点击屏幕顶部的Engines标签，如果它可用的话。这将显示类似于图 18-2 的存储引擎列表。

Note

图 18–2 中的截图是在 MariaDB 服务器上拍摄的。您可能会在 MySQL 服务器上看到不同的存储引擎选择，但是 MySQL 和 MariaDB 通常都应该至少提供 InnoDB 和 MyISAM。Aria 存储引擎是 MariaDB 对 MyISAM 的改进版本。本书没有涉及它，因为它在 MySQL 上不可用，也不支持事务或外键约束。

img/332054_5_En_18_Fig3_HTML.jpg

图 18-3

确认支持 InnoDB

  1. 该列表显示所有存储引擎，包括不受支持的存储引擎。不支持或禁用的存储引擎呈灰色显示。如果您不确定 InnoDB 的状态，请在列表中单击它的名称。

  2. 如果不支持 InnoDB，您会看到一条消息告诉您这一点。另一方面，如果您看到类似于图 18-3 的变量列表，那么您很幸运——InnoDB 是受支持的。

img/332054_5_En_18_Fig4_HTML.jpg

图 18-4

表选项中列出了可用的存储引擎

  1. 如果 phpMyAdmin 中没有Engines选项卡，选择数据库中的任意一个表，然后单击屏幕右上角的Operations选项卡。在Table options部分，点击Storage engine字段右侧的向下箭头显示可用选项(参见图 18-4 )。如果列出了 InnoDB，它是受支持的。

img/332054_5_En_18_Fig5_HTML.jpg

图 18-5

storage_engines.php 中的 SQL 查询报告了支持哪些

  1. 如果前面的方法都没有给你答案，打开ch18文件夹中的storage_engines.php。编辑前三行，在远程服务器上插入数据库的主机名、用户名和密码。

  2. 将storage_engines.php上传到你的网站，并将页面加载到浏览器中。您应该会看到存储引擎和支持级别的列表，如图 18-5 所示。在某些情况下，NO会被DISABLED代替。

如图 18-5 所示，一个典型的安装支持多个存储引擎。令人惊讶的是，您可以在同一个数据库中使用不同的存储引擎。事实上，建议你这样做。即使您的远程服务器支持 InnoDB，对于不需要事务或没有外键关系的表，使用 MyISAM 或 Aria 通常更有效。对于需要事务或具有外键关系的表，使用 InnoDB。

我将在本章的后面解释如何将表格转换成 InnoDB。在此之前，让我们看看如何建立和使用外键关系，而不管使用的是什么存储引擎。

将记录插入多个表中

一个INSERT查询只能向一个表中插入数据。因此，在处理多个表时，需要仔细规划插入脚本，以确保存储所有信息并建立正确的外键关系。

上一章的 PHP 解决方案 17-2 (MySQLi)和 17-3 (PDO)展示了如何为已经在数据库中注册的图像添加正确的外键。然而，当插入一个新的博客条目时，您需要能够选择一个现有的图像，上传一个新的图像，或者选择没有图像。这意味着您的处理脚本需要检查图像是否已被选择或上传，并相应地执行相关命令。此外，用零个或多个类别标记博客条目增加了脚本需要做出的决策数量。图 18-6 显示了决策链。

img/332054_5_En_18_Fig6_HTML.png

图 18-6

插入带有图像和类别的新博客文章的决策链

当页面第一次加载时，表单还没有提交，所以页面只显示插入表单。通过查询数据库，现有图像和类别都列在插入表单中，查询方式与 PHP 解决方案 17-2 和 17-3 中更新表单中的图像相同。

提交表单后，处理脚本将执行以下步骤:

  1. 如果已经上传了一个图像，则处理上传，图像的细节存储在images表中，脚本获得新记录的主键。

  2. 如果没有上传图像，但是选择了一个现有的图像，那么脚本从通过$_POST数组提交的值中获取外键。

  3. 在这两种情况下，新的博客文章会作为外键与图像的主键一起插入到blog表中。但是，如果既没有上传图像，也没有从现有图像中选择图像，那么文章将被插入到blog表中，而没有外键。

  4. 最后，脚本检查是否选择了任何类别。如果有，脚本会获取新文章的主键，并将其与article2cat表中所选类别的主键相结合。

如果在任何阶段出现问题，脚本都需要放弃流程的其余部分，重新显示用户的输入。这个剧本很长，所以我将把它分成几个部分。第一步是创建article2cat对照表。

创建交叉引用表

当处理数据库中的多对多关系时，你需要建立一个对照表，如图 18-1 所示。交叉引用表只包含两列，它们被共同声明为表的主键(称为复合主键)。如果你查看图 18-7 ，你会看到article_id和cat_id列都多次包含相同的数字——这在主键中是不可接受的，主键必须是唯一的。但是，在复合主键中，两个值的组合是唯一的。前两个组合1,3和2,1不会在表中的任何地方重复出现，其他组合也不会。

img/332054_5_En_18_Fig7_HTML.jpg

图 18-7

在交叉引用表中，两列一起构成一个复合主键

设置类别和交叉引用表

在ch18文件夹中，您将找到categories.sql，它包含创建categories表和交叉引用表article2cat的 SQL，以及一些示例数据。在 phpMyAdmin 中，选择phpsols数据库，并使用Import选项卡加载categories.sql来创建表格和数据。表 18-1 和 18-2 中列出了这些表的设置。两个数据库表都只有两列。

表 18-2

article2cat 对照表设置

|

名字

|

类型

|

长度/值

|

属性

|

空

|

索引

|

阿奇

| | --- | --- | --- | --- | --- | --- | --- | | article_id | INT |   | UNSIGNED | 取消选择 | PRIMARY |   | | cat_id | INT |   | UNSIGNED | 取消选择 | PRIMARY |   |

表 18-1

类别表的设置

|

名字

|

类型

|

长度/值

|

属性

|

空

|

索引

|

A_I

| | --- | --- | --- | --- | --- | --- | --- | | cat_id | INT |   | UNSIGNED | 取消选择 | PRIMARY | 挑选 | | category | VARCHAR | Twenty |   | 取消选择 |   |   |

关于对照表的定义，重要的一点是两列都设置为主键，并且两列都没有选中A_I ( AUTO_INCREMENT)复选框。

Caution

若要创建复合主键，必须同时将两列声明为主键。如果您错误地只声明了一个主键，数据库会阻止您在以后添加第二个主键。您必须从单个列中删除主键索引，然后将其重新应用于两个列。这两列的组合被视为主键。

获取上传图像的文件名

这个脚本使用了第九章的Upload类，但是这个类需要稍微调整一下，因为上传文件的文件名被合并到了$messages属性中。

PHP 解决方案 18-2:改进上传类

这个 PHP 解决方案修改了第九章中的Upload类，创建了一个新的受保护属性来存储成功上传的文件的名称，并使用一个公共方法来检索名称数组。

  1. 打开Php8Solutions/File文件夹中的Upload.php。或者，从ch18/Php8Solutions/File文件夹中复制Upload.php并保存在php8sols站点根目录下的Php8Solutions/File中。

  2. 将以下行添加到文件顶部的属性列表中:

protected $filenames = [];

这将名为$filenames的受保护属性初始化为一个空数组。

  1. 修改moveFile()方法，如果文件上传成功，将修改后的文件名添加到$filenames属性中。新代码以粗体突出显示:

    protected function moveFile($file) {
    $filename = $this->newName ?? $file['name'];
    $success = move_uploaded_file($file['tmp_name'], $this->destination . $filename);
    if ($success) {
        // add the amended filename to the array of uploaded files
        $this->filenames[] = $filename;
        $result = $file['name'] . ' was uploaded successfully';
        if (!is_null($this->newName)) {
            $result .= ', and was renamed ' . $this->newName;
        }
        $this->messages[] = $result;
    } else {
        $this->messages[] = 'Could not upload ' . $file['name'];
    }
}

仅当文件成功移动到目标文件夹时，该名称才会添加到$filenames数组中。

  1. 添加一个公共方法来返回存储在$filenames属性中的值。代码如下所示:

    public function getFilenames() {
    return $this->filenames;
}

将这些代码放在类定义中的什么地方并不重要，但是将所有公共方法放在一起是一种常见的做法。

  1. 保存Upload.php。如果你需要检查你的代码，将它与ch18/Php8Solutions/File文件夹中的Upload_01.php进行比较。

调整插入表单以处理多个表格

您在第十五章中创建的博客文章插入表单已经包含了在blog表中插入大部分细节所需的代码。与其从头开始，不如修改现有页面。目前，页面只包含标题的文本输入字段和文章的文本区域。

您需要为类别添加一个多选<select>列表，为现有图像添加一个下拉<select>菜单。

为了防止用户在上传新图像的同时选择现有图像，一个复选框和 JavaScript 控制相关输入字段的显示。选中该复选框将禁用现有图像的下拉菜单，并显示新图像和标题的输入字段。取消选中该复选框会隐藏和禁用文件和标题字段，并重新启用下拉菜单。如果 JavaScript 被禁用，上传新图像和标题的选项将被隐藏。

Note

为了节省篇幅，本章剩余的大多数 PHP 解决方案只给出了 MySQLi 的详细说明。PDO 版本的结构和 PHP 逻辑是相同的。唯一的区别在于用于提交 SQL 查询和显示结果的命令。完整注释的 PDO 文件在ch18文件夹中。

PHP 解决方案 18-3:添加类别和图像输入字段

这个 PHP 解决方案通过添加类别和图像的输入字段，开始修改第十五章中的博客条目插入表单。

  1. 在admin文件夹中，找到并打开您在第十五章中创建的blog_insert_mysqli.php版本。或者，将blog_insert_mysqli_01.php从ch18文件夹复制到admin文件夹，并从文件名中删除_01。

  2. 当页面首次加载时，类别和现有图像的<select>元素需要查询数据库，因此您需要将连接脚本和数据库连接移到检查表单是否已提交的条件语句之外。找到以粗体突出显示的行:

    if (isset($_POST['insert'])) {
    require_once '../includes/connection.php';
    // initialize flag
    $OK = false;
    // create database connection
$conn = dbConnect('write');

将它们移出条件语句，并包含utility_funcs.php，如下所示:

  1. 页面主体中的表单需要能够上传文件，因此您需要将enctype属性添加到开始的<form>标签，如下所示:
require_once '../includes/connection.php';
require_once '../includes/utility_funcs.php';
// create database connection
$conn = dbConnect('write');
if (isset($_POST['insert'])) {
    // initialize flag
    $OK = false;

  1. 如果在尝试上传文件时出现错误(例如，文件太大或者不是图像文件)，插入操作将会暂停。使用与第六章所示相同的技术，修改现有的文本输入字段和文本区域，以重新显示数值。文本输入字段如下所示:

    <input name="title" type="text" id="title" value="<?php if (isset($error)) {
    echo safe($_POST['title']);
} ?>">


<form method="post" action="blog_insert_mysqli.php" enctype="multipart/form-data">

文本区域如下所示:

<textarea name="article" id="article"><?php if (isset($error)) {
     echo safe($_POST['article']);
} ?></textarea>

确保开始和结束的 PHP 标签和 HTML 之间没有间隙。否则，您将在文本输入字段和文本区域中添加不需要的空白。

  1. 新的表单元素位于文本区域和提交按钮之间。首先，为类别的多选<select>列表添加代码。代码如下所示:

    <p>
    <label for="category">Categories:</label>
    <select name="category[]" size="5" multiple id="category">
        <?php
        // get categories
        $getCats = 'SELECT cat_id, category FROM categories ORDER BY category';
        $categories = $conn->query($getCats);
        while ($row = $categories->fetch_assoc()) {
            ?>
            <option value="<?= $row['cat_id'] ?>" <?php
            if (isset($_POST['category']) && in_array($row['cat_id'],
                $_POST['category'])) { echo 'selected';
            } ?>><?= safe($row['category']) ?></option>
        <?php } ?>
    </select>
</p>

为了允许选择多个值，multiple属性被添加到了<select>标签中，size属性被设置为5。这些值需要以数组的形式提交，所以在name属性后面添加了一对方括号。

SQL 查询categories表，一个while循环用主键和类别名填充<option>标签。while循环中的条件语句将selected添加到<option>标签中，以便在insert操作失败时重新显示选定的值。

img/332054_5_En_18_Fig8_HTML.jpg

图 18-8

多选列表从类别表中提取值

  1. 保存blog_insert_mysqli.php并将页面加载到浏览器中。该表单现在应该如图 18-8 所示。

  2. 查看页面的源代码，验证每个类别的主键是否正确嵌入了每个<option>标记的value属性中。你可以将你的代码与ch18文件夹中的blog_insert_mysqli_02.php进行比较。

  3. 接下来，创建<select>下拉菜单，显示已经在数据库中注册的图像。在步骤 5 中插入的代码之后立即添加此代码:

    <p>
    <label for="image_id">Uploaded image:</label>
    <select name="image_id" id="image_id">
        <option value="">Select image</option>
        <?php
        // get the list of images
        $getImages = 'SELECT image_id, filename
                      FROM images ORDER BY filename';
        $images = $conn->query($getImages);
        while ($row = $images->fetch_assoc()) {
            ?>
            <option value="<?= $row['image_id'] ?>"
                <?php
                if (isset($_POST['image_id']) && $row['image_id'] ==
                    $_POST['image_id']) {
                    echo 'selected';
                }
                ?>><?= safe($row['filename']) ?></option>
        <?php } ?>
    </select>
</p>

这创建了另一个SELECT查询来获取存储在images表中的每个图像的主键和文件名。代码现在应该非常熟悉了，所以不需要解释。

  1. 标题的复选框、文件输入字段和文本输入字段位于上一步中的代码和提交按钮之间。代码如下所示:

    <p id="allowUpload">
    <input type="checkbox" name="upload_new" id="upload_new">
    <label for="upload_new">Upload new image</label>
</p>
<p class="optional">
    <label for="image">Select image:</label>
    <input type="file" name="image" id="image">
</p>
<p class="optional">
    <label for="caption">Caption:</label>
    <input name="caption" type="text" id="caption">
</p>

包含复选框的段落被赋予了 ID allowUpload，另外两个段落被赋予了一个名为optional的类。admin.css中的样式规则将这三段的display属性设置为none。

  1. 保存blog_insert_mysqli.php并在浏览器中加载页面。images <select>下拉菜单显示在categories列表的下面，但是您在步骤 9 中插入的三个表单元素是隐藏的。如果浏览器中禁用了 JavaScript，将会显示以下内容。用户可以选择类别和现有的图像，但不能上传新的图像。

如有必要，对照 ch18 文件夹中的blog_insert_mysqli_03.php检查您的代码。

  1. 将ch18文件夹中的toggle_fields.js复制到admin文件夹。该文件包含以下 JavaScript:

    const cbox = document.getElementById('allowUpload');
cbox.style.display = 'block';
const uploadImage = document.getElementById('upload_new');
uploadImage.onclick = function () {
    const image_id = document.getElementById('image_id');
    const image = document.getElementById('image');
    const caption = document.getElementById('caption');
    const sel = uploadImage.checked;
    image_id.disabled = sel;
    image.parentNode.style.display = sel ? 'block' : 'none';
    caption.parentNode.style.display = sel ? 'block' : 'none';
    image.disabled = !sel;
    caption.disabled = !sel;
}

这使用在步骤 8 中插入的元素的 id 来控制它们的显示。如果启用了 JavaScript，页面加载时会自动显示复选框，但标题的文件输入字段和文本输入字段保持隐藏。如果选中该复选框，则禁用现有图像的下拉菜单，并显示隐藏的元素。如果随后取消选中该复选框，下拉菜单将重新启用，并且文件输入字段和标题字段将再次隐藏。

  1. 在结束</body>标签之前用<script>标签将toggle_fields.js链接到blog_insert_mysqli.php，就像这样:

    </form>
      <script src="toggle_fields.js"></script>
</body>

在页面底部添加 JavaScript 可以加速下载和显示。如果将toggle_fields.js中的代码添加到<head>中，它将无法正常工作。

img/332054_5_En_18_Fig9_HTML.jpg

图 18-9

复选框控制文件和标题输入字段的显示

  1. 保存blog_insert_mysqli.php并在浏览器中加载页面。在支持 JavaScript 的浏览器中，复选框应该显示在<select>下拉菜单和提交按钮之间。选中复选框，禁用下拉菜单，显示隐藏字段，如图 18-9 所示。

  2. 取消选中该复选框。文件和标题输入字段被隐藏，下拉菜单被重新启用。如果有必要，你可以用ch18文件夹中的blog_insert_mysqli_04.php和toggle_fields.js来检查你的代码。

我使用 JavaScript 而不是 PHP 来控制文件和标题输入字段的显示，因为 PHP 是一种服务器端语言。PHP 引擎将输出发送到浏览器后，它不再与页面交互，除非您向 web 服务器发送另一个请求。另一方面，JavaScript 在浏览器中工作，所以它能够在本地操作页面内容。JavaScript 也可以与 PHP 结合使用，在后台向 web 服务器发送请求，并且它可以使用结果来刷新页面的一部分，而无需重新加载它——这是一种称为 Ajax 的技术，这超出了本书的范围。

更新后的插入表单现在有了类别和图像的输入字段，但是处理脚本仍然只处理标题的文本输入字段和博客条目的文本区域。

PHP 解决方案 18-4:将数据插入多个表格

该 PHP 解决方案采用blog_insert_mysqli.php中的现有脚本上传新图像(如果需要)，然后按照图 18-6 中概述的决策链将数据插入images、blog和article2cat表中。它假设你已经建立了article2cat交叉引用表，并完成了 PHP 解决方案 18-2 和 18-3。

不要试图匆匆读完这一部分。代码很长，但是它集合了您以前学过的许多技术。

Note

如果您使用的是 PDO，这个 PHP 解决方案后面有一个单独的部分描述了代码中的主要差异。

  1. 您在 PHP 解决方案 18-2 中更新的Upload类使用了一个名称空间，因此您需要在脚本的顶层导入它。在blog_insert_mysqli.php顶部的开始 PHP 标签后立即添加这一行:

  2. 初始化准备好的语句后，立即插入以下条件语句来处理图像(如果已经上载或选择了图像):

    // initialize prepared statement
$stmt = $conn->stmt_init();
// if a file has been uploaded, process it
if(isset($_POST['upload_new']) && $_FILES['image']['error'] == 0) {
    $imageOK = false;
    require_once '../Php8Solutions/File/Upload.php';
    $loader = new Upload('image', '../img/');
    $names = $loader->getFilenames();
    // $names will be an empty array if the upload failed
    if ($names) {
        $sql = 'INSERT INTO images (filename, caption) VALUES (?, ?)';
        if ($stmt->prepare($sql)) {
            $stmt->bind_param('ss', $names[0], $_POST['caption']);
            $stmt->execute();
            $imageOK = $stmt->affected_rows;
        }
    }
    // get the image's primary key or find out what went wrong
    if ($imageOK) {
        $image_id = $stmt->insert_id;
    } else {
        $imageError = implode(' ', $loader->getMessages());
    }
} elseif (!empty($_POST['image_id'])) {
    // get the primary key of a previously uploaded image
    $image_id = $_POST['image_id'];
}
// create SQL
$sql = 'INSERT INTO blog (title, article) VALUES(?, ?)';


use Php8Solutions\File\Upload;

首先检查$_POST['upload_new']是否已经设置。如第六章所述，只有当复选框被选中时，它才会包含在$_POST数组中。因此，如果复选框没有被选中，那么条件失败，取而代之的是测试底部的elseif子句。elseif子句检查$_POST['image_id']的存在。如果存在且不为空，则意味着已经从下拉菜单中选择了一个现有图像，该值存储在$image_id中。

如果两个测试都失败，则既没有上传图像，也没有从下拉菜单中选择图像。该脚本稍后在为blog表准备INSERT查询时会考虑到这一点，允许您创建一个没有图像的博客条目。

但是，如果$_POST['upload_new']存在，则复选框已被选中，图像可能已被上传。为了确保这一点，条件语句还会检查$_FILES['image']['error']的值。正如您在第九章中了解到的，错误代码0表示上传成功。任何其他错误代码意味着上传失败或没有选择文件。

假设一个文件已经从表单成功上传，条件语句包括Upload类定义并创建一个名为$loader的对象，传递给它文件输入字段的名称，并将目标文件夹设置为images。为了避免代码复杂化，我没有对Upload构造函数使用三个可选参数。因此，将使用默认的最大大小和 MIME 类型，并且具有重复文件名的图像将被重命名。

您在 PHP 解决方案 18-2 中对Upload类所做的更改会将上传文件的名称添加到$filenames属性中，前提是该文件已成功移动到目标文件夹中。getFilenames()方法检索$filenames属性的内容，并将结果分配给$names。

如果文件被成功移动，其文件名将被存储为$names数组的第一个元素。因此，如果$names包含一个值，您可以安全地继续执行INSERT查询，该查询将$names[0]和$_POST['caption']的值作为字符串绑定到准备好的语句。

执行完语句后，affected_rows属性会重置$imageOK的值。如果INSERT查询成功，$imageOK为1，作为true处理。

如果图像细节被插入到images表中，那么准备好的语句的insert_id属性将检索新记录的主键，并将其存储在$image_id中。在运行任何其他 SQL 查询之前，必须访问insert_id属性，因为它包含最近查询的主键。

然而，如果$imageOK仍然为假，else块调用上传对象的getMessages()方法，并将结果分配给$imageError。getMessages()方法返回一个数组，因此implode()函数用于将数组元素连接成一个字符串。失败最可能的原因是文件太大或者 MIME 类型不正确。

  1. 只要图片上传没有失败，流程的下一步就是将博客条目插入到blog表中。INSERT查询的形式取决于图像是否与博客条目相关联。如果是，$image_id存在，需要作为外键插入到blog表中。否则，可以使用原始查询。

像这样修改原始查询:

// insert blog details only if there hasn't been an image upload error
if (!isset($imageError)) {
    // if $image_id has been set, insert it as a foreign key
    if (isset($image_id)) {
        $sql = 'INSERT INTO blog (image_id, title, article) VALUES(?, ?, ?)';
        if ($stmt->prepare($sql)) {
            $stmt->bind_param('iss', $image_id, $_POST['title'], $_POST['article']);
            $stmt->execute();
        }
    } else {
        // create SQL
        $sql = 'INSERT INTO blog (title, article)
                   VALUES(?, ?)';
        if ($stmt->prepare($sql)) {
            // bind parameters and execute statement
            $stmt->bind_param('ss', $_POST['title'], $_POST['article']);
            $stmt->execute();
        }
    }
    if ($stmt->affected_rows > 0) {
        $OK = true;
    }
}

这一整段代码被包装在一个条件语句中，该语句检查$imageError是否存在。如果是的话，插入新的博客条目就没有意义了，所以整个代码块都被忽略了。

然而，如果$imageError不存在，嵌套的条件语句根据$image_id是否存在准备不同的INSERT查询，然后执行已经准备好的查询。

检查affected_rows属性的条件语句被移出else块，这样它就可以应用于任何一个INSERT查询。

  1. 流程的下一阶段将值插入到article2cat交叉引用表中。代码紧跟在上一个步骤的代码之后，如下所示:

    // if the blog entry was inserted successfully, check for categories
if ($OK && isset($_POST['category'])) {
    // get the article's primary key
    $article_id = $stmt->insert_id;
    foreach ($_POST['category'] as $cat_id) {
        if (is_numeric($cat_id)) {
            $values[] = "($article_id, " . (int) $cat_id . ')';
        }
    }
    if ($values) {
        $sql = 'INSERT INTO article2cat (article_id, cat_id)
                   VALUES ' . implode(',', $values);
        // execute the query and get error message if it fails
        if (!$conn->query($sql)) {
            $catError = $conn->error;
        }
    }
}

$OK的值由在blog表中插入数据的查询的affected_rows属性决定，只有选择了任何类别，多选<select>列表才会包含在$_POST数组中。因此，只有当数据成功插入到blog表中，并且在表单中至少选择了一个类别时，这个代码块才会运行。它首先从准备好的语句的insert_id属性中获取插入操作的主键，并将其赋给$article_id。

表单将类别值作为数组提交。foreach循环检查$_POST['category']中的每个值。如果该值是数字，则执行下面一行:

$values[] = "($article_id, " . (int) $cat_id . ')';

这将创建一个包含两个主键$article_id和$cat_id的字符串，用逗号分隔，并用一对括号括起来。(int)造型操作符确保$cat_id是一个整数。结果被分配给一个名为$values的数组。例如，如果$article_id是10并且$cat_id是4，则分配给数组的结果字符串是(10, 4)。

如果$values包含任何元素，implode()将其转换为逗号分隔的字符串，并将其附加到 SQL 查询中。例如，如果选择了类别2、4和5，结果查询如下所示:

INSERT INTO article2cat (article_id, cat_id)
VALUES (10, 2),(10, 4),(10, 5)

正如在第十五章的“回顾四个基本的 SQL 命令”中所解释的，这就是如何用一个INSERT查询插入多行。

因为$article_id来自可靠的来源，并且已经检查了$cat_id的数据类型，所以在 SQL 查询中直接使用这些变量是安全的，不需要使用准备好的语句。使用query()方法执行查询。如果失败，连接对象的错误属性将存储在$catError中。

  1. 代码的最后一部分处理成功和错误消息的重定向。修改后的代码如下所示:

    // redirect if successful or display error
if ($OK && !isset($imageError) && !isset($catError)) {
    header('Location: http://localhost/php8sols/admin/blog_list_mysqli.php');
    exit;
} else {
    $error = $stmt->error;
    if (isset($imageError)) {
        $error .= ' ' . $imageError;
    }
    if (isset($catError)) {
        $error .= ' ' . $catError;
    }
}

控制重定向的条件现在确保了$imageError和$catError不存在。如果有，该值将连接到原始的$error，其中包含来自准备好的语句对象的任何错误消息。

  1. 保存blog_insert_mysqli.php并在浏览器中测试。尝试上传太大的图像或错误 MIME 类型的文件。表单应该重新显示一条错误消息，并保留博客的详细信息。也可以尝试插入带有或不带有图片和/或类别的博客条目。您现在有了一个多用途的插入表单。

如果您没有合适的图像上传，请使用phpsols images文件夹中的图像。Upload类重命名它们以避免覆盖现有的文件。

您可以对照ch18文件夹中的blog_insert_mysqli_05.php来检查您的代码。

PDO 版本的主要区别

最终的 PDO 版本可以在ch18文件夹的blog_insert_pdo_05.php中找到。它遵循与 MySQLi 版本相同的基本结构和逻辑，但是在将值插入数据库的方式上有一些重要的不同。

步骤 2 中的代码严格遵循 MySQLi 版本，但是使用了命名占位符而不是匿名占位符。为了获得受影响的行数，PDO 在 statement 对象上使用了rowCount()方法。最近插入操作的主键是使用 connection 对象上的lastInsertId()方法获得的。像 MySQLi insert_id属性一样，您需要在执行完INSERT查询后立即访问它。

最大的变化是第 3 步中的代码，它将细节插入到 blog 表中。因为 PDO 可以使用bindValue()将一个null值插入到一个列中，所以只需要一条准备好的语句。步骤 3 的 PDO 代码如下所示:

// insert blog details only if there hasn't been an image upload error
if (!isset($imageError)) {
    // create SQL
    $sql = 'INSERT INTO blog (image_id, title, article)
                VALUES(:image_id, :title, :article)';
    // prepare the statement
    $stmt = $conn->prepare($sql);
    // bind the parameters
    // if $image_id exists, use it
    if (isset($image_id)) {
        $stmt->bindParam(':image_id', $image_id, PDO::PARAM_INT);
    } else {
        // set image_id to NULL
        $stmt->bindValue(':image_id', NULL, PDO::PARAM_NULL);
    }
    $stmt->bindParam(':title', $_POST['title'], PDO::PARAM_STR);
    $stmt->bindParam(':article', $_POST['article'], PDO::PARAM_STR);
    // execute and get number of affected rows
    $stmt->execute();
    $OK = $stmt->rowCount();
}

如果图像已经上传，以粗体突出显示的条件语句将$image_id的值绑定到命名的:image_id占位符。但是如果没有上传图像，bindValue()将该值设置为NULL。

在第 4 步中，PDO 版本使用exec()而不是query()将值插入到article2cat表中。exec()方法执行 SQL 查询并返回受影响的行数，因此当不需要准备好的语句时，它应该与INSERT、UPDATE和DELETE查询一起使用。

另一个重要的区别是在出现问题时构建错误消息的代码。因为创建和准备报表在 PDO 中是一步到位的过程，所以如果出现问题，报表对象可能不存在。如果没有语句，对errorInfo()的调用将是null。因此，代码使用 null 合并操作符从数据库连接对象获取错误消息。还需要将$error初始化为一个空字符串，将各种消息连接起来，就像这样:

// redirect if successful or display error
if ($OK && !isset($imageError) && !isset($catError)) {
    header('Location: http://localhost/php8sols/admin/blog_list_pdo.php');
    exit;
} else {
    $error = ";
    $error .= $stmt->errorInfo()[2] ?? $conn->errorInfo()[2];
    if (isset($imageError)) {
        $error .= ' ' . $imageError;
    }
    if (isset($catError)) {
        $error .= ' ' . $catError;
    }
}

更新和删除多个表中的记录

添加了categories和article2cat表意味着上一章 PHP 解决方案 17-2 和 17-3 中对blog_update_mysqli.php和blog_update_pdo.php的更改不再充分涵盖phpsols数据库中的外键关系。除了修改更新表单之外，您还需要创建脚本来删除记录，而不破坏数据库的参照完整性。

更新交叉引用表中的记录

交叉引用表中的每条记录只包含一个复合主键。通常情况下，主键不应该被改变。此外，它们必须是独特的。这给更新article2cat表带来了问题。如果在更新博客条目时没有对所选类别进行更改，则不需要更新交叉引用表。但是，如果类别发生变化，您需要确定要删除哪些交叉引用以及要插入哪些新的交叉引用。

一个简单的解决方案是删除所有现有的交叉引用并再次插入所选的类别，而不是纠结于是否进行了任何更改。如果没有进行任何更改，您只需再次插入相同的内容。

PHP 解决方案 18-5:向更新表单添加类别

这个 PHP 解决方案修正了上一章 PHP 解决方案 17-2 中的blog_update_mysqli.php,允许你更新与博客条目相关的类别。为了保持结构简单，对与条目相关联的图像的唯一改变是选择不同的现有图像或者根本不选择图像。

  1. 继续使用 PHP 解决方案 17-2 中的blog_update_mysqli.php。或者，从ch18文件夹中复制blog_update_mysqli_04.php并作为blog_update_mysqli.php保存在admin文件夹中。

  2. 当页面第一次加载时，您需要运行第二个查询来获取与博客条目相关的类别。将以下突出显示的代码添加到获取所选记录详细信息的条件语句中:

    $stmt->free_result();
// get categories associated with the article
$sql = 'SELECT cat_id FROM article2cat
        WHERE article_id = ?';
if ($stmt->prepare($sql)) {
    $stmt->bind_param('i', $_GET['article_id']);
    $OK = $stmt->execute();
    $stmt->bind_result($cat_id);
    // loop through the results to store them in an array
    $selected_categories = [];
    while ($stmt->fetch()) {
        $selected_categories[] = $cat_id;
    }
}

该查询从交叉引用表中匹配所选博客条目主键的所有记录中选择cat_id。结果被绑定到$cat_id，一个while循环将这些值提取到一个名为$selected_categories的数组中。

  1. 在 HTML 页面的主体中，在文本区域和显示图像列表的<select>下拉菜单之间添加一个多选<select>列表。使用另一个 SQL 查询来填充它，如下所示:

    <p>
    <label for="category">Categories:</label>
    <select name="category[]" size="5" multiple id="category">
        <?php
        // get categories
        $getCats = 'SELECT cat_id, category FROM categories
                           ORDER BY category';
        $categories = $conn->query($getCats);
        while ($row = $categories->fetch_assoc()) {
            ?>
            <option value="<?= $row['cat_id'] ?>" <?php
            if (isset($selected_categories) &&
                in_array($row['cat_id'], $selected_categories)) {
                echo 'selected';
            } ?>><?= safe($row['category']) ?></option>
        <?php } ?>
    </select>
</p>

while循环通过在value属性中插入cat_id并显示开始和结束标签之间的类别来构建每个<option>标签。如果cat_id在$selected_categories数组中，selected被插入到<option>标签中。这将选择已经与博客条目关联的类别。

  1. 保存blog_update_mysqli.php并选择blog_list_mysqli.php中的EDIT链接之一，以确保多选列表中填充了类别。如果您在 PHP 解决方案 18-4 中插入了一个新条目，那么您与该条目相关联的类别应该被选中，如下面的屏幕截图所示。

img/332054_5_En_18_Figa_HTML.jpg

如有必要，您可以对照 ch18 文件夹中的blog_update_mysqli_05.php检查您的代码。PDO 版本出现在blog_update_pdo_05.php中。

  1. 接下来，您需要编辑提交表单时更新记录的代码部分。新代码首先删除交叉引用表中匹配article_id的所有条目，然后插入在更新表单中选择的值。行内注释指出了为节省空间而省略的现有代码:

    // if form has been submitted, update record
if (isset($_POST ['update'])) {
    // prepare update query
    if (!empty($_POST['image_id'])) {
        // existing code omitted
    } else {
        // existing code omitted
            $done = $stmt->execute();
        }
    }
    // delete existing values in the cross-reference table
    $sql = 'DELETE FROM article2cat WHERE article_id = ?';
    if ($stmt->prepare($sql)) {
        $stmt->bind_param('i', $_POST['article_id']);
        $done = $stmt->execute();
    }
    // insert the new values in articles2cat
    if (isset($_POST['category']) && is_numeric($_POST['article_id'])) {
        $article_id = (int) $_POST['article_id'];
        foreach ($_POST['category'] as $cat_id) {
            $values[] = "($article_id, " . (int) $cat_id . ')';
        }
        if ($values) {
            $sql = 'INSERT INTO article2cat (article_id, cat_id)
                      VALUES ' . implode(',', $values);
            $done = $conn->query($sql);
        }
    }
}

插入在更新表单中选择的值的代码与 PHP 解决方案 18-4 的步骤 4 中的代码相同。需要注意的关键点是，它使用了一个INSERT查询，而不是UPDATE。原始值已被删除，所以您要重新添加它们。

  1. 保存blog_update_mysqli.php并通过更新blog表中的现有记录进行测试。如果有必要，你可以对照ch18文件夹中的blog_update_mysqli_06.php来检查你的代码。PDO 版本见于blog_update_pdo_06.php。

将多个查询视为事务中的一个块

前面的 PHP 解决方案对信任要求很高。更新序列包括三个独立的查询:更新blog表，删除article2cat表中的引用，并插入新的引用。如果其中任何一个失败，$done将被设置为false；但是如果下一次成功，它将被重置为true。您可能很容易只得到部分更新，但是除非是一系列查询的最后一部分失败，否则您不会知道。

一种解决方案可能是运行一系列条件语句，如果前面的查询失败，则阻止任何进一步的执行。问题是，您最终仍然会得到部分更新。当更新多个表中的连接记录时，需要将整个序列视为一个块。如果一部分失败，整个序列都会失败。只有当更新序列的所有部分都成功时，才会处理更新序列。将多个查询视为一个统一的块在 SQL 中称为事务。在 MySQLi 和 PDO 中实现交易都很简单。

Note

要在 MySQL 和 MariaDB 中使用事务，必须使用 InnoDB 存储引擎。

在 MySQLi 中使用事务

默认情况下，MySQL 和 MariaDB 在自动提交模式下工作。换句话说，SQL 查询会立即执行。要使用事务，您需要关闭自动提交模式，然后像这样调用数据库连接对象上的begin_transaction()方法(假设$conn是数据库连接):

$conn->autocommit(false);
$conn->begin_transaction();

然后正常运行 SQL 查询序列，根据查询是否成功执行，将变量设置为true或false。如果检测到任何错误，您可以在序列结束时将所有表回滚到它们的原始状态。否则，您可以提交事务，将序列作为单个块进行处理，如下所示:

if ($trans_error) {
    $conn->rollback();
} else {
    $conn->commit();
}
使用 PDO 的交易

PDO 也在自动提交模式下工作。在数据库连接对象上调用beginTransaction()方法会关闭自动提交模式。PHP 8 一遇到问题就会自动抛出异常，所以没有必要使用变量来跟踪单个查询的成功。只需使用一个catch块将表回滚到原始状态。基本结构是这样的:

try {
    $conn->beginTransaction();
    // run sequence of SQL queries
    // commit the transaction if no problems have been encountered
    $done = $conn->commit();
    // catch the exception if there’s a problem
} catch (Exception $e) {
    // roll back to the original state and get the errormessage
    $conn->rollBack();
    $trans_error = $e->getMessage();
}

Caution

PHP 中的函数和方法名是不区分大小写的，所以对于 MySQLi 和 PDO 来说，rollBack()和rollback()同样是可以接受的。然而，在begin_transaction() (MySQLi)和beginTransaction() (PDO)之间有一个微妙的区别。PDO 方法没有下划线。

在 PHP 8 之前，PDO 的默认错误模式是静默的。如果您的服务器运行的是旧版本的 PHP，您需要明确设置错误模式，以便在遇到类似这样的问题时抛出异常:

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
PHP 解决方案 18-6:将表格转换为 InnoDB 存储引擎

这个 PHP 解决方案展示了如何将一个表格从 MyISAM 转换成 InnoDB。如果您计划将表上传到您的远程服务器，它还必须支持 InnoDB(参见 PHP 解决方案 18-1)。

img/332054_5_En_18_Fig10_HTML.jpg

图 18-10

在 phpMyAdmin 中，更改表的存储引擎非常容易

  1. 在 phpMyAdmin 中选择phpsols数据库，然后选择article2cat表。

  2. 单击屏幕右上角的操作选项卡。

  3. 在“表选项”部分,“存储引擎”字段报告表当前使用的引擎。如果显示 MyISAM，从下拉菜单中选择 InnoDB，如图 18-10 所示。

  4. 点击Go。改变存储引擎就是这么简单！

Note

每个表都需要单独转换。不能在一次操作中更改数据库中的所有表。

PHP 解决方案 18-7:在事务中包装更新序列(MySQLi)

这个 PHP 解决方案改进了blog_update_mysqli.php中的脚本，将更新blog和article2cat表的 SQL 查询序列封装在一个事务中，如果序列的任何部分失败，就将数据库回滚到原始状态。

  1. 如有必要，将blog和article2cat表的存储引擎转换为 InnoDB，如前面的 PHP 解决方案所述。

  2. 继续使用 PHP 解决方案 18-5 中的blog_update_mysqli.php和blog_list_mysqli.php。或者，将blog_update_mysqli_06.php和blog_list_mysqli_04.php从ch18文件夹复制到php8sols站点根目录下的admin文件夹，并删除文件名中的数字。

  3. 在blog_update_mysqli.php的顶部初始化一个空数组来存储错误信息:

  4. 关闭自动提交模式，并在运行更新查询序列的条件语句的开头开始一个事务，如下所示:

    // if form has been submitted, update record
if (isset($_POST ['update'])) {
   // set autocommit to off
   $conn->autocommit(false);
   $conn->begin_transaction();
   // prepare update query

  5. 在更新 blog 表的查询之后，添加一个条件语句，以便在查询失败时将任何错误消息添加到$trans_error 数组中。为了节省空间，省略了一些现有代码:

    if (!empty($_POST['image_id'])) {
    // existing code omitted
        $done = $stmt->execute();
    }
} else {
    // existing code omitted
       $done = $stmt->execute();
    }
}
if (!$done) {
    $trans_error[] = $stmt->error;
}

  6. 添加一个类似的条件语句，以捕获因删除交叉引用表中的现有值而产生的任何错误消息:

    // delete existing values in the cross-reference table
$sql = 'DELETE FROM article2cat WHERE article_id = ?';
if ($stmt->prepare($sql)) {
    $stmt->bind_param('i', $_POST['article_id']);
    $done = $stmt->execute();
    if (!$done) {
        $trans_error[] = $stmt->error;
    }
}

  7. 从在article2cat表中插入更新值中捕获任何错误消息的代码需要稍有不同，因为它使用了query()方法，而不是准备好的语句。您需要像这样访问数据库连接对象的error属性，而不是语句对象的error属性:

    if ($values) {
    $sql = 'INSERT INTO article2cat (article_id, cat_id)
                VALUES ' . implode(',', $values);
    $done = $conn->query($sql);
    if (!$done) {
        $trans_error[] = $conn->error;
    }
}

  8. 在一系列查询之后，使用条件语句回滚或提交事务，如下所示(代码位于条件语句中，当单击 Update 按钮时，条件语句将运行脚本):

    if ($trans_error) {
    $conn->rollback();
    $done = false;
} else {
    $conn->commit();
}


$trans_error = [];

如果$trans_error包含任何错误信息，有必要将$done明确设置为false。这是因为$done将被任何在事务之外成功的查询设置为true。

  1. 需要修改重定向页面的条件语句来处理事务。添加以粗体突出显示的新代码:

    // redirect page after updating or if $_GET['article_id']) not defined
if (($done || $trans_error) || (!$_POST && !isset($_GET['article_id']))) {
    $url = 'http://localhost/php8sols/admin/blog_list_mysqli.php';
    if ($done) {
        $url .= '?updated=true';
    } elseif ($trans_error) {
        $url .= '?trans_error=' . serialize($trans_error);
    }
    header("Location: $url");
    exit;
}

条件现在被分组在括号内，以确保它们被正确解释。第一对检查$done或$trans_error是否等于true。通过检查$_POST数组是否为空，最终条件变得更加具体。这是必要的，因为点击更新按钮后，!isset($_GET['article_id'])总是true。

如果$trans_error包含任何错误消息，那么它等同于true，因此一个查询字符串被附加到重定向位置。因为$trans_error是一个数组，所以在连接到查询字符串之前，需要将它传递给serialize()函数。这会将数组转换为字符串，该字符串可以转换回其原始格式。

  1. 最后的改变是在blog_list_mysqli.php中表格上方的 PHP 块中。添加粗体代码，以便在更新失败时显示任何错误消息:

    if (isset($_GET['updated'])) {
    echo '<p>Record updated</p>';
} elseif (isset($_GET['trans_error'])) {
    $trans_error = unserialize($_GET['trans_error']);
    echo "<p>Can't update record because of the following error(s):</p>";
    echo '<ul>';
    foreach ($trans_error as $item) {
       echo '<li>' . safe($item) . '</li>';
    }
    echo '</ul>';
}

unserialize()函数逆转了serialize()的效果，将错误消息转换回一个数组，然后在foreach循环中显示。

img/332054_5_En_18_Fig11_HTML.jpg

图 18-11

由于列名中的错误，更新失败

  1. 保存blog_update_mysqli.php和blog_list_mysqli.php，并更新现有记录。脚本应该和以前一样工作。

  2. 在blog_update_mysqli.php中的 SQL 中引入一些故意的错误，并再次测试。这一次，当您返回到blog_list_mysqli.php时，您应该会看到类似于图 18-11 的一系列错误信息。

  3. 单击您刚刚尝试更新的记录的EDIT链接，并确认没有任何值发生变化。你可以对照ch18文件夹中的blog_update_mysqli_07.php和blog_list_mysqli_05.php来检查你的代码。

PHP 解决方案 18-8:在事务中包装更新序列(PDO)

这个 PHP 解决方案改进了blog_update_pdo.php中的脚本，将更新blog和article2cat表的 SQL 查询序列封装在一个事务中，如果序列的任何部分失败，就将数据库回滚到原始状态。

  1. 如有必要，将blog和article2cat表的存储引擎转换为 InnoDB，如 PHP 解决方案 18-6 所述。

  2. 继续使用 PHP 解决方案 18-5 中的blog_update_pdo.php和blog_list_pdo.php。或者，将blog_update_pdo_06.php和blog_list_pdo_04.php从ch18文件夹复制到php8sols站点根目录下的admin文件夹，并删除文件名中的数字。

  3. 初始化页面顶部的一个变量以跟踪事务，并将其值设置为false:

$trans_error = false;

Note

PHP 8 在 PDO 中遇到错误时会自动抛出异常，所以只有当您的服务器运行的是旧版本的 PHP 时，才需要执行以下步骤。如果您运行的是 PHP 8 或更高版本，请跳到步骤 5。

  1. 在运行查询序列以更新blog和article2cat表的条件语句中，设置 PDO 在遇到类似这样的问题时抛出异常:

    if (isset($_POST['update'])) {
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // prepare update query
    $sql = 'UPDATE blog SET image_id = ?, title = ?, article = ?
            WHERE article_id = ?';

  2. 将运行更新查询的所有代码包装在一个try / catch块中，并在try块的开头开始一个事务，如下所示:

    if(isset($_POST['update'])) {
    try {
        $conn->beginTransaction();
        // prepare update query
        // other database queries omitted
    } catch (Exception $e) {
        $conn->rollBack();
        $trans_error = $e->getMessage();
    }
}

  3. 在现有代码中，执行每个查询的返回值都设置为$done。这不再是必要的，因为我们正在使用一个事务。我们将使用$done作为成功提交事务的返回值。找到以下行(它们在第 53、57 和 69 行周围):

    $done = $stmt->execute();
$done = $stmt->execute([$_POST['article_id']]);
$done = $conn->exec($sql);

将它们更改为:

  1. 紧接在catch块之前，添加粗体代码以提交事务:

        $done = $conn->commit();
} catch (Exception $e) {
    $conn->rollBack();
    $trans_error = $e->getMessage();
}

  2. 需要修改重定向页面的条件语句来处理事务。添加以粗体突出显示的新代码:

    // redirect page after updating or if $_GET['article_id'] not defined
if (($done || $trans_error) || (!$_POST && !isset($_GET['article_id']))) {
    $url = 'http://localhost/php8sols/admin/blog_list_pdo.php';
    if ($done) {
        $url .= '?updated=true';
    } elseif ($trans_error) {
        $url .= "?trans_error=$trans_error";
    }
    header("Location: $url");
    exit;
}


$stmt->execute();
$stmt->execute([$_POST['article_id']]);
$conn->exec($sql);

条件现在被分组在括号内，以确保它们被正确解释。第一对检查$done或$trans_error是否等于true。通过检查$_POST数组是否为空，最终条件变得更加具体。这是必要的，因为点击更新按钮后，!isset($_GET['article_id'])总是true。

如果$trans_error包含任何错误消息，那么它等同于true，因此一个查询字符串被附加到重定向位置。

  1. 最后的改变是在blog_list_pdo.php中表格上方的 PHP 块中。添加粗体代码，以便在更新失败时显示任何错误消息:

    if (isset($_GET['updated'])) {
    echo '<p>Record updated</p>';
} elseif (isset($_GET['trans_error'])) {
    echo "Can't update record because of the following error: ";
    echo safe($_GET['trans_error']) . '</p>';
}

PDO 一遇到错误就抛出异常，所以即使有多个错误，也只会有一个错误消息。

  1. 保存blog_update_pdo.php和blog_list_pdo.php，并更新现有记录。脚本应该和以前一样工作。

  2. 在blog_update_pdo.php中的一个更新查询中故意引入一个错误，并再次测试。这一次，当您返回到blog_list_pdo.php时，您将看到错误消息。

  3. 单击您刚刚尝试更新的记录的EDIT链接，并确认没有任何值发生变化。你可以对照ch18文件夹中的blog_update_pdo_07.php和blog_list_pdo_05.php来检查你的代码。

Tip

在一系列查询只有在满足特定条件的情况下才应该被处理的情况下，事务是必不可少的。例如，在金融数据库中，只有在有足够的资金可用的情况下，才能进行资金转移。

删除时保持引用完整性

在 PHP 解决方案 18-5 中，当您删除交叉引用表中的记录时，无需担心引用完整性，因为存储在每个记录中的值都是外键。每个记录只是引用存储在blog和categories表中的主键。参见本章开头的图 18-1 ，从对照表中删除将article_id 2与cat_id 1组合在一起的记录，只是断开了标题为“见习艺妓去购物”的文章与Kyoto类别之间的链接。文章和类别都不受影响。它们都保留在各自的表中。

如果你决定删除文章或类别，情况就大不相同了。如果您从blog表中删除“见习艺妓去购物”这篇文章，所有对article_id 2的引用也必须从交叉引用表中删除。类似地，如果您删除了Kyoto类别，那么所有对cat_id 1的引用都必须从交叉引用表中删除。或者，如果项目的主键作为外键存储在其他地方，则必须停止删除。

最好的方法是通过建立外键约束。为此，相关表必须使用 InnoDB 存储引擎。如果您使用的是 MySQL 或 MariaDB 5.5 或更高版本，InnoDB 是默认的。此外，本书附带的所有.sql文件都选择了 InnoDB 引擎。但是，如果您有使用 MyISAM 存储引擎创建的现有表，您需要在建立外键约束之前转换它们(参见 PHP 解决方案 18-6)。

PHP 解决方案 18-9:设置外键约束

这个 PHP 解决方案描述了如何在 phpMyAdmin 中的article2cat、blog和categories表之间设置外键约束。外键约束必须始终在子表中定义。在本例中，子表是article2cat，因为它将其他表中的article_id和cat_id主键存储为外键。

img/332054_5_En_18_Fig12_HTML.jpg

图 18-12

外键约束是在 phpMyAdmin 的关系视图中定义的

  1. 在 phpMyAdmin 中选择article2cat表，然后单击 Structure 选项卡。

  2. 单击结构表上方的关系视图(图 18-12 中的圆圈)(在 phpMyAdmin 的旧版本中，它是结构表下方的链接)。

  3. 打开的屏幕是您定义外键约束的地方。将“约束名称”字段留空。phpMyAdmin 将自动为约束生成一个名称。

  4. 外键约束只能在已索引的列上设置。article2cat中的article_id和cat_id列是表的复合主键，所以它们都列在列下拉菜单中。选择文章标识。然后在外键约束(INNODB)下选择以下设置:

    • 数据库:phpsols

    • 表格:博客

    • 列:文章标识

这在父表(blog)中的 article_id 和子表(article2cat)中的 article_id 之间建立了一个约束。

  1. 接下来，您需要决定约束应该如何表现。“删除时”下拉菜单包含以下选项:

    • 级联:当您删除父表中的记录时，子表中的所有相关记录都会被删除。例如，如果删除blog表中主键为article_id 2的记录，那么article2cat表中所有主键为article_id 2的记录都会被自动删除。

    • SET NULL:删除父表中的记录时，子表中的所有相关记录都将外键设置为 NULL。外键列必须接受空值。

    • 无操作:在一些数据库系统中，这允许外键约束检查被延迟。MySQL 会立即执行检查，因此这与 RESTRICT 具有相同的效果。

    • RESTRICT:如果子表中仍然存在相关记录，这可以防止删除父表中的记录。

Note

同样的选项也适用于ON UPDATE。除了RESTRICT之外，它们的意义有限，因为只有在特殊情况下才应该更改记录的主键。ON UPDATE RESTRICT不仅阻止对父表中主键的更改；它还拒绝在子表中插入或更新任何可能导致在父表中没有匹配的外键值的内容。

如果是对照表，CASCADE是合理的选择。如果您决定删除父表中的记录，您希望同时删除对该记录的所有交叉引用。然而，为了演示外键约束的默认行为，为ON DELETE和ON UPDATE都选择RESTRICT。

img/332054_5_En_18_Fig13_HTML.jpg

图 18-13

为交叉引用表设置外键约束

  1. 单击添加约束链接，使用以下设置为 cat_id 建立外键约束:

    • 数据库:phpsols

    • 表格:类别

    • 列:cat_id

  2. 将ON DELETE和ON UPDATE设置为RESTRICT。设置应该如图 18-13 所示。然后点击保存按钮。

Note

phpMyAdmin 旧版本中的 Relation 视图的布局有所不同，它将数据库、表和列下拉菜单组合在一个下拉菜单中。

img/332054_5_En_18_Fig14_HTML.jpg

图 18-14

尝试删除类别表中的记录

  1. 如果您还没有这样做，请更新至少一个博客条目，使其与类别相关联。

  2. 在 phpMyAdmin 中，选择categories表，然后点击与博客条目相关的类别旁边的Delete，如图 18-14 所示。

img/332054_5_En_18_Fig15_HTML.jpg

图 18-15

如果存在相关记录，外键约束会阻止删除

  1. 当 phpMyAdmin 要求您确认删除时，单击OK。如果你已经正确设置了外键约束，你会看到一个类似于图 18-15 的错误信息。

  2. 如果错误消息出现在模式对话框中，请单击该框将其消除。

  3. 选择article2cat表，并点击结构选项卡。然后点击“关系视图”

    Note

    在 phpMyAdmin 的旧版本中，ON DELETE和ON UPDATE可能为空。将这些选项留空与选择RESTRICT效果相同，这是两者的默认设置。

  4. 将两个ON DELETE设置都更改为CASCADE，并点击Save。

  5. 在blog表中选择一条已知与类别相关联的记录。记下它的article_id，然后删除记录。

  6. 检查article2cat表。与您刚刚删除的记录相关联的记录也被删除。

要继续探索外键约束，请选择blog表，并与images表中的image_id建立外键关系。如果从 images 表中删除一条记录，那么 blog 表中的image_id外键需要设置为NULL。如果将ON DELETE的值设置为SET NULL，这将自动完成。通过从images表中删除一条记录并检查blog表中的相关记录来进行测试。

Note

如果需要将 InnoDB 表转换回 MyISAM，必须首先删除所有外键约束。选择“关系视图”，然后单击每个约束左上角的“放下”。在 phpMyAdmin 的旧版本中，将“外键(INNODB)”字段设置为空白，然后单击Save。移除约束后，您可以按照 PHP 解决方案 18-6 中的描述更改存储引擎。选择MyISAM而不是InnoDB。

创建带有外键约束的删除脚本

在 InnoDB 表中选择ON DELETE的值取决于表之间关系的性质。在使用phpsols数据库的情况下，将article2cat交叉引用表中的两列都设置为CASCADE不仅安全，而且是可取的。如果在blog或categories父表中删除了一条记录，则需要删除对照表中的相关值。

images和blog表之间的关系是不同的。如果您从images表中删除一条记录，您可能不想删除blog表中的相关文章。在那种情况下，SET NULL是一个合适的选择。当从images表中删除一条记录时，相关文章中的外键被设置为NULL，但文章保持不变。

另一方面，如果图像对理解文章至关重要，请选择RESTRICT。任何删除仍有相关文章的图像的尝试都会自动停止。

这些考虑会影响您处理删除脚本的方式。当外键约束设置为CASCADE或SET NULL时，您不需要做任何特殊的事情。您可以使用一个简单的DELETE查询，剩下的交给数据库。

但是，如果外键约束被设置为RESTRICT，那么DELETE查询将会失败。要显示适当的错误消息，请使用 MySQLi 语句对象的errno属性。因外键约束而失败的查询的 MySQL 错误代码是1451。在调用了execute()方法之后，您可以检查 MySQLi 中的错误，如下所示(参见blog_delete_mysqli_innodb.php):

$stmt->execute();
if ($stmt->affected_rows > 0) {
    $deleted = true;
} else {
    $deleted = false;
    if ($stmt->errno == 1451) {
        $error = 'That record has dependent files in a child table, and cannot be deleted.';
    } else {
        $error = 'There was a problem deleting the record.';
    }
}

如果您使用 PDO，请使用errorCode()方法。由于外键约束而失败的查询的代码是HY000。在检查了受影响的行数之后，您可以使用 PDO 准备好的语句来检查错误代码，就像这样(参见blog_delete_pdo_innodb.php):

$deleted = $stmt->rowCount();
if (!$deleted) {
    if ($stmt->errorCode() == 'HY000') {
        $error = 'That record has dependent files in a child table, and cannot be deleted.';
    } else {
        $error = 'There was a problem deleting the record.';
    }
}

如果您使用 PDO exec()方法，技术是相同的，该方法使用非SELECT查询返回受影响的行数。当使用exec()时，在数据库连接上调用errorCode()方法:

$deleted = $conn->exec($sql);
if (!$deleted) {
    if ($conn->errorCode() == 'HY000') {
        $error = 'That record has dependent files in a child table, and cannot be deleted.';
    } else {
        $error = 'There was a problem deleting the record.';
    }
}

创建没有外键约束的删除脚本

如果您不能使用 InnoDB 表，那么您需要将相同的逻辑构建到您自己的删除脚本中。为了达到和ON DELETE CASCADE一样的效果，运行两个连续的DELETE查询，像这样(代码在blog_delete_mysqli_myisam_cascade.php；PDO 版本在blog_delete_pdo_myisam_cascade.php):

$sql = 'DELETE FROM article2cat WHERE article_id = ?';
$stmt->prepare($sql);
$stmt->bind_param('i', $_POST['article_id']);
$stmt->execute();
$sql = 'DELETE FROM blog WHERE article_id = ?';
$stmt->prepare($sql);
$stmt->bind_param('i', $_POST['article_id']);
$stmt->execute();

为了达到与ON DELETE SET NULL相同的效果，运行一个UPDATE查询结合一个DELETE查询，如下所示:

$sql = 'UPDATE blog SET image_id = NULL WHERE image_id = ?';
$stmt->prepare($sql);
$stmt->bind_param('i', $_POST['image_id']);
$stmt->execute();
$sql = 'DELETE FROM images WHERE image_id = ?';
$stmt->prepare($sql);
$stmt->bind_param('i', $_POST['image_id']);
$stmt->execute();

为了达到与ON DELETE RESTRICT相同的效果，您需要在继续执行DELETE查询之前运行SELECT查询来查找是否有相关记录，如下所示:

$sql = 'SELECT image_id FROM blog WHERE image_id = ?';
$stmt->prepare($sql);
$stmt->bind_param('i', $_POST['image_id']);
$stmt->execute();
// store result to find out how many rows it contains
$stmt->store_result();
// if num_rows is not 0, there are dependent records
if ($stmt->num_rows) {
    $error = 'That record has dependent files in a child table, and cannot be deleted.';
} else {
    $sql = 'DELETE FROM images WHERE image_id = ?';
    $stmt->prepare($sql);
    $stmt->bind_param('i', $_POST['image_id']);
    $stmt->execute();
}

第三章回顾

一旦您学会了与数据库通信所需的基本 SQL 和 PHP 命令，使用单个表就非常容易了。然而，通过外键来链接表是很有挑战性的。关系数据库的强大之处在于它的灵活性。问题是这种无限的灵活性意味着没有单一的“正确”做事方式。

不过，不要因此而分心。您的直觉可能是坚持使用单个表，但是沿着这条路走下去会有更大的复杂性。让使用数据库变得容易的关键是在早期阶段限制你的野心。像本章一样建立简单的结构，用它们做实验，了解它们是如何工作的。逐渐添加表和外键链接。有大量数据库工作经验的人说，他们经常花一半以上的开发时间来考虑表结构。在那之后，编码是容易的部分！

在最后一章中，我们将回到使用单个表，解决使用数据库进行用户身份验证的重要问题，以及如何处理散列和加密的密码。

十九、使用数据库认证用户

第十一章向您展示了用户认证和会话的原则，以对您的网站的部分进行密码保护，但是登录脚本都依赖于存储在 CSV 文件中的用户名和密码。将用户详细信息保存在数据库中更安全也更高效。数据库不仅仅存储用户名和密码的列表，还可以存储其他细节，比如名、姓、电子邮件地址等等。数据库还为您提供了使用散列(单向且不可逆)或加密(双向)的选项。在本章的第一节，我们将检查两者之间的区别。然后，您将为这两种类型的存储创建注册和登录脚本。

本章涵盖

  • 决定如何存储密码

  • 使用单向密码散列进行用户注册和登录

  • 使用双向加密进行用户注册和登录

  • 解密密码

选择密码存储方法

第十一章中的 PHP 解决方案使用了密码散列——一旦密码被散列，这个过程就无法逆转。这既是优点也是缺点。它为用户提供了更高的安全性，因为以这种方式存储的密码是保密的。然而，没有办法重新发布丢失的密码，因为甚至网站管理员也不能从散列版本中提取原始密码。唯一的解决办法是重设密码。

另一种方法是使用秘密密钥加密。这是一个双向、可逆的过程，依赖于一对函数:一个对密码进行加密，另一个将密码转换回纯文本，从而可以方便地向健忘的用户重新发布密码。双向加密使用传递给两个函数的密钥来执行转换。密钥只是一个你自己编的字符串。显然，为了保证数据的安全，密钥需要足够难以猜测，并且永远不应该存储在数据库中。但是，您需要将密钥嵌入到您的注册和登录脚本中——要么直接嵌入，要么通过包含文件嵌入——因此，如果您的脚本暴露了，您的安全性就会大大降低。

MySQL 和 MariaDB 提供了许多双向加密功能，但AES_ENCRYPT()被认为是最安全的。它使用美国政府批准的 128 位密钥长度(AES-128)的高级加密标准，用于保护机密级别的机密材料(绝密材料需要 AES-192 或 AES-256)。

哈希和密钥加密都有优点和缺点。许多安全专家建议，密码应该经常更改。因此，强迫用户更改忘记的密码，因为它无法被解密，可以被视为一个很好的安全措施。另一方面，用户可能会因为每次忘记现有密码时都需要处理新密码而感到沮丧。我将让您决定哪种方法最适合您的环境，我将只关注技术实现。

使用密码哈希

为了简单起见，我将使用与第十一章中相同的基本形式，因此只有用户名和散列密码存储在数据库中。

创建一个表来存储用户的详细信息

在 phpMyAdmin 中，在phpsols数据库中创建一个名为users的新表。该表需要三列，设置如表 19-1 所示。

表 19-1

用户表的设置

|

名字

|

类型

|

长度/值

|

属性

|

空

|

索引

|

A_I

| | --- | --- | --- | --- | --- | --- | --- | | user_id | INT |   | UNSIGNED | 取消选择 | PRIMARY | 挑选 | | username | VARCHAR | 15 |   | 取消选择 | UNIQUE |   | | pwd | VARCHAR | 255 |   | 取消选择 |   |   |

为了确保没有人可以注册与已经使用的用户名相同的用户名，username列被赋予了一个UNIQUE索引。

用于密码的pwd列允许存储最多 255 个字符的字符串。这比password_hash()使用的默认散列算法所需的 60 个字符要长得多。但是PASSWORD_DEFAULT常数被设计成随着新的更强的算法被添加到 PHP 中而随时间改变。所以推荐的大小是 255 个字符。

在数据库中注册新用户

要在数据库中注册用户，您需要创建一个要求输入用户名和密码的注册表单。已经用一个UNIQUE索引定义了username列，因此如果有人试图注册与现有用户名相同的用户名，数据库将返回一个错误。除了认证用户输入，处理脚本还需要检测错误，并建议用户选择不同的用户名。

PHP 解决方案 19-1:创建用户注册表单

这个 PHP 解决方案展示了如何改编第十一章中的注册脚本来使用 MySQL 或 MariaDB。它使用了 PHP 解决方案 11-3 中的CheckPassword类和 PHP 解决方案 11-4 中的register_user_csv.php。

如果需要，将ch19/Php8Solutions/Authenticate文件夹中的CheckPassword.php复制到php8sols站点根目录下的Php8Solutions/Authenticate文件夹中，将register_user_csv.php从ch19文件夹中复制到includes文件夹中。你也应该阅读 PHP 解决方案 11-3 和 11-4 中的说明来理解原始脚本是如何工作的。

  1. 将register_db.php从ch19文件夹复制到php8sols站点根目录下一个名为authenticate的新文件夹中。该页面包含与第十一章相同的基本用户注册表单，有一个用于用户名的文本输入字段、一个密码字段、另一个用于确认的密码字段和一个用于提交数据的按钮，如下面的屏幕截图所示:

img/332054_5_En_19_Figa_HTML.jpg

  1. 在 PHP 块中的DOCTYPE声明上方添加以下代码:
if (isset($_POST['register'])) {
    $username = trim($_POST['username']);
    $password = trim($_POST['pwd']);
    $retyped = trim($_POST['conf_pwd']);
    require_once '../includes/register_user_mysqli.php';
}

这与 PHP 解决方案 11-4 中的代码非常相似。如果表单已经提交，用户输入将被去掉前导和尾部的空格，并分配给简单的变量。然后包含一个名为register_user_mysqli.php的外部文件。如果您计划使用 PDO，请将包含文件命名为register_user_pdo.php。

  1. 处理用户输入的文件基于章节 11 中的register_user_csv.php。制作一份原始文件的副本(或使用ch19文件夹中的版本)并以register_user_mysqli.php或register_user_pdo.php的名称保存在includes文件夹中。

  2. 在您刚刚复制并重命名的文件中，找到如下开头的条件语句(在第 18 行周围):

  3. 删除条件语句中的其余代码。条件语句现在应该是这样的:

if (!$errors) {
    // hash password using default algorithm
    $password = password_hash($password, PASSWORD_DEFAULT);
  1. 将用户详细信息插入数据库的代码放在条件语句中。首先包含数据库连接文件，并创建一个具有读写权限的连接:
if (!$errors) {
    // hash password using default algorithm
    $password = password_hash($password, PASSWORD_DEFAULT);
}


if (!$errors) {
    // hash password using default algorithm
    $password = password_hash($password, PASSWORD_DEFAULT);
    // include the connection file
    require_once 'connection.php';
    $conn = dbConnect('write');
}

连接文件也在includes文件夹中，所以您只需要文件名。对于 PDO，添加'pdo'作为dbConnect()的第二个参数。

  1. 代码的最后一部分准备并执行准备好的语句，将用户的详细信息插入到数据库中。因为username列有一个UNIQUE索引，如果用户名已经存在，查询就会失败。如果发生这种情况，代码需要生成一条错误消息。MySQLi 和 PDO 的代码是不同的。

对于 MySQLi，添加以粗体突出显示的代码:

if (!$errors) {
    // hash password using default algorithm
    $password = password_hash($password, PASSWORD_DEFAULT);
    // include the connection file
    require_once 'connection.php';
    $conn = dbConnect('write');
    // prepare SQL statement
    $sql = 'INSERT INTO users (username, pwd) VALUES (?, ?)';
    $stmt = $conn->stmt_init();
    if ($stmt = $conn->prepare($sql)) {
        // bind parameters and insert the details into the database
        $stmt->bind_param('ss', $username, $password);
        $stmt->execute();
    }
    if ($stmt->affected_rows == 1) {
        $success = htmlentities($username) . ' has been registered.
            You may now log in.';
    } elseif ($stmt->errno == 1062) {
        $errors[] = htmlentities($username) . ' is already in use.
            Please choose another username.';
    } else {
        $errors[] = $stmt->error;
    }
}

新代码首先将参数绑定到准备好的语句。用户名和密码是字符串，所以bind_param()的第一个参数是'ss'(参见第十三章中的“在 MySQLi 预准备语句中嵌入变量”)。执行完语句后，条件语句检查affected_rows属性的值。如果是1，说明细节已经插入成功。

提示你需要明确检查affected_rows的值，因为如果有错误，它就是 1。与一些编程语言不同，PHP 将 1 视为true。

替代条件检查准备好的语句的errno属性的值，该属性包含 MySQL 错误代码。索引为UNIQUE的列中重复值的代码是1062。如果检测到该错误代码，就会向$errors数组添加一条错误消息，要求用户选择不同的用户名。如果生成了不同的错误代码，存储在语句的error属性中的消息将被添加到$errors数组中。

PDO 版本看起来是这样的:

if (!$errors) {
    // encrypt password using default encryption
    $password = password_hash($password, PASSWORD_DEFAULT);
    // include the connection file
    require_once 'connection.php';
    $conn = dbConnect('write', 'pdo');
    try {
        // prepare SQL statement
        $sql = 'INSERT INTO users (username, pwd) VALUES (:username, :pwd)';
        $stmt = $conn->prepare($sql);
        // bind parameters and insert the details into the database
        $stmt->bindParam(':username', $username, PDO::PARAM_STR);
        $stmt->bindParam(':pwd', $password, PDO::PARAM_STR);
        $stmt->execute();
        if ($stmt->rowCount() == 1) {
            $success = htmlentities($username) . ' has been registered.
                You may now log in.';
        }
    } catch (PDOException $e) {
          if ($e->getCode() == 23000) {
              $errors[] = htmlentities($username) . 'is already in use.
                  Please choose another username.';
          } else {
               $errors[] = $e->getMessage();
          }
    }
}

PHP 8 中 PDO 的默认错误模式是出错时抛出异常，因此向数据库提交查询的代码需要包装在try / catch块中。

准备好的语句对username和pwd列使用命名参数。提交的值通过bindParam()方法绑定到它，使用PDO::PARAM_STR常量将数据类型指定为字符串。执行完语句后，条件语句使用rowCount()方法检查记录是否已经创建。

如果准备好的语句失败，catch块处理 PDOException。条件语句调用异常对象上的getCode()方法。如果用户名已经存在于数据库中，则返回值为23000。PDO 使用 ANSI SQL 标准定义的错误代码，而不是 MySQL 生成的错误代码。如果错误代码匹配，则向$errors数组添加一条消息，要求用户选择不同的用户名。否则，使用来自getMessage()方法的错误消息。

Note

在 MySQLi 和 PDO 脚本中，当在一个活动的网站上部署注册脚本时，用一个通用错误消息替换else块中的代码。显示语句的error属性(MySQLi)或$e->getMessage() (PDO)的值仅用于测试目的。

  1. 剩下的工作就是添加在注册页面上显示结果的代码。在register_db.php中的开始<form>标记之前添加以下代码:

  2. 保存register_db.php，并在浏览器中加载。通过输入您知道违反密码强度规则的输入来测试它。如果您在同一次尝试中犯了多个错误，错误消息的项目符号列表应该出现在表单的顶部，如下一个屏幕截图所示:

<h1>Register user</h1>
<?php
if (isset($success)) {
    echo "<p>$success</p>";
} elseif (isset($errors) && !empty($errors)) {
    echo '<ul>';
    foreach ($errors as $error) {
        echo "<li>$error</li>";
    }
    echo '</ul>';
}
?>
<form action="register_db.php" method="post">

img/332054_5_En_19_Figb_HTML.jpg

  1. 现在正确地填写登记表。您应该会看到一条消息，告诉您已经为您选择的用户名创建了一个帐户。

  2. 尝试再次注册相同的用户名。这一次，您应该会看到与下面的屏幕截图类似的消息:

img/332054_5_En_19_Figc_HTML.jpg

  1. 如有必要，对照register_db_mysqli.php和register_user_mysqli.php或register_db_pdo.php和register_user_pdo.php检查你的代码，这些都在ch19文件夹中。

现在您已经在数据库中注册了用户名和密码，您需要创建一个登录脚本。ch19文件夹包含一组复制 PHP 解决方案 11-5 到 11-7 中设置的文件:一个登录页面和两个受密码保护的页面。

PHP 解决方案 19-2:用数据库认证用户凭证

这个 PHP 解决方案展示了如何通过查询数据库找到用户名密码的散列版本，然后将其作为参数与用户提交的密码一起传递给password_verify(),从而认证用户存储的凭证。如果password_verify()返回true，用户将被重定向到受限页面。

  1. 将login_db.php、menu_db.php和secretpage_db.php从ch19文件夹复制到authenticate文件夹。同样将logout_db.php和session_timeout_db.php从ch19文件夹复制到includes文件夹。

    这建立了与第十一章中使用的相同的基本测试平台。唯一的区别是链接被改为重定向到authenticate文件夹。

  2. 在login_db.php中，在DOCTYPE声明上方的 PHP 块中添加以下代码:

$error = ";
if (isset($_POST['login'])) {
    session_start();
    $username = trim($_POST['username']);
    $password = trim($_POST['pwd']);
    // location to redirect on success
    $redirect = 'http://localhost/php8sols/authenticate/menu_db.php';
    require_once '../includes/authenticate_mysqli.php';
}

这与第十一章中登录表单的代码遵循相似的模式。它首先将$error初始化为一个空字符串。如果表单已提交，条件语句将启动一个会话。从用户输入字段中删除空白，用户成功时将被重定向到的页面位置存储在一个变量中。最后，包含了您接下来要构建的身份验证脚本。

如果您使用 PDO，使用authenticate_pdo.php作为处理脚本。

  1. 创建一个名为authenticate_mysqli.php或authenticate_pdo.php的新文件，并保存在includes文件夹中。该文件将只包含 PHP 脚本，所以去掉任何 HTML 标记。

  2. 包括数据库连接文件，使用只读帐户创建到数据库的连接，并使用准备好的语句获取用户的详细信息。

对于 MySQLi，使用以下代码:

<?php
require_once 'connection.php';
$conn = dbConnect('read');
// get the username's hashed password from the database
$sql = 'SELECT pwd FROM users WHERE username = ?';
// initialize and prepare statement
$stmt = $conn->stmt_init();
$stmt->prepare($sql);
// bind the input parameter
$stmt->bind_param('s', $username);
$stmt->execute();
// bind the result, using a new variable for the password
$stmt->bind_result($storedPwd);
$stmt->fetch();

这是一个如此简单的SELECT查询，以至于我在将它传递给 MySQLi prepare()方法时没有使用条件语句。用户名是一个字符串，所以bind_param()的第一个参数是's'。如果找到匹配，结果将绑定到$storedPwd。您需要为存储的密码使用一个新的变量，以避免覆盖用户提交的密码。

执行完语句后，fetch()方法获得结果。

对于 PDO，请改用以下代码:

<?php
require_once 'connection.php';
$conn = dbConnect('read', 'pdo');
// get the username's hashed password from the database
$sql = 'SELECT pwd FROM users WHERE username = ?';
// prepare statement
$stmt = $conn->prepare($sql);
// pass the input parameter as a single-element array
$stmt->execute([$username]);
$storedPwd = $stmt->fetchColumn();

这段代码和 MySQLi 版本做的一样，但是使用了 PDO 语法。用户名作为单元素数组传递给execute()方法。因为结果中只有一列，fetchColumn()返回值并将其分配给$storedPwd。

  1. 一旦您获得了用户名的密码，您需要做的就是将提交和存储的版本传递给password_verify()。如果password_verify()返回true，创建会话变量以指示成功登录和会话开始的时间，重新生成会话 ID，并重定向到受限页面。否则，在$error中存储一条错误信息。

在上一步中输入的代码后插入以下代码。MySQLi 和 PDO 都是如此。

// check the submitted password against the stored version
if (password_verify($password, $storedPwd)) {
    $_SESSION['authenticated'] = 'Jethro Tull';
    // get the time the session started
    $_SESSION['start'] = time();
    session_regenerate_id();
    header("Location: $redirect");
    exit;
} else {
    // if not verified, prepare error message
    $error = 'Invalid username or password';
}

正如在第十一章中一样，$_SESSION['authenticated']的值并不重要。

  1. 保存authenticate_mysqli.php或authenticate_pdo.php，用你在 PHP 解决方案 19-1 末尾注册的用户名和密码登录，测试login_db.php。登录过程应该与第十一章中的方式完全相同。不同之处在于，所有细节都更安全地存储在数据库中。

如果有必要，你可以对照login_mysqli.php和authenticate_mysqli.php或者login_pdo.php和authenticate_pdo.php来检查你的代码，它们都在ch19文件夹中。如果遇到问题，最常见的错误是在数据库中为散列密码创建了太窄的列。它必须至少有 60 个字符宽，建议它能够存储多达 255 个字符，以防将来的加密方法生成更长的字符串。

虽然在数据库中存储哈希密码比使用文本文件更安全，但密码是以明文、未加密的文本形式从用户的浏览器发送到服务器的。为了安全起见，应该通过传输层安全(TLS)或安全套接字层(SSL)连接来登录和访问后续页面。

使用密钥加密

为秘钥加密设置用户注册和验证的主要区别在于，密码需要使用BLOB数据类型作为二进制对象存储在数据库中(更多信息请参见第十二章中的“存储二进制数据”),密码验证发生在 SQL 查询中，而不是在 PHP 脚本中。

创建存储用户详细信息的表

在 phpMyAdmin 中，在phpsols数据库中创建一个名为users_2way的新表。它需要三列，设置如表 19-2 所示。

表 19-2

users_2way 表的设置

|

名字

|

类型

|

长度/值

|

属性

|

空

|

索引

|

A_I

| | --- | --- | --- | --- | --- | --- | --- | | user_id | INT |   | UNSIGNED | 取消选择 | PRIMARY | 挑选 | | username | VARCHAR | 15 |   | 取消选择 | UNIQUE |   | | pwd | BLOB |   |   | 取消选择 |   |   |

注册新用户

AES_ENCRYPT()函数有两个参数:要加密的值和加密密钥。加密密钥可以是您选择的任何字符串。出于这个例子的目的，我选择了takeThisWith@PinchOfSalt，但是一系列随机的字母数字字符和符号会更安全。默认情况下，AES_ENCRYPT()用 128 位密钥对数据进行编码。对于更安全的 256 位密钥长度，需要将 MySQL 中的block_encryption_mode系统变量设置为aes-256-cbc(详见 https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-decrypt )。

单向密码散列和密钥加密的基本注册脚本是相同的。唯一的区别在于将用户数据插入数据库的部分。

提示下面的脚本将加密密钥直接嵌入页面。出于安全考虑，您应该在包含文件中定义密钥，并将其存储在服务器的文档根目录之外。

MySQLi 的代码如下所示(完整的清单在ch19文件夹的register_2way_mysqli.php中):

if (!$errors) {
    // include the connection file
    require_once 'connection.php';
    $conn = dbConnect('write');
    // create a key
    $key = 'takeThisWith@PinchOfSalt';
    // prepare SQL statement
    $sql = 'INSERT INTO users_2way (username, pwd)
            VALUES (?, AES_ENCRYPT(?, ?))';
    $stmt = $conn->stmt_init();
    if ($stmt = $conn->prepare($sql)) {
        // bind parameters and insert the details into the database
        $stmt->bind_param('sss', $username, $password, $key);
        $stmt->execute();
    }
    if ($stmt->affected_rows == 1) {
        $success = htmlentities($username) . ' has been registered. You may now log in.';
    } elseif ($stmt->errno == 1062) {
        $errors[] = htmlentities($username) . ' is already in use. Please choose another username.';
    } else {
        $errors[] = $stmt->error;
    }
}

对于 PDO，它看起来是这样的(完整列表见ch19文件夹中的register_2way_pdo.php):

if (!$errors) {
    // include the connection file
    require_once 'connection.php';
    $conn = dbConnect('write', 'pdo');
    // create a key
    $key = 'takeThisWith@PinchOfSalt';
    try {
        // prepare SQL statement
        $sql = 'INSERT INTO users_2way (username, pwd)
                   VALUES (:username, AES_ENCRYPT(:pwd, :key))';
        $stmt = $conn->prepare($sql);
        // bind parameters and insert the details into the database
        $stmt->bindParam(':username', $username, PDO::PARAM_STR);
        $stmt->bindParam(':pwd', $password, PDO::PARAM_STR);
        $stmt->bindParam(':key', $key, PDO::PARAM_STR);
        $stmt->execute();
        if ($stmt->rowCount() == 1) {
            $success = htmlentities($username) . ' has been registered. You may now log in.';
        }
    } catch (PDOException $e) {
          if ($e->getCode() == 23000) {
              $errors[] = htmlentities($username) . ' is already in use. Please choose another username.';
          } else {
               $errors[] = $e->getMessage();
          }
    }
}

严格来说，没有必要为$key使用绑定参数，因为它不是来自用户输入。但是，如果直接将其嵌入到查询中，整个查询需要用双引号括起来，而$key需要用单引号括起来。

为了测试前面的脚本，将它们复制到includes文件夹中，并包含在register_db.php中，而不是register_db_mysqli.php或register_db_pdo.php中。

双向加密的用户认证

创建双向加密的登录页面非常简单。连接到数据库后，将用户名、密钥和未加密的密码合并到一个SELECT查询的WHERE子句中。如果查询找到匹配，则允许用户进入站点的受限部分。如果不匹配，登录将被拒绝。代码与 PHP 解决方案 19-2 中的相同，除了下面的部分。

对于 MySQLi，看起来是这样的(见authenticate_2way_mysqli.php):

<?php
require_once 'connection.php';
$conn = dbConnect('read');
// create key
$key = 'takeThisWith@PinchOfSalt';
$sql = 'SELECT username FROM users_2way
        WHERE username = ? AND pwd = AES_ENCRYPT(?, ?)';
// initialize and prepare statement
$stmt = $conn->stmt_init();
$stmt->prepare($sql);
// bind the input parameters
$stmt->bind_param('sss', $username, $password, $key);
$stmt->execute();
// to get the number of matches, you must store the result
$stmt->store_result();
// if a match is found, num_rows is 1, which is treated as true
if ($stmt->num_rows) {
    $_SESSION['authenticated'] = 'Jethro Tull';
    // get the time the session started
    $_SESSION['start'] = time();
    session_regenerate_id();
    header("Location: $redirect"); exit;
} else {
    // if not verified, prepare error message
    $error = 'Invalid username or password';
}

注意，在访问num_rows属性之前，需要存储预处理语句的结果。如果不这样做，num_rows将始终是0，即使用户名和密码正确，登录也会失败。

修订后的 PDO 代码如下所示(见authenticate_2way_pdo.php):

<?php
require_once 'connection.php';
$conn = dbConnect('read', 'pdo');
// create key
$key = 'takeThisWith@PinchOfSalt';
$sql = 'SELECT username FROM users_2way
        WHERE username = ? AND pwd = AES_ENCRYPT(?, ?)';
// prepare statement
$stmt = $conn->prepare($sql);
// bind variables by passing them as an array when executing statement
$stmt->execute([$username, $password, $key]);
// if a match is found, rowCount() produces 1, which is treated as true
if ($stmt->rowCount()) {
    $_SESSION['authenticated'] = 'Jethro Tull';
    // get the time the session started
    $_SESSION['start'] = time();
    session_regenerate_id();
    header("Location: $redirect"); exit;
} else {
    // if not verified, prepare error message
    $error = 'Invalid username or password';
}

为了测试这些脚本，将它们复制到includes文件夹中，用它们代替authenticate_mysqli.php和authenticate_pdo.php。

解密密码

解密使用双向加密的密码只需将密钥作为准备好的语句中的第二个参数传递给AES_DECRYPT(),如下所示:

$key = 'takeThisWith@PinchOfSalt';
$sql = "SELECT AES_DECRYPT(pwd, '$key') AS pwd FROM users_2way
        WHERE username = ?";

该密钥必须与最初用于加密密码的密钥完全相同。如果您丢失了密钥，密码仍然像使用单向哈希存储的密码一样不可访问。

通常，只有当用户请求密码提醒时，才需要解密密码。创建适当的安全策略来发送这样的提醒在很大程度上取决于您正在运行的站点的类型。然而，不言而喻，你不应该在屏幕上显示解密的密码。您需要设置一系列安全检查，比如询问用户的出生日期，或者提出一个只有用户可能知道答案的问题。即使用户答对了，您也应该通过电子邮件将密码发送到用户的注册地址。

如果你已经在这本书里学了这么多，所有必要的知识都应该唾手可得。

更新用户详细信息

我没有包括任何用户注册页面的更新表单。这是一个你在这个阶段应该能够独立完成的任务。关于更新用户注册细节最重要的一点是，您不应该在更新表单中显示用户的现有密码。如果你使用的是密码散列法，无论如何都不行。

接下来去哪里？

这本书涵盖了大量的领域。如果您已经掌握了这里介绍的所有技术，那么您正在成为一名中级 PHP 开发人员，再努力一点，您将进入高级水平。如果这是一场斗争，不要担心。再看一遍前面的章节。你练习得越多，它就变得越容易。

你可能在想，“我到底怎么能记住所有这些？”你不需要。不要羞于查阅资料。将 PHP 在线手册( www.php.net/manual/en/ )加入书签，定期使用。它不断更新，并且有很多有用的例子。在每页右上角的搜索框中键入一个函数名称，即可直接进入该函数的完整描述。即使您不记得正确的函数名，手册也会将您带到一个页面，提示最可能的候选函数。大多数网页都有实际的例子来展示这个函数或类是如何使用的。

让动态 web 设计变得容易的不是 PHP 函数和类的百科知识，而是对条件语句、循环和其他结构如何控制脚本流的深刻理解。一旦你可以用“如果这种情况发生了，接下来会发生什么？”来想象你的项目你是自己游戏的主人。我经常查阅 PHP 在线手册。对我来说，它就像一本字典。大多数时候，我只是想检查我的论点顺序是否正确，但我经常发现一些东西吸引了我的眼球，并打开了新的视野。我可能不会立即使用这些知识，但我会把它们储存在脑海中以备将来使用，并在需要检查细节时回去查看。

MySQL 在线手册( https://dev.mysql.com/doc/refman/8.0/en/ )同样有用。MariaDB 的文档位于 https://mariadb.com/kb/en/library/documentation/ 。让 PHP 和数据库在线手册成为你的朋友，你的知识将会突飞猛进。

avatar
文章
阅读
粉丝