I have looked at similar questions but I could not find any answers that worked for my specific situation so I hoped asking a new one would be of help. So I have 3 tables: posts, en, images. posts is the main table and the other two connect to it with a foreign key. the row, "path" comes from the image table and there is several image paths bound to each post. so this is where the problem comes in because in the loop, all of the information about category, title and text is repeated for each image path that belongs to that one post entry. is there a way to make sure the query only prints each data point related to a row only once? here is the code for better understanding:
$sql = "
SELECT *
FROM posts
INNER JOIN en
ON posts.id = en.post
INNER JOIN images
ON posts.id = images.post";
$result = $conn -> query($sql);
if (mysqli_num_rows($result) > 0) {
while( $row = $result->fetch_assoc()) {
echo "<p>".$row['category']."</p>";
echo "<p>".$row['title']."</p>";
echo "<p>".$row['text']."</p>";
echo "<p>".$row['path']."</p>";
}
}else{
echo "<h1>No posts available...</h1>";
}
results:
category: 0
title: Blue Boat
text: “Blue Boat» was first made in 1986
image path: img/2/0.jpg
category: 0
title: Blue Boat
text: “Blue Boat» was first made in 1986
image path: img/2/1.jpg
category: 0
title: Blue Boat
text: “Blue Boat» was first made in 1986
image path: img/2/3.jpg
as you see, the post is repeated for each time there is an image path tied to its id. how do I prevent this? I want something like this to be the result:
category: 0
title: Blue Boat
text: “Blue Boat» was first made in 1986
image path: img/2/0.jpg
image path: img/2/1.jpg
image path: img/2/3.jpg
Perhaps you want aggregation. Assuming you are using MySQL, you can concatenate the image paths into a string:
SELECT p.category, p.title, p.text, GROUP_CONCAT(i.path)
FROM posts p INNER JOIN
en
ON p.id = en.post INNER JOIN
images i
ON p.id = i.post
GROUP BY p.category, p.title, p.text;