Creating a Related or Similar Posts Using PHP & MySQL

Creating a related or similar posts using PHP & MySQL

Using the MySQL Full Text search MATCH (col1,col2,...) AGAINST (expr [search_modifier]) thing.

Let's say your table is articles and you need to find related posts about a title of current post. Do it like this:

SELECT *, MATCH(title, body) AGAINST('$CurrentPostTitle') AS score
FROM articles 
WHERE MATCH(title, body) AGAINST('$CurrentPostTitle') 
ORDER BY score DESC LIMIT 5

This will give you top 5 related posts.

But first remember to enabled Full Text search for that table's columns, by running this query:

ALTER TABLE articles ADD FULLTEXT (title, body);

[EDIT]: Why to not use LIKE: Clarification to OP:

Because it will not give correct results. Let's say you current title is "Music of 1980" and you want related posts on that. Now, if you use LIKE then only the posts containing EXACTLY the sequence of words "Music of 1980" will appear. However, if you use MATCH ... AGAINST, then posts that contain Music OR 1980 will appear. Also, the posts that contain both Music and 1980 will appear on Top because it gives a SCORE to each results and we are sorting by that score.I hope that's clear.

[EDIT]: 2:

If you have categories, you can add AND Category = '$CurrentCategory' in the SQL query where clause to get more specific results.

[EDIT]: 3: OP can't use Full text:

If you can not use Full Text (for some reason), you can just show 5 random posts from the same category. As they are in same category, they are somehow related at least:

SELECT *
FROM articles 
WHERE Category = '$CurrentCategory'
LIMIT 5

Edited Syntax: Changed LIMTI to LIMIT in MySQL Code

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

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.

Creating a tag-based related links feature using PHP and MySQL

The inclusion of the + character in your $searchstring is what forces all tags to be present. If you put in just the words and omit the +, the engine will rank its results without requiring each word to be present.

Take a look at the docs for fulltext searching in MySQL.

You have many options with each word. + will force the word to be found somewhere in the result, - will force the word to not be found anywhere in the result, ~ will allow a word to be found but lower the result's ranking if it is found. Read the docs, they're very useful.

PHP/MYSQL to create similar posts type script (similar images)

If you're mainly wondering how you determine similarity, then you can do that with a text search in MySQL. I also found this page if you want to go to the trouble of building a tagging database schema. The former is simpler, but the latter is a really useful if you want to practice joins.

mySQL + php, Show related posts to a tag from another table in a loop

Just make a second query:

// Query tags
$q = mysql_query("...");

while($result = mysql_fetch_array($q)) { 
    echo $result['tag'],'<br />';
    // Query comments
    $q1 = mysql_query("SELECT * FROM comments WHERE ... LIMIT 5");
    while($result = mysql_fetch_array($q)) {
        echo $result['comment'],'<br />';
    }
}

---

Related Topics