I have a situation where I'm trying to output the contents of a MySQL pivot/linking table from a many-to-many relationship with PHP.
In the code below I have a series of boards that will contain images. The actual board previews are outputted with the first block of PHP, but inside this I need a nested while loop that outputs the images themselves.
The pivot/linking table is called boards_images has two columns board_id and image_id and these are both foreign keys to the boards table and images table. A representation of the tables is given below the main code below.
Because some boards will have an image that is already on other boards I obviously need some type of conditional logic that outputs the image when the related board is present.
Each board preview will only show four images so I will need need to add a LIMIT 4 clause to the MySQL
My Question
What is the best way to approach this, do I:
a) Need to do two database calls one in the parent while loop and one in the nested while loop, or do I need to get all of the info from the MySQL database in the parent while loop with a multiple JOIN?
b) How do I actually output the content of the pivot/linking table? I can't seem to get my head around how to do this.
<?php
// $db_id is a variable created from a user login $_SESSION value
$sql = "SELECT boards.board_id, boards.board_name, users.user_id
FROM boards
JOIN users ON boards.user_id = users.user_id
WHERE users.user_id = :user_id
ORDER BY boards.board_id DESC";
$stmt = $connection->prepare($sql);
$stmt -> execute([
':user_id' => $db_id
]);
// parent while loop that outputs the board name
while ($row = $stmt->fetch()) {
$dbBoardname = htmlspecialchars($row['board_name']);
?>
<div class="board-component">
<h2><?= $dbBoardname; ?></a></h2>
<?php
// --- NESTED INNER WHILE LOOP
$SQL2 = "SELECT boards_images.board_id, boards_images.image_id, images.filename
FROM boards_images
JOIN images ON boards_images.image_id = images.image_id
WHERE boards_images.board_id = :board_id
LIMIT 4";
$stmt2 = $connection->prepare($SQL2);
$stmt2 -> execute([
':board_id' => $dbBoardId
]);
while ($row2 = $stmt2->fetch()) {
$dbImageId = htmlspecialchars($row['image_id']);
$dbImageFilename = htmlspecialchars($row['filename']);
?>
<img src='<?= $wwwRoot . "/images-lib/{$dbImageFilename}" ?>' >
<?php } ?> <!-- end of nested while loop -->
</div>
<?php } ?> <!-- end of parent while loop -->
Representation of the Tables
// 'fk' stands for foreign key
// BOARDS_IMAGES LINKING / PIVOT TABLE
+----------------+----------------+
| board_id (fk) | image_id (fk) |
+----------------+----------------+
| 1 | 23 |
| 1 | 106 |
| 1 | 55 |
| 1 | 22 |
+----------------+----------------+
// BOARDS TABLE
+-------------+--------------+---------------+
| board_id | board_name | user_id (fk) |
----------------------------------------------
| 1 | London | 21 |
| 2 | France | 21 |
+-------------+--------------+---------------+
// IMAGES TABLE
+-------------+--------------------+---------------+
| image_id | filename | user_id (fk) |
---------------------------------------------------+
| 23 | BigBen.jpeg | 21 |
| 106 | TowerBridge.jpeg | 21 |
| 55 | TheMall.jpg | 21 |
| 22 | BuckPalace.jpg | 21 |
+-------------+--------------------+---------------+
// USERS TABLE
+-----------------+----------------+
| user_id | username |
+-----------------+----------------+
| 21 | johndoe |
+-----------------+----------------+
a) Need to do two database calls one in the parent while loop and one in the nested while loop, or do I need to get all of the info from the MySQL database in the parent while loop with a multiple JOIN?
You can do one query to get all the data necessary, we can limit the number of images displayed in php, and the join to the users table isn't needed because you have the FK in the boards table:
SELECT boards.board_name, images.filename
FROM boards
INNER JOIN boards_images on boards_images.board_id = boards.board_id
INNER JOIN images on boards_images.image_id = images.image_id
WHERE boards.user_id = :user_id
b) How do I actually output the content of the pivot/linking table? I can't seem to get my head around how to do this.
With the output of the above query resulting in something like:
board_name | filename
-------------------------------
London | BigBen.jpeg
London | TowerBridge.jpeg
London | TheMall.jpg
London | BuckPalace.jpg
France | BigBen.jpeg
France | TowerBridge.jpeg
France | TheMall.jpg
France | BuckPalace.jpg
Your php loop could look something like this:
<?php
// $db_id is a variable created from a user login $_SESSION value
$sql = "SELECT boards.board_name, images.filename
FROM boards
INNER JOIN boards_images on boards_images.board_id = boards.board_id
INNER JOIN images on boards_images.image_id = images.image_id
WHERE boards.user_id = :user_id";
$stmt = $connection->prepare($sql);
$stmt -> execute([
':user_id' => $db_id
]);
$dbBoardname_last = '';
$imgcount = 0;
while ($row = $stmt->fetch()) {
$dbBoardname = htmlspecialchars($row['board_name']);
$dbImageFile = "$wwwRoot/images-lib/" . htmlspecialchars($row['filename']);
//if the boardname is the same as the previous row only add images.
if($dbBoardname != $dbBoardname_last)
{
//reset the image count for new boards
$imgcount = 0;
echo "<div class=\"board-component\"><h2>$dbBoardname</a></h2>";
}
//By counting the images within the loop we can avoid using multiple or nested queries to the DB
if($imgcount < 4) {
echo "<img src=\"$dbImageFile\">";
}
$imgcount++;
if($dbBoardname != $dbBoardname_last)
{
echo '</div>';
}
//record the last board_name to check if a new board element should be created
$dbBoardname_last = $dbBoardname;
}
?>
Note: hopefully this code works as you intended but long term I think best practice would be to parse the SQL output into a JSON object and iterate over that instead, the code might come out cleaner