mysql/php - view specific rows from database table based on id

Question

I have 2 tables that are relevant to this question, they are called satellite and satellite_multi. My thought process for creating the tables was that the satellite table contains the channel_id (primary key), name of the channel and the country that the channel broadcasts from, the

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.

I created the satellite_multi table as there are some channels that are on more than one satellite, e.g. Thor 0.8w and Hotbird 13.0e may have the same channel broadcasting, so I needed a way of being able to display multiple rows of data, if the channel is broadcast on more than one satellite.

Below is the table structure of the satellite_multi table:

+-----------------+---------------+-----------+--------------+------------+-----+----------+
| 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 |
+-----------+----------------+----------+

I have the site setup where the user clicks a hyperlink of the channel name on the main site, and they are brought to a page named view_channels.php , where the channel details are shown based on the ID of the channel from the satellite table. e.g. view_channel.php?channelid=19

This works correctly when the channel was on one satellite, as I was able to run a SELECT * query and display all the data.

I had an attempt to get the multiple channel data to display under each individual channel ID, but sadly it would not work .

I used the following code below in my view_channels.php page

$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();

The output was that all the information from the satellite_multi table and satellite table is shown for every channel ID, in this example, as Polsat is ID 1, only polsat should have been displayed ,but AFN Sports which contains a different ID was also displayed. (see image below)

All Data showing

My question is, is there something I have to add to my query to check the ID from the browser link, and to match it with the ID received from the table, so only the channel data for the particular ID will be shown?

I tried to add in a WHERE clause to show data based on channelid_multi

WHERE channelid_multi = $channelid_multi

but I got an error:

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

Thanks for any guidance anyone can provide

I have included my whole `

view_channels.php

` code below in case anyone needs to look at that

<?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

Like others have already told you in the comments the problem with your query is that you use WHERE condition before the JOIN statement and that is wrong mysql syntax. So you must change your query like this in order to work:

$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";

BUT

Your have not a good database design.

e.g What would happen when a satellite name change?You must update every row in satellite_multi having this satellite.

Since you have a many-to-many relationship I would use 3 tables.

• One for sattelites named satellites.

• One for channels named channels

• And a many-to-many table named channels2satellites.

  notice: I assume Frequency,Polarisation etc are attributes of a satellite.If they are attributes of a channel just move them to channels table.

satellites table

+-----------------+---------------+-----------+--------------+------------+-----+----------+
|          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 table

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

channels2satellites table

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

And when I needed the data for a channel I would use this query. Let's say you wanted information for 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

`

Answer 2

got it sorted,

this was what worked for me, thanks for the guidance @Anant and @nowhere

$channelid = $_GET["channelid"];
$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 = :channelid
GROUP BY s.name, s.country, f.satname, f.frequency, f.polarisation, f.symbrate, f.fec, f.encorfta";
$stmt = $DB->prepare($sql);
$stmt -> bindParam(':channelid', $channelid);
$stmt->execute();