php - 在PHP中显示多个表中的外键
问题描述
我目前正在开发一个简单的搜索网站。我正在使用 php 和 mysql 工作台。我的问题是在下面的文章页面中,我无法显示位置和供应商的实际名称,它们位于不同的表中,但通过我的“复制”表中的外键连接。它只显示与实际单词对应的首字母缩写词和数字。对不起,我的英语不好。我将插入一张我的 er 图表的图片。
ER图在这里:
搜索页面:
<?php
include 'header.php';
?>
<div id= "Title">
<h1>Online Public Access Catalog</h1>
</div>
<div id="navbar">
<h2> DISCOVERY SERVICE</h2>
<form id="SearchBar" action="search.php" method="POST">
<input type="text" name="search" class="search" placeholder="Search Here">
<button type="submit" name="submit" class= "submit">Submit</button>
</form>
<h3 class="enterword"> Enter a word or phrase and press submit</h3>
</body>
</html>
搜索结果页面:
<?php
include 'header.php'
?>
<h1 class="searchresults">Search Results:</h1>
<div class="search-container">
<?php
if (isset($_POST['submit']))
{
$search = mysqli_real_escape_string($conn, $_POST['search']);
$sql = "SELECT * FROM book WHERE Book_Title LIKE '%$search%' OR Author LIKE '%$search%' OR Call_Number LIKE '%$search%'";
$result = mysqli_query($conn, $sql);
$queryResult = mysqli_num_rows($result);
echo "<h3 class='resultcount'>There are ".$queryResult." results!</h3>";
if ($queryResult > 0)
{
while ($row = mysqli_fetch_assoc($result))
{
echo "<a href='article.php?Book_Title=".$row['Book_Title']."&id=".$row['Book_ID']."' class= 'search-ref'><div class=search-box>
<tr><td>".$row['Book_Title']." </td>
<td>/ ".$row['Author']."</td>
<p>".$row['Call_Number']."</p>
</div></tr><br>";
}
}
}
?>
<input type="button" value="Back" onclick="history.back(-1)" />
</div>
<!-- Article Page -->
<?php
include 'header.php';
?>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<div class="article-container">
<?php
//Declairing Variables
$Author = "Authors: ";
$Edition = "Edition: ";
$Subject ="Subject: ";
$Summary = "Summary: ";
$Notes = "Notes: ";
$Publisher ="Publisher: ";
$Phys_Desc ="Physical Description: ";
$Call_Number ="Call Number: ";
$Book_ID = "Book ID: ";
$Title= mysqli_real_escape_string($conn, $_GET['Book_Title']);
$sql ="SELECT * FROM book WHERE Book_Title='$Title'";
$result = mysqli_query($conn, $sql);
$queryResult = mysqli_num_rows($result);
if ($queryResult > 0)
while ($row = mysqli_fetch_assoc($result))
{
echo "<div class= 'article-box'>
<h3 class='booktitle'><b>".$row['Book_Title']."</h3></b>
<p><b>$Author</b>".$row['Author']."</p>
<p><b>$Edition</b>".$row['Edition']."</p>
<p><b>$Subject</b>".$row['Subject']."</p>
<p><b>$Summary</b>".$row['Summary']."</p>
<p><b>$Notes</b>".$row['Notes']."</p>
<p><b>$Publisher</b>".$row['Publisher']."</p>
<p><b>$Phys_Desc</b>".$row['Phys_Desc']."</p>
<p><b>$Call_Number</b>".$row['Call_Number']."</p>
</div>";
}
?>
<input type="button" value="Back" onclick="history.back(-1)" />
<!-- POP-UP WINDOW -->
<!-- Trigger the modal with a button -->
<button type="button" class="btn btn-info btn-lg" data-toggle="modal" data-target="#myModal">Copies</button>
<!-- Modal -->
<div class="modal fade" id="myModal" role="dialog">
<div class="modal-dialog">
<!-- Modal content-->
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title">Copies</h4>
</div>
<div class="modal-body">
<table class = "table">
<tr>
<th>Barcode</th>
<th>Copy</th>
<th>Status</th>
<th>Location</th>
<th>Format</th>
<th>Vendor</th>
</tr>
<?php
{
$id = mysqli_real_escape_string($conn, $_GET['id']);
$sql = "SELECT * FROM copy WHERE Book_ID='$id'";
$result = mysqli_query($conn, $sql);
$queryResult = mysqli_num_rows($result);
if ($queryResult > 0)
{
while ($row = mysqli_fetch_assoc($result))
{
echo "
<tr><td>".$row['Barcode']."</td>
<td>".$row['Copy']."</td>
<td>".$row['Status']."</td>
<td>".$row['Location']."</td>
<td>".$row['Format']."</td>
<td>".$row['Vendor']."</td></tr>
";
}
}
}
?>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
解决方案
尝试在查询中使用 JOIN。
SELECT Barcode, Copy, Status, l.Location, f.Format, Vendor
FROM copy c
INNER JOIN location l ON l.Location_Acronym = c.Location
INNER JOIN format f ON f.Format_ID = c.Format
WHERE Book_ID='$id'
推荐阅读
- php - 我在 web.php 中定义了一条路由,但错误提示 throw new InvalidArgumentException("Route [{$name}] not defined.");
- android - 是否有其他解决方案而不是嵌套的 Recyclerview
- python - 使用“np.where”条件将值分配给数据框列时出现值错误
- rust - 为什么 Rust 编译器在调用 parse 时无法推断类型?
- swift - 无法将类型“String.Type”的值分配给类型“String?” - 斯威夫特
- asp.net-mvc - 当我们从 url 传递时,为什么 %2020 对长数据类型有效
- java - 如何将处理程序与 CaptureSession.stopReapeating() 一起使用?
- c++ - GDCL Mp4mux 过滤器 BeginFlush() 是一次性操作。我可以解决这个限制吗?
- java - Java 中 int/long/referenceToAObject 的线程安全
- android - 如何从单选组中获取默认选择的单选按钮?