我已经为一个查询连接了三个表。问题是，它先显示每个类名，然后显示狗，但没有将狗名链接到特定的节目。它在每个节目中显示相同的狗名列表。我已经尝试在我的狗表中使用show_id，并将其链接到show表中的show_id。
三个表格分别是:show, results, dogs。
有人能帮我吗?
<?php
if(isset($_GET['s_id'])) {
$the_show_id = $_GET['s_id'];
$view_query = "UPDATE shows SET show_view_count = show_view_count + 1 WHERE show_id = $the_show_id ";
$send_query = mysqli_query($connection, $view_query);
if(!$send_query) {
die("query failed" );
}
if(isset($_SESSION['user_role']) && $_SESSION['user_role'] == 'admin' ) {
$query = "SELECT * FROM shows WHERE show_id = $the_show_id ";
} else {
$query = "SELECT * FROM shows WHERE show_id = $the_show_id AND show_status = 'published' ";
}
$select_all_shows_query = mysqli_query($connection,$query);
if(mysqli_num_rows($select_all_shows_query) < 1) {
echo "<h1 class='text-center'>No shows available</h1>";
} else {
while($row = mysqli_fetch_assoc($select_all_shows_query)) {
$show_name = $row['show_title'];
$show_author = $row['show_author'];
$show_date = $row['show_date'];
$show_content = $row['show_content'];
?>
<h1 class="page-header">
Shows
</h1>
<!-- First Blog Post -->
<h2>
<a href="#"><?php echo $show_name ?></a>
</h2>
<p class="lead">
by <a href="index.php"><?php echo $show_author ?></a>
</p>
<h4>Show Date: <span class="glyphicon glyphicon-time"> </span>
<? php echo $show_date ?></h4>
<hr>
<div class="row">
<div class="col-xs-6 col-sm-3"><h4>Class Name</h4></div>
<div class="col-xs-6 col-sm-2"><h4>Placement</h4></div>
<div class="col-xs-6 col-sm-7"><h4>Dog Name</h4></div>
</div><hr>
<?php
$query = "SELECT result.class_name, result.placement, dogs.dog_name
FROM result
INNER JOIN dogs on result.resultID = dogs.resultIDD
INNER JOIN shows on dogs.show_id = shows.show_id
WHERE dog_name NOT LIKE 'absent' GROUP BY shows.show_id";
$result = mysqli_query($connection, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($connection), E_USER_ERROR);
if($result) {
while($row = mysqli_fetch_assoc($result)) {
$dog_name = $row['dog_name'];
$placement = $row['placement'];
$class_name = $row['class_name'];
?>
<div class="row">
<div class="col-xs-6 col-sm-3"><p><?php echo $class_name ?></p></div>
<div class="col-xs-6 col-sm-2"><p><?php echo $placement ?></p></div>
<div class="col-xs-6 col-sm-7"><p><?php echo $dog_name ?></p></div>
</div>
<?php
}
}
}
?>
好吧，假设所有其他事情都正常工作，您可能只需要添加狗。Show_id(或show。Show_id任意方式)的选择。您可以尝试下面的查询，然后如果它不起作用，报告您的表结构(您可以使用"show create table TBL_NAME"对三个表中的每一个表执行此操作)。
<?php
$query = "
SELECT
dogs.show_id,
result.class_name, result.placement,
dogs.dog_name
FROM
result INNER JOIN
dogs on result.resultID = dogs.resultIDD INNER JOIN
shows on dogs.show_id = shows.show_id
WHERE
dog_name NOT LIKE 'absent'
";
?>
UPDATE:好的，现在我们知道你已经在一个循环中，告诉我们show_id，你只需要更新查询，只拉狗(假设show_id实际上在狗表中…这不是真正属于它，除非你有狗+show_id作为一个唯一的，复合键)。
看这里，我添加$show_id到声明列表从最初的show查询，然后在查询中使用它来限制狗从该查询。此外，内连接在这里不是最好的…恕我直言。
...
while ($row = mysqli_fetch_assoc($select_all_shows_query)) {
// WE ALREADY KNOW THE SHOW WE'RE IN
$show_id = $row['show_id'];
$show_name = $row['show_title'];
$show_author = $row['show_author'];
$show_date = $row['show_date'];
$show_content = $row['show_content'];
?>
<h1 class="page-header">Shows</h1>
<!-- First Blog Post -->
<h2><a href="#"><?php echo $show_name ?></a></h2>
<p class="lead"> by <a href="index.php"><?php echo $show_author ?></a></p>
<h4>Show Date: <span class="glyphicon glyphicon-time"> </span> <?php echo $show_date ?></h4>
<hr>
<div class="row">
<div class="col-xs-6 col-sm-3"><h4>Class Name</h4></div>
<div class="col-xs-6 col-sm-2"><h4>Placement</h4></div>
<div class="col-xs-6 col-sm-7"><h4>Dog Name</h4></div>
</div>
<hr>
<?php
// SO UPDATE THE QUERY TO ONLY PULL THAT SHOW'S DOGS
$query = "SELECT result.class_name, result.placement, dogs.dog_name
FROM result
LEFT JOIN dogs on result.resultID = dogs.resultIDD
WHERE dogs.show_idd = $show_id AND dog_name NOT LIKE 'absent'";
$result = mysqli_query($connection, $query) or trigger_error
("Query Failed! SQL: $query - Error: ". mysqli_error
($connection), E_USER_ERROR);
if ($result) {
while ($row = mysqli_fetch_assoc($result)) {
$dog_name = $row['dog_name'];
$placement = $row['placement'];
$class_name = $row['class_name'];
?>
<div class="row">
<div class="col-xs-6 col-sm-3"><p><?php echo $class_name ?></p></div>
<div class="col-xs-6 col-sm-2"><p><?php echo $placement ?></p></div>
<div class="col-xs-6 col-sm-7"><p><?php echo $dog_name ?></p></div>
</div>