I am trying to join two tables together. In posts table I have two column post_id and post_message.
In another table name post_attach have row_id, postid(same as post_id), and file_name( As like ms.jpg).
A post may have several attachment.But I don't know what will be query to show a post with several attachment. Here is my query...
$s = " SELECT posts.post_id,post_message, post_attach.file_name from posts join post_attach on post_id=row_id;
<?php
$conn = mysqli_connect('localhost', 'root', 'root', 'blog') or die();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
</head>
<body>
<h3>Allo post with their attahcment</h3>
<?php
$s = " SELECT posts.post_id,post_message, post_attach.file_name from posts join post_attach on post_id=row_id;
";
$q = mysqli_query($conn,$s);
while ($row = mysqli_fetch_array($q)) {?>
<p>Post No: <?php echo $row['post_id'] ?></p>
<p>Post text: <?php echo $row['post_message'] ?></p>
<p>Post attach: <?php echo $row['file_name'] ?></p>
<?php } ?>
</body>
</html>
I want something like:
post no: 1, post text: hello php, attachment: ms.jpg, ms.jpeetc.
The final word is every post will show with their relative attachment.
I wouldn't join the tables outright. I would iterate through the main posts table and show the post id and post message, then loop through the attachments. Something like:
<?php
$s = "SELECT posts.post_id,post_message FROM posts";
$q = mysqli_query($conn,$s);
while($row = mysqli_fetch_array($q))
{
?>
<p>Post No: <?php echo $row['post_id'] ?></p>
<p>Post text: <?php echo $row['post_message'] ?></p>
<p>
Post attach:
<?php
$att = "SELECT post_attach.file_name FROM post_attach WHERE post_id = $row[post_id]";
$files = mysqli_query($conn,$att);
while($row_files = mysqli_fetch_array($files))
{
echo $row_files['file_name'] . "<br>";
}
?>
</p>
<?php
}
?>