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 } ?>
Advertisement
Answer
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.