I have a blog site in PHP and MySQL with tow table, POST and CATEGORIES. I need help, how to display related post from same category when visitor clicked on a single post title. your help will be greatly appreciated

POST table structure:

POST_ID ----- CAT_ID ---- TITLE ---- DESCRIPTION ---- IMAGE ---- VIEWS
 1.            3          
 2.            5
 3.            1
 4.            6

CATEGORIES table structure:

Cat_id    Cat_name        Total_post
 3        National           5
 5        International      7
 1        Sports             3
 6        Technology         2

Here is my Code:

<?php
if (isset($_GET['POST_ID'])) {
$POST_ID = $_GET['POST_ID'];
}
$sql1 = "select * from POST where POST_ID=$POST_ID";
$result1 = mysqli_query($conn, $sql1);
if (mysqli_num_rows($result1) > 0) {
?>

<div >
<div >
<div >
<h2 >Related Posts</h2>
<div >

<?php
while ($row = mysqli_fetch_assoc($result1)) {
?>
<div >
<div >
<img src="<?php if (file_exists("upload/" . $row['image'])) { echo "upload/" . $row['image']; } else { echo "upload/" . $row['image']; } ?>" alt="" >
</div>
<div >

<h4><a href="single-page.php?POST_ID=<?php echo $row['POST_ID']; ?>" title="<?php echo $row1['title']; ?>"><?php echo $row1['title']; ?></a></h4>

</div>
</div>
<?php } ?>
</div>
</div>
<?php } ?>

CodePudding user response：

Related Posts query

$post_id = NULL;

if (!empty($_GET['POST_ID'])) {
  $post_id= intval($_GET['POST_ID']); // Making sure that we received an integer ID
} else {
  echo "A post ID must be provided!";
  die;
}

$main_post_query = "select * from POST where POST_ID=$post_id";
$main_post = mysqli_query($conn, $main_post_query);
$main_post_data = NULL; // Will become the associative array

if (mysqli_num_rows($main_post) > 0) {
  $main_post_data = mysqli_fetch_assoc($main_post);
} else {
  echo "Post not found";
  die;
}

$category_id = $row['CAT_ID'];

$related_posts_query = "
SELECT
    *
FROM
    POST
WHERE
    CAT_ID = $category_id
    AND
    POST_ID != $post_id
";
$related_posts = mysqli_query($conn, $related_posts_query );
$related_posts_data = [];

if (mysqli_num_rows($related_posts) > 0) {
  $related_posts_data = mysqli_fetch_all($related_posts , MYSQLI_ASSOC);
}

Relational database for blog

Basically:

A post may belong to many categories.

For example Lastest windows vulnerabilities post may belong to tech and hacking categories

A category may reference many posts

For exmaple tech category may have Lastest windows vulnerabilities and top 10 PHP frameworks in 2022 posts

That requires a bridge table between the two entities.

CodePudding user response：

"select * from POST where CAT_ID=" . $row['cat_id'] . " and id !=" . $POST_ID