Hiya,
I need to learn the SQL query that counts all the points from more than one column and orders the matching rows based on most points in descending order.
EXAMPLE 1:
I do a keyword search for "mobile phone tutorial apps". Note 4 words.
Sql should find all the rows that contain any of these 4 keywords.
That means, since I searched for 4 keywords, sql should even pull-up those rows that just have even 1 or 2 or 3 of these 4 keywords in them.
Say, my mysql table looks like this:
id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
----------------------------------------------------------------------------------------------
0 | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2
----------------------------------------------------------------------------------------------
1 | mobile | 1 | phone | 1 | apps | 3 | tutorial | 3
----------------------------------------------------------------------------------------------
2 | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3
----------------------------------------------------------------------------------------------
3 | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5
NOTE: All 4 keywords exists on the first 3 matching rows. However, only 3 words exist in the final matching row.
And the final matching row's keywords are not in the order of my keyword search.
here in Q1, this should not matter. Sql should ignore in which order the keywords are in each column when comparing the order of my searched keywords. All it should do, is:
A). Find matching rows, regardless of how many of my searched keywords exist on each row;
B). Count the totals of each points, (count more than one column in this case), in each row; And
C) List the rows in the point's descending order.
As you can see, from my example, the following are the keyword points of each row after calculating ALL their keyword points (all point columns):
id 0 = 10 points
id 1 = 8 points
id 2 = 11 points
id 3 = 20So, in this case, the SQL query should present rows in this descending order:
id 3 = 20
id 2 = 11 points
id 0 = 10 points
id 1 = 8 points
id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
----------------------------------------------------------------------------------------------
3 | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5
----------------------------------------------------------------------------------------------
2 | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3
----------------------------------------------------------------------------------------------
0 | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2
----------------------------------------------------------------------------------------------
1 | mobile | 1 | phone | 1 | apps | 3 | tutorial | 3
----------------------------------------------------------------------------------------------Had there been only a single "keyword point" (kw1_point) to calculate, then I would have written the SQL like this using prepared statements:
$sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? order by kw1_point desc LIMIT 100";The confusion arises when there is more than one column's values to count.
Now the next question ...