php - mysql/php - 根据 id 查看数据库表中的特定行

Question

我有两个与这个问题相关的表，它们被称为卫星和satellite_multi。我创建表的思维过程是卫星表包含channel_id(主键)、 channel 名称以及 channel 广播的国家/地区，

satellite_multi table has a column called channelid_multi, which is linked as a foreign key to the channel_id from the satellite table, and the other columns are
SatName, Frequency, Polarisation, Symbrate, FEC, EncorFTA.

我创建了 Satellite_multi 表，因为有些 channel 位于多个卫星上，例如Thor 0.8w 和 Hotbird 13.0e 可能具有相同的 channel 广播，因此如果 channel 在多个卫星上广播，我需要一种能够显示多行数据的方法。

下面是satellite_multi表的表结构:

+-----------------+---------------+-----------+--------------+------------+-----+----------+
| ChannelID_Multi(FK) |    SatName    | Frequency | Polarisation |   Symbrate | FEC | EncorFta |
+-----------------+---------------+-----------+--------------+------------+-----+----------+
|               1 | Thor 0.8w     |     10932 |   H          |     275000 | 5/6 |   ENC    |
|               1 | Hotbird 13.0e |     10654 |   V          |      25000 | 3/4 |   FTA    |
+-----------------+---------------+-----------+--------------+------------+-----+----------+

This is the table structure for the table named satellite:
+-----------+----------------+----------+
| ChannelID (PK) |       Name     | Country  |
+-----------+----------------+----------+
|         1 |   Polsat Sport | Poland   |
|         2 |   Sky Sports   |  England |
+-----------+----------------+----------+

我有一个网站设置，用户单击主网站上 channel 名称的超链接，他们将被带到名为 view_channels.php 的页面，其中根据来自 channel 的 ID 显示 channel 详细信息卫星表。例如view_channel.php?channelid=19

当 channel 位于一颗卫星上时，这可以正常工作，因为我能够运行 SELECT * 查询并显示所有数据。

我曾尝试让多个 channel 数据显示在每个单独的 channel ID 下，但遗憾的是它不起作用。

我在 view_channels.php 页面中使用了以下代码

$sql = "SELECT s.name, s.country, f.satname, f.frequency, f.polarisation, f.symbrate, f.fec, f.encorfta
FROM satellitemulti f
LEFT JOIN satellite s
ON f.channelid_multi=s.channelid
GROUP BY s.name, s.country, f.satname, f.frequency, f.polarisation, f.symbrate, f.fec, f.encorfta";
$stmt = $DB->prepare($sql);
$stmt->execute();

输出是针对每个 channel ID 显示来自 Satellite_multi 表和卫星表的所有信息，在此示例中，由于 Polsat 的 ID 为 1，因此只应显示 polsat，但包含不同 ID 的 AFN Sports 被显示也显示。 (见下图)

All Data showing

我的问题是，是否需要在查询中添加一些内容来检查浏览器链接中的 ID，并将其与从表中收到的 ID 进行匹配，以便仅显示特定 ID 的 channel 数据？

我尝试添加 WHERE 子句来显示基于channelid_multi的数据

WHERE channelid_multi = $channelid_multi

但我收到一个错误:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN satellite s ON f.channelid_multi=s.channelid GROUP BY s.name, s.country, f.' at line 4' in E:\home\students\2132\B00614408\public_html\sportsschedule\view_channels.php:19 Stack trace: #0 E:\home\students\2132\B00614408\public_html\sportsschedule\view_channels.php(19): PDOStatement->execute() #1 {main} thrown in E:\home\students\2132\B00614408\public_html\sportsschedule\view_channels.php on line 19

感谢任何人都可以提供的任何指导

我已经包括了我的整个`

view_channels.php

`下面的代码，以防有人需要查看

<?php

require_once './config.php';
include './header.php';

$sql = "SELECT s.name, s.country, f.satname, f.frequency, f.polarisation, f.symbrate, f.fec, f.encorfta
FROM satellitemulti f
WHERE channelid_multi = $channelid_multi
LEFT JOIN satellite s
ON f.channelid_multi=s.channelid
GROUP BY s.name, s.country, f.satname, f.frequency, f.polarisation, f.symbrate, f.fec, f.encorfta";
$stmt = $DB->prepare($sql);
$stmt->execute();


?>




<div class="panel panel-primary">
<div class="panel-heading">
<h3 class="panel-title"> Whats On</h3>
</div>
<div class="panel-body">
    </div>


<div class="clearfix"></div>
<div class="table-responsive">
<table class="table table-striped table-hover table-bordered ">
<tbody>
<caption> Channel Details</caption>
<tr>
<th>Name</th>
<th>Country</th>
<th>Sat Name</th>
<th>Frequency</th>
<th>Polarisation</th>
<th>Symbol Rate</th>
<th>FEC</th>
<th>Enc or FTA</th>

</tr>
<?php   while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{

$name = $row['name'];
$country= $row['country'];
$satname = $row['satname'];
$frequency=$row['frequency'];
$polarisation=$row['polarisation'];
$symbrate=$row['symbrate'];
$fec=$row['fec'];
$encorfta=$row['encorfta'];
$channelid_multi=$row['channelid_multi'];



echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['satname'] . "</td>";
echo "<td>" . $row['frequency'] . "</td>";
echo "<td>" . $row['polarisation'] . "</td>";
echo "<td>" . $row['symbrate'] . "</td>";
echo "<td>" . $row['fec'] . "</td>";
echo "<td>" . $row['encorfta'] . "</td>";

}
echo "</tr>";
echo "</table>";
?>
</div>


<?php
include './footer.php';
?>

Answer 1

就像其他人已经在评论中告诉你的问题一样查询是您使用 WHERE JOIN之前的条件声明和那个mysql语法错误。因此，您必须像这样更改查询才能工作:

$sql = "SELECT s.name, s.country, f.satname, f.frequency, f.polarisation, f.symbrate, f.fec, f.encorfta
FROM satellitemulti f
LEFT JOIN satellite s
ON f.channelid_multi=s.channelid
WHERE channelid_multi = $channelid_multi
GROUP BY s.name, s.country, f.satname, f.frequency, f.polarisation, f.symbrate, f.fec, f.encorfta";

但是

你的数据库设计不好。

例如，当卫星名称更改时会发生什么？您必须更新中的每一行 satellite_multi拥有这颗卫星。

由于您有多对多关系，我将使用 3 个表。

• 一颗名为 satellites 的卫星.

• 一个用于名为 channels 的 channel

• 还有一个名为 channels2satellites 的多对多表.

  注意:我假设频率、偏振等是卫星的属性。如果它们是 channel 的属性，只需将它们移至 channels表。

satellites表

+-----------------+---------------+-----------+--------------+------------+-----+----------+
|          ID(PK) |    SatName    | Frequency | Polarisation |   Symbrate | FEC | EncorFta |
+-----------------+---------------+-----------+--------------+------------+-----+----------+
|               1 | Thor 0.8w     |     10932 |   H          |     275000 | 5/6 |   ENC    |
|               2 | Hotbird 13.0e |     10654 |   V          |      25000 | 3/4 |   FTA    |
+-----------------+---------------+-----------+--------------+------------+-----+----------+

channels表

+-----------+----------------+----------------+
|        ID (PK) |       Name     |  Country  |
+-----------+----------------+----------+
|         1      |   Polsat Sport |  Poland   |
|         2      |   Sky Sports   |  England  |
+-----------+----------------+----------------+

channels2satellites表

+-----------+----------------+----------------------------+
|        ID (PK) |   channel_id(FK)   | satellite_id(FK)  |
+----------------+--------------------+-------------------+
|         1      |   1                |  1                |
|         2      |   1                |  2                |
|         3      |   2                |  1                |
+-----------+----------------+----------------------------+

当我需要 channel 的数据时，我会使用此查询。假设您想要 channel 1 的信息

SELECT c.Name,c.Country,s.SatName,s.Frequency,s.Polarization.s.Symbrate,s.FEC,s.EncorfFta FROM channels c
INNER JOIN channels2satellites c2s ON c.id=c2s.channel_id
INNER JOIN satellites s ON c2s.satellite_id=s.id
WHERE c.id=1

`