SQL项目中自连接（Self Joins）无法正常运行的问题求助

我有一个展示图书馆数据库信息的网页，原本可以正常显示书籍标题、作者全名、书架位置等内容。现在我在Author表中新增了作者姓氏（ALast）和名字（AFirst）字段，需要在同一张表格中同时展示作者全名、姓氏和名字，但尝试修改后的代码无法正常运行。Author表的作者全名、新增的姓名字段都是通过BookAuthor.AID关联的。

原正常运行代码 #

$sqlbook = $conn->prepare("SELECT Books.Title, Author.Author, BookLocation.PID, BookLocation.SID from Books LEFT JOIN BookAuthor on Books.BID=BookAuthor.BID LEFT JOIN Author on BookAuthor.AID=Author.AID RIGHT JOIN BookLocation ON Books.BID=BookLocation.BID where Author.Author like '%$search%'");
/* $sqlbook->bindParam("s",$search); */
$sqlbook->execute();
$result = $sqlbook->get_result();
$sqlbook->close();
echo "<br><table class=\"results\">";
echo 
    "<tr>
        <th>Book Title</th>
        <th>Author</th>
        <th>Position on Shelf (0 front, 1 back)</th>
        <th>Shelf Number</th>
    </tr>";
    while ($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["Title"] . "</td><td>" . $row["Author"]  . "</td><td>" . $row["PID"] . "</td><td>" . $row["SID"];
        echo "</td></tr>\n";
    }
echo "</table>";

修改后出错的代码 #

$sqlbook = $conn->prepare("SELECT Books.Title, Author.Author, Author.ALast as ALast1, Author.AFirst as AFirst1, BookLocation.PID, BookLocation.SID from Books LEFT JOIN BookAuthor on Books.BID=BookAuthor.BID LEFT JOIN Author on BookAuthor.AID=Author.AID JOIN ALast on BookAuthor.AID=ALast1 JOIN AFirst on BookAuthor.AID=AFirst1 RIGHT JOIN BookLocation ON Books.BID=BookLocation.BID where Books.Title like '%$search%'");
/* $sqlbook->bindParam("s","%" . $search . "%"); */
$sqlbook->execute();
$result = $sqlbook->get_result();
$sqlbook->close();
echo "<br><table class=\"results\">";
echo 
    "<tr>
        <th>Book Title</th>
        <th>Author Full Name</th>
        <th>Author Last Name</th>
        <th>Author First Name</th>
        <th>Position on Shelf (0 front, 1 back)</th>
        <th>Shelf Number</th>
    </tr>";
    while ($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["Title"] . "</td><td>" . $row["Author"]  . "</td><td>" . $row["ALast"] . "</td><td>" . $row["AFirst"] . "</td><td>" . $row["PID"] . "</td><td>" . $row["SID"];
        echo "</td></tr>\n";
    }
echo "</table>";

错误原因 #

你错误地将Author表中的字段ALast和AFirst当成独立表进行连接，这完全没必要——这两个字段本身就在Author表里，只需在查询时直接选取即可，不需要额外自连接。另外，你给字段起了别名ALast1、AFirst1，但输出时调用原字段名ALast、AFirst，会导致取值失败。

修正后的代码 #

$sqlbook = $conn->prepare("SELECT Books.Title, Author.Author, Author.ALast, Author.AFirst, BookLocation.PID, BookLocation.SID 
                          FROM Books 
                          LEFT JOIN BookAuthor ON Books.BID=BookAuthor.BID 
                          LEFT JOIN Author ON BookAuthor.AID=Author.AID 
                          RIGHT JOIN BookLocation ON Books.BID=BookLocation.BID 
                          WHERE Books.Title LIKE ?");
// 使用参数绑定避免SQL注入，比直接拼接字符串更安全
$sqlbook->bind_param("s", "%$search%");
$sqlbook->execute();
$result = $sqlbook->get_result();
$sqlbook->close();

echo "<br><table class=\"results\">";
echo "<tr>
        <th>Book Title</th>
        <th>Author Full Name</th>
        <th>Author Last Name</th>
        <th>Author First Name</th>
        <th>Position on Shelf (0 front, 1 back)</th>
        <th>Shelf Number</th>
      </tr>";

while ($row = $result->fetch_assoc()) {
    echo "<tr>
            <td>" . htmlspecialchars($row["Title"]) . "</td>
            <td>" . htmlspecialchars($row["Author"]) . "</td>
            <td>" . htmlspecialchars($row["ALast"]) . "</td>
            <td>" . htmlspecialchars($row["AFirst"]) . "</td>
            <td>" . htmlspecialchars($row["PID"]) . "</td>
            <td>" . htmlspecialchars($row["SID"]) . "</td>
          </tr>\n";
}
echo "</table>";

关键修正点 #

1. 移除错误自连接：删除JOIN ALast on BookAuthor.AID=ALast1和JOIN AFirst on BookAuthor.AID=AFirst1两行错误语句，直接从Author表选取ALast和AFirst字段。
2. 修正字段别名问题：不再给ALast、AFirst起多余别名，确保查询结果字段名和输出调用一致。
3. 修复SQL注入风险：启用参数绑定bind_param，避免直接拼接$search到SQL语句中，提升安全性。
4. 添加内容转义：输出时用htmlspecialchars转义数据库内容，防止XSS攻击，增强页面安全性。

内容的提问来源于stack exchange，提问作者Erek G