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

CATEGORIES table

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=<?= $row['POST_ID']; ?>" title="<?= $row1['title']; ?>"><?= $row1['title']; ?></a>
                            </h4>

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

<?php } ?>

CodePudding user response：

RECREATING YOUR DATABASE

Next time you should provide the code to recreate the portion of the code that you are having a problem with!

CREATE TABLE CATEGORIES(
    Cat_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    Cat_name VARCHAR(50),
    Total_post INT
);

CREATE TABLE POST(
    POST_ID BIGINT PRIMARY KEY AUTO_INCREMENT,
    CAT_ID BIGINT,
    TITLE VARCHAR(50),
    DESCRIPTION TEXT,
    FOREIGN KEY (CAT_ID) REFERENCES CATEGORIES(CAT_ID)
);

INSERT INTO
    `categories`(`Cat_id`, `Cat_name`, `Total_post`)
VALUES 
    (3, 'national', 5),
    (5, 'International', 7),
    (1, 'Sports', 3),
    (6, 'Technology', 2);

$db_connection = mysqli_connect($db_host, $db_user, $db_password, $db_name);

Solution

/* ********************************* */
// GETTING POST ID
/* ********************************* */
$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;
}

/* ********************************* */
// FETCHING THE POST
/* ********************************* */

$main_post = mysqli_query(
    $db_connection,
    "SELECT * FROM POST WHERE POST_ID=$post_id"
);

$main_post_data = NULL; // Will become the associative array

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

/* ********************************* */
// FETCHING RELTED POSTS
/* ********************************* */
$category_id = $main_post_data['CAT_ID'];

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

$related_posts_data = [];

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

/* ********************************* */
// THE END
/* ********************************* */
echo "<pre>";
print_r($main_post_data);
print_r($related_posts_data);
echo "</pre>";
die;

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.