Asked  7 Months ago    Answers:  5   Viewed   27 times

I was looking around SO and was wondering how would I go about in creating a related or similar posts to display on my web site using PHP & MySQL? What is a basic example of how to do this?

 Answers

67

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

Wednesday, March 31, 2021
 
PHLAK
answered 7 Months ago
17

Use mysqli_set_charset to change the client encoding to UTF-8 just after you connect:

$mysqli->set_charset("utf8");

The client encoding is what MySql expects your input to be in (e.g. when you insert user-supplied text to a search query) and what it gives you the results in (so it has to match your output encoding in order for echo to display things correctly).

You need to have it match the encoding of your web page to account for the two scenarios above and the encoding of the PHP source file (so that the hardcoded parts of your queries are interpreted correctly).

Update: How to convert data inserted using latin-1 to utf-8

Regarding data that have already been inserted using the wrong connection encoding there is a convenient solution to fix the problem. For each column that contains this kind of data you need to do:

ALTER TABLE table_name MODIFY column_name existing_column_type CHARACTER SET latin1;
ALTER TABLE table_name MODIFY column_name BLOB;
ALTER TABLE table_name MODIFY column_name existing_column_type CHARACTER SET utf8;

The placeholders table_name, column_name and existing_column_type should be replaced with the correct values from your database each time.

What this does is

  1. Tell MySql that it needs to store data in that column in latin1. This character set contains only a small subset of utf8 so in general this conversion involves data loss, but in this specific scenario the data was already interpreted as latin1 on input so there will be no side effects. However, MySql will internally convert the byte representation of your data to match what was originally sent from PHP.
  2. Convert the column to a binary type (BLOB) that has no associated encoding information. At this point the column will contain raw bytes that are a proper utf8 character string.
  3. Convert the column to its previous character type, telling MySql that the raw bytes should be considered to be in utf8 encoding.

WARNING: You can only use this indiscriminate approach if the column in question contains only incorrectly inserted data. Any data that has been correctly inserted will be truncated at the first occurrence of any non-ASCII character!

Therefore it's a good idea to do it right now, before the PHP side fix goes into effect.

Wednesday, March 31, 2021
 
superhero
answered 7 Months ago
68

Try getting all of the post vars and looping through them to see if they are valid, and then build your query

<?php
$id = $_POST[id];
$field1 = $_POST[field1];
$field2 = $_POST[field2];
$field3 = $_POST[field3];

$whereArr = array();
if($id != "") $whereArr[] = "id = {$id}";
if($field1 != "") $whereArr[] = "field1 = {$field1}";
if($field2 != "") $whereArr[] = "field2 = {$field2}";
if($field3 != "") $whereArr[] = "field3 = {$field3}";

$whereStr = implode(" AND ", $whereArr);

$query = "Select * from assignments WHERE {$whereStr}";

Something like that should handle what you need

Saturday, May 29, 2021
 
pinaki
answered 5 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
36

I have this problem often, and I solved it with a simple solution : an extra column called Sort Order (or DisplayOrder, whatever floats your boat really) . This allows me the flexibility to use auto-generated, auto-incremented ID column and have a special pre-defined sort.

In my case, I need them to come out of the database with an alphabetical order except that some items like "Other" and "N/A" are always last.

ProdID ProdText SortOrder
2      "Anchovies"    1
3      "Rivet"        2
4      "N/A"          4
5      "Other"        3

SELECT ProdID, ProdText ORDER BY Sort Order
Friday, October 1, 2021
 
user27815
answered 3 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :