TheStudent2023

@kicken Sorry for the late reply. I did not logon yesterday. I understand your table structure. Good for a searchengine. However, I am an intermediate level student and I have a list of SQL query basics which I must learn. And so, I just structured that table that you see, to get my point across to you fine folks what I intend to learn. And so bare with me and try aiding my learning as much as possible. I made one serious mistake on my ORIGINAL POST which someone pointed-out to me. I was intending to ONLY COUNT the POINTS of MATCHING KEYWORDS on EACH ROW/RECORD. And NOT COUNT the POINTS of the non-MATCHING KEYWORDS on the MATCHING RECORDS before assigning the point score to the record. And then RANK the MATCHING records in DESC ORDER. So, how to DO IT ? The SQL, I mean ? Maybe you are confused and so let us draw-up a new table example here for your convenience and mine. Say, I did an EXACT MATCH (no wildcard) keywords/keyphrase search for: php book usa Or, php+book+usa Now, it should not matter in what arrangements the searched keywords are in OR how many MATCHING keywords exist in a record. Aslong as any keyword exists in a record, that record should be presented to the searcher. Say my table records look like this: Table: Links id | url | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point 0 | kicken.com | usa | 3 | phone | 3 | apps | 2 | tutorial | 2 1 | phpfreaks.com| uk | 1 | php | 4 | apps | 3 | price | 3 2 | borobhaisab.com | tutorial | 3 | book | 3 | php | 2 | usa | 3 3 | php.com | PHP's | 5 | books | 5 | united states america | 5 | prices | 5 4 | php.com | python | 5 | book | 5 | apps | 5 | usa | 5 I have highlighted the matching keywords. NOTE 1: Notice that the row, id:3, actually got the matching keywords. But since the keywords are not EXACT, then SQL should NOT draw-up this record. PHP's is not an exact match of php. Books is not an exact match for book. united states america is not an exact match for usa. Hence, record: id:3, should be IGNORED. Also notice record id:4. It got php.com. But that is not an exact match for php. Hence, that Mysql cell should be ignored too. NOTE 2: Record id: 2 got 3 matches, while record id: 4 got 2. However, record id:3 scores a total of 10 points while record id:2 scores 8. Hence, in this case, record 1d:3 should get better ranking when presented to the keyword searcher. Because ranking should not be based on how many keywords matched in a record but total points of all matching keywords in a record. So, this is how the SQL should rank things before presenting the records to the keyword searcher: Result ... 4 | php.com | python | 5 | book | 5 | apps | 5 | usa | 5 -------------------- 10 point 2 | borobhaisab.com | tutorial | 3 | book | 3 | php | 2 | usa | 3 --------------------- 8 point 1 | phpfreaks.com| uk | 1 | php | 4 | apps | 3 | price | 3 ---------------------- 4 point 0 | kicken.com | usa | 3 | phone | 3 | apps | 2 | tutorial | 2 ---------------------- 3 point The total point section on the right on the above presented result, is for your convenience ONLY to understand things how I want done. Obviously, I do not expect SQL to echo points in that manner. I can write php to do that, if I really want to. You may have a question, why should SQL present record id: 0 here, since the record is totally irrelevant. ANSWER: It does not matter if the record is irrelevant or not to the keyword search in such examples. There is an EXACT matching keyword here "usa". And so, this record counts in this EXACT MATCH query. So, how to write the SQL ? Thanks for your time & effort.

No one got any clue why this failed ? ```` $sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? ORDER BY (kw_1_point+kw_2_point+kw_3_point+kw_4_point) DESC ````

@Ginerjm CORRECTION: This failed: ```` $sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? ORDER BY (kw_1_point+kw_2_point+kw_3_point+kw_4_point) DESC ```` On my previous post, that got truncated for some reason.

@ginerjm My tables looked like these users members domains links Changed the links to keywords. It had cols like this: id|domain|url|title|meta_keywords|meta_description| But I did not want to add more than one keyword in the same row/record (under "meta keywords column") and I did not want to present results to my searchengine users based on "id" cols ranking. And so ... Now I I have changed it to something like this to give each keyword a point per record (link): id|domain|url|title|kw_1|kw_1_point|kw_2|kw_2_point|kw_3|kw_3_point|kw_4|kw_4_point| Anyway, how to write the SQL for it to calc foolowing rows number values kw_1_point, kw_2_point, kw_3_point & kw_4_point And then rank the rows based on points (number values) in DESC order ? This failed: ```` $sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? order by kw1_point desc ```` It only presents the first row (id: 0) even though other rows fot matching keywords of my search. And yes, building a searchengine.

@gula Put this code at the top of the page and then try submitting. See if you get any errors. If so, copy & paste here: ```` ini_set("display_errors",1); ini_set("display_startup_errors",1); error_reporting(E_ALL); ````

@ginerjm I am putting one phrase per row or record. One keyword per column. Multiple keywords per row. So, if I got this phrase: "ginerjm pro deveoper". Then my script will break the phrase up and put each individual keyword on separate columns like so: id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point 0 | ginerjm | 5 | pro | 100 | developer | 50 As you can see, the whole phrase is in one record or one row. But each individual keyword per column. And I give each keyword a point. Now, all I need to learn is to count all points (from all cols) per row/reord and rank them according to their points. Need the sql that does this.

@ginerjm Did I answer you ok ?

No. kw stands for "keyword_1". So, it is a column. "kw_1_point" is another column. "kw_2" is another column. "kw_2_point" is another column. "kw_3" is another column. "kw_3_point" is another column. "kw_4" is another column. "kw_4_point" is another column.

EXPLANATION of above post ... Had there been only a single "keyword point" (kw1_point) to calculate, then I would have written the SQL like following using prepared statements: ```` $sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? order by kw1_point desc LIMIT 100"; ```` EXAMPLE 1: Anyway, 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 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. NOTE: All 4 keywords exists on above first 3 matching rows. But 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 point id 1 = 8 point id 2 = 11 point id 3 = 20 point So, 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 Like so: 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

Great Evening! My first post now after regd. Need the SQL query to counts all points from 1+ column and order matching rows based on most points in descending order. How to do this ?

Good Evening! Just registered to learn php from you people. Trying to build a searchengine & spider. Thank You!