英文:
Trouble with getting Self Joins to work with SQL project
问题
这是您的代码部分的中文翻译：
之前的代码：
$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->execute();
$result = $sqlbook->get_result();
$sqlbook->close();
echo "<br><table class=\"results\">";
echo
"<tr>
<th>书名</th>
<th>作者</th>
<th>书架位置 (0 前排, 1 后排)</th>
<th>书架编号</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->execute();
$result = $sqlbook->get_result();
$sqlbook->close();
echo "<br><table class=\"results\">";
echo
"<tr>
<th>书名</th>
<th>作者全名</th>
<th>作者姓氏</th>
<th>作者名字</th>
<th>书架位置 (0 前排, 1 后排)</th>
<th>书架编号</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>";
请注意，这是您提供的代码的翻译部分，不包括其他内容。
英文:
I have a webpage that allows me to display a table from my database that has library info, in this case, author full name, shelf location, book title, etc. I have added first and last name to the database, in the same part as the author name, and I need to be able to display it in the same table with the other info. I am struggling on how to get this to work, and my attempts at self joins have not worked well. The author full name and first and last name parts all use the bookauthor.AID as their primary keys.
This is the code that I had before that works:
$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>";
And this is what I have now that is not currently working:
$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>";
答案1
得分: 0
I've translated the code portion for you:
我猜你是在`Author`表中添加了新的列(`ALast`和`AFirst`)。在这种情况下，你不需要进行自连接。
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 '%$search%'
Please note that the variable $search is not translated as it appears to be a placeholder in the code.
英文:
I'm guessing you added the new columns (ALast and AFirst) to the Author table. In that case, you don't need a self join.
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 '%$search%'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论