Display related posts from the same category. Using PHP and MySQL

Question

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

POST_ID	CAT_ID	TITLE	DESCRIPTION	IMAGE
1	3
2	5
3	1
4	6

CATEGORIES table

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 class="col-lg-4">
        <div class="sidebar">
            <div class="sidebar-widget">

                <h2 class="sw-title">Related Posts</h2>

                <div class="news-list">

    <?php while ($row = mysqli_fetch_assoc($result1)) { ?>
                    
                    <div class="nl-item">
                        <div class="nl-img">

                                <img src="<?php if (file_exists("upload/" . $row['image'])) {
                                                echo "upload/" . $row['image'];
                                            } else {
                                                echo "upload/" . $row['image'];
                                            } ?>" alt="" class="img-fluid">

                        </div>

                        <div class="nl-title">

                            <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 } ?>

Modify your query to fetch records by `CAT_ID`, from the original `POST_ID` record. A nested query. — जलजनक, Feb 24 '22 at 18:08
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Feb 24 '22 at 23:00

medilies · Accepted Answer · 2022-02-25T14:22:33.987

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.

That's a nice structure, however, OP stated he only has 2 tables, so while this would be a nicer design, it is not the structure they have. — gview, Feb 24 '22 at 18:33
@gview sure I answered according to his current DB structure in the first place, but expanded what the futur may hide — medilies, Feb 24 '22 at 18:40
Yes, you did, thumbs up. With that said, you should add a php code snippet showing how to use your query with the existing code, as the op is clearly floundering. — gview, Feb 24 '22 at 19:00
Hi Medilies, I used your above query codes but it displayed only one related post from the same category... — Safin, Feb 24 '22 at 20:04
@safin try `mysqli_fetch_all($related_posts, MYSQLI_ASSOC)` and give me the feedback. if it works I'll edit my work : — medilies, Feb 24 '22 at 20:21
You are welcome. But you should improve how you design your futur database, learn about MVC to structure your code and maybe learn a framework like Laravel. And consider the security issues like the mentionned SQL injection here. good luck :) — medilies, Feb 25 '22 at 15:31

Display related posts from the same category. Using PHP and MySQL

1 Answers1

RECREATING YOUR DATABASE

Solution

Relational database for blog