Possible To Add Sums Of Many Tbl Cols With Sql ?

[TheStudent2023]

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 ?

Edited April 9, 2023 by TheStudent2023

[TheStudent2023]

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

[ginerjm]

Are kw1, kw2, kw3.....  the same piece of data?  Like phone1 phone2 phone3?   Cause if they are you have a bad database design.

[TheStudent2023]

16 minutes ago, ginerjm said:

Are kw1, kw2, kw3.....  the same piece of data?  Like phone1 phone2 phone3?   Cause if they are you have a bad database design.

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.

[TheStudent2023]

@ginerjm

Did I answer you ok ?

[ginerjm]

No you did not.  Of course - I Know it is a column. 

My question pertains to "are you putting multiple values of something into a single record".  A table should contain individual values/attributes of something in one record.  Where there are multiple values of that attribute (such as a keyword?) they should be in their own table, tied to the primary via a key such as the primary id/key. 

[TheStudent2023]

15 hours ago, ginerjm said:

No you did not.  Of course - I Know it is a column. 

My question pertains to "are you putting multiple values of something into a single record".  A table should contain individual values/attributes of something in one record.  Where there are multiple values of that attribute (such as a keyword?) they should be in their own table, tied to the primary via a key such as the primary id/key. 

@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.

 

Edited April 10, 2023 by TheStudent2023

[ginerjm]

You should have a table of 'users' where you put my name and some other 'personal' info pertaining to me alone.  Then you should have a keywords table containing all possible keywords along with their point value (which I am sure you will continue to add new records as you come up with new keywords) and lastly a table that contains usernames (as specified in table 1) and keywords from table 2.   Then you do a query for a username and all of his assigned keywords from table 3 that does your summing.

[TheStudent2023]

3 hours ago, ginerjm said:

You should have a table of 'users' where you put my name and some other 'personal' info pertaining to me alone.  Then you should have a keywords table containing all possible keywords along with their point value (which I am sure you will continue to add new records as you come up with new keywords) and lastly a table that contains usernames (as specified in table 1) and keywords from table 2.   Then you do a query for a username and all of his assigned keywords from table 3 that does your summing.

@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.

 

Edited April 10, 2023 by TheStudent2023

[TheStudent2023]

@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.

 

Edited April 10, 2023 by TheStudent2023

[TheStudent2023]

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]

One does NOT put multiple 'same' values into one record.  AS MY POST DEMONSTRATED.  One user may have 3-4-5-or-even-10 rows in table #3 if that is what it takes.  He does not have one row with 10 keywords in it.  Period.

[kicken]

4 hours ago, TheStudent2023 said:

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|

This was a mistake.  If you find your self making multiple columns for the same type of data, what you really want is another table.

A more proper change would have been to create a second link_keywords table with a reference to the link ID.  Like so:

links: id|domain|url|title

link_keyword: link_id|keyword|points

With that setup, your query would then be simple, like:

select * 
from links
inner join link_keyword on link_keyword.link_id=links.id
where 
    link_keyword.keyword = ?
order by 
    link_keyword.points desc

If you want to have a global list of keywords and point values that is shared across all your links, then you'd have three tables, such as:

links: id|domain|url|title

link_keyword: link_id|keyword_id

keywords: id|keyword|points

The adjust the query with another join as appropriate.

 

[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.

[kicken]

1 hour ago, TheStudent2023 said:

However, I am an intermediate level student and I have a list of SQL query basics which I must learn.

You won't learn anything useful about SQL pursuing this design because the design is fundamentally flawed and directly opposed what SQL is designed to do.

1 hour ago, TheStudent2023 said:

So, how to write the SQL ?

My answer is that you don't.  I'm sure there is a way for it to be done, but doing so would teach you nothing useful. 

The correct solution to your problem is to re-design your table structure, then use SQL as it's intended to be used rather than fight against it trying to make a poor design work.

1 hour ago, TheStudent2023 said:

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

This shows yet another potential reason why your design is flawed.  Why do you have multiple rows for the same link?  If the answer is "To have more than 4 key words" then that's wrong. The multi-table solution gives you the ability to have an unlimited number of keywords per link.

Edited April 12, 2023 by kicken

[TheStudent2023]

@ginerjm

I have another SQL query similar to the SQL query I asked for on my previous post to kicken.
I do not think it will be fair on kicken to work out for me 2 sophisticated SQL queries and so I hope you won't mind me expecting you to show me how this 2nd SQL should be ?

You see, here, I am searching for an SQL that will search for records with EXACT MATCHING keyword per column but RANK the records first based on number of matching keywords found per record and then based on score per record.

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 BUT ...
1. how many MATCHING keywords exist in a record. (1st priority).
2. total score of record of matching keywords' points.

Aslong as any keyword exists in a record, even one matching keyword, 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 | kickenDOTcom | **usa** | 3 | phone | 3 | apps | 2 | tutorial | 2
1 | mypointDOTcom| uk | 1 | **php** | 4 | apps | 3 | price | 3
2 | borobhaisabDOTcom | tutorial | 3 | **book** | 3 | **php** | 2 | **usa** | 3
3 | phpDOTcom | PHP’s | 5 | books | 5 | united states america | 5 | prices | 5
4 | pythonDOTcom | 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**.
And so, even though record *id:4* got more points, you can clearly see that, it has less matching keywords than record **id:2**.
Hence, in this case, record **1d:2** should get **better ranking** when presented to the keyword searcher.

Because here, in this 2nd SQL I seek, ranking should **BE based on how many keywords matched** in a record AND after that, ranking should be based on **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 ...**
2 | borobhaisabDOTcom | tutorial | 3 | **book** | 3 | **php** | 2 | **usa** | 3      **-------8 point**
4 | pythonDOTcom | python | 5 | **book** | 5 | apps | 5 | **usa** | 5      **-------------------- 10 point**
1 | mypointDOTcom| uk | 1 | **php** | 4 | apps | 3 | price | 3               **---------------------- 4 point**
0 | drumminDOTcom | **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.

[TheStudent2023]

Folks,

Reading my two previous posts may confuse you.
You may think I am asking the same question or asking for the same SQL here.
But I am not. If you notice the result … of both posts and glance at the two final tables on both posts then things will become clear.
And so, I asked kicken one question and asked ginerjm a different question. They are similar but not same.
You are welcome to chime in and answer my above two posts if you wish.

[TheStudent2023]

35 minutes ago, kicken said:

You won't learn anything useful about SQL pursuing this design because the design is fundamentally flawed and directly opposed what SQL is designed to do.

My answer is that you don't.  I'm sure there is a way for it to be done, but doing so would teach you nothing useful. 

The correct solution to your problem is to re-design your table structure, then use SQL as it's intended to be used rather than fight against it trying to make a poor design work.

This shows yet another potential reason why your design is flawed.  Why do you have multiple rows for the same link?  If the answer is "To have more than 4 key words" then that's wrong. The multi-table solution gives you the ability to have an unlimited number of keywords per link.

@kicken

Ok. You spotted right here. More than one link in the same table "phpDOTcom".

Ok, then let us change this to UNIQUE where one url per table.

Now, even though you deem my table arrangement is UNORTHODOX , I still wish to learn how to achieve my 2 purposes with SQL that I asked you and ginerjm above. Just for my learning purpose, to satisfy my CURIOSITY, do you mind handing me the SQL ? I want to experiment and fiddle.

Then, after that, I can move onto experimenting & fiddling your WAY of doing things.

This is how I gain experience. Do things UNORTHODOX first and then ORTHODOX.

I know it is very easy for you to draw-up the SQL I asked you and I know it would be peasy for you to draw-up the SQL I asked ginerjm.

So, how-about drawing them up for me ? Let me play tonight with these 2 SQLs.

Then tomorrow, I can move-on to your ORTHODOX way of doing things.

Edited April 12, 2023 by TheStudent2023

[kicken]

54 minutes ago, TheStudent2023 said:

do you mind handing me the SQL

I'm not going to spend time writing SQL that I don't believe should be written in the first place, but if you insist on pursing this, I'll at least give you a hint.

CASE

 

Edited April 12, 2023 by kicken

[TheStudent2023]

38 minutes ago, kicken said:

I'm not going to spend time writing SQL that I don't believe should be written in the first place, but if you insist on pursing this, I'll at least give you a hint.

CASE

 

Thanks. If I get stuck, I'll pester you again.

In the meanwhile, got to hassle you some more.

Imagine, you want to build a mini searchengine. Let us see the tables and their cols that you yourself will build.

Don't bother typing the col names. Instead, if you do not mind, write the SQLs that will build the tables and their cols. And I will feed them to my mysql for them to be built on auto. Plus, I get to see the SQL commands and can learn from them. Most of all, I am curious to see what you name the tables, how many tables you create and what you name the cols and how you link them together and what SQLs you use.

Ok. You showed me something above but that was rough, which you thoughtup at the top of your head, based on my question. SO my strings were attached.

This time no strings attached. You are free to do things your way.

SQL is not my thing. I only know the basics. SELECT, DELETE, UPDATE, INSERT. That is all. Learnt them when learning to build PAGINATIONs. So that CASE thing will give me some hiccups. Checking it thoroughly right now. Had a glance for a min.

[kicken]

4 hours ago, TheStudent2023 said:

Imagine, you want to build a mini searchengine. Let us see the tables and their cols that you yourself will build.

For a basic keyword based search engine, there's nothing really to add to what was shown above.  All you need is a list of URLs and the associated keywords.

The page table stores details about the page.  Each keyword associated with the page gets inserted into the page_keyword table as it's own row.

Just to make things slightly more interesting, say you wanted to track of the links a page has.  You would add another table storing the ID of the page, an the ID of the linked page.

In addition to the above, for each link found in a page, you'd create a new entry in the page table for that link's URL then insert a record in page_link with the original page id and the newly created page id.

 

-- MySQL Script generated by MySQL Workbench
-- Thu 13 Apr 2023 12:21:03 AM EDT
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Table `page`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `page` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `url` VARCHAR(2000) NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  `last_accessed` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `UQ_page_url` (`url` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `page_link`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `page_link` (
  `page_id` INT NOT NULL,
  `linked_page_id` INT NOT NULL,
  PRIMARY KEY (`page_id`, `linked_page_id`),
  INDEX `fk_page_link_linked_page_id_idx` (`linked_page_id` ASC),
  CONSTRAINT `fk_page_link_page_id`
    FOREIGN KEY (`page_id`)
    REFERENCES `page` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_link_linked_page_id`
    FOREIGN KEY (`linked_page_id`)
    REFERENCES `page` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `page_keyword`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `page_keyword` (
  `page_id` INT NOT NULL,
  `keyword` VARCHAR(100) NOT NULL,
  `points` INT NOT NULL,
  PRIMARY KEY (`page_id`, `keyword`),
  CONSTRAINT `fk_page_keyword_page_id`
    FOREIGN KEY (`page_id`)
    REFERENCES `page` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Data for table `page`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `page` (`id`, `url`, `title`, `last_accessed`) VALUES (1, 'kicken.com', 'Kickens World', '2023-04-12 23:54:00');
INSERT INTO `page` (`id`, `url`, `title`, `last_accessed`) VALUES (2, 'phpfreaks.com', 'PHPFreaks', '2023-04-12 23:55:00');
INSERT INTO `page` (`id`, `url`, `title`, `last_accessed`) VALUES (3, 'borobhaisab.com', 'Boro', '2023-04-12 23:56:00');
INSERT INTO `page` (`id`, `url`, `title`, `last_accessed`) VALUES (4, 'php.com', 'PHP', '2023-04-12 23:57:00');

COMMIT;


-- -----------------------------------------------------
-- Data for table `page_link`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `page_link` (`page_id`, `linked_page_id`) VALUES (1, 2);
INSERT INTO `page_link` (`page_id`, `linked_page_id`) VALUES (3, 2);
INSERT INTO `page_link` (`page_id`, `linked_page_id`) VALUES (1, 4);
INSERT INTO `page_link` (`page_id`, `linked_page_id`) VALUES (3, 4);
INSERT INTO `page_link` (`page_id`, `linked_page_id`) VALUES (2, 4);

COMMIT;


-- -----------------------------------------------------
-- Data for table `page_keyword`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (1, 'usa', 3);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (1, 'phone', 3);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (1, 'apps', 2);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (1, 'tutorial', 2);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (2, 'uk', 1);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (2, 'php', 4);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (2, 'apps', 3);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (2, 'price', 3);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (3, 'tutorial', 3);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (3, 'book', 3);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (3, 'php', 2);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (3, 'usa', 3);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (4, 'PHPs', 5);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (4, 'books', 5);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (4, 'united states america', 5);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (4, 'prices', 5);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (4, 'python', 5);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (4, 'book', 5);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (4, 'apps', 5);
INSERT INTO `page_keyword` (`page_id`, `keyword`, `points`) VALUES (4, 'usa', 5);

COMMIT;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

 

DB Fiddle example.

[TheStudent2023]

@kicken

 

Thank you very much for the insight!

I am reading your latest reply nearly 2wks later! We had storm here and elec cables get ripped on the roadsides and we get current blackout. Sometimes it takes days for all cables on the streets on all local areas to get repaired in full.

Anyway, back online now.

One other thing. Someone pointed another flaw in my mysql table design as there is no way to findout which keyword matched in the row/record (in the keyword search result) since I got many keywords in the same row and some of these keywords are no matches for my keyword search. In short, SQL won't know which keywords points to count and which ones to ignore since they (non matching keyword & points & matching keywords & points) are all on the same row/record.

Anyway, I had a hunch originally, how to achieve this with php. But, I thought SQL will solve it better but it seems I was wrong. Originally, I did not want to do the link ranking after keywords points counts with php as to shorten the code plus it would be slower since php is interpreter and so thought best let the Mysql compiler do the ranking as it will be faster. But now I see Mysql does not even do what I want with my table structure. One table won't do.

Edited April 24, 2023 by TheStudent2023

[ginerjm]

You are correct.  "one table won't do".

Something is missing in your sql class teachings.  The design you have come up with is so wrong and so un-like what one is supposed to learn about in a class for sql.

[TheStudent2023]

@ginerjm

I have no real programming background. Did not attend any course in school.

Self learning at home from the likes of you in forums and from tutorial sites. That is all.

[ginerjm]

Then you need to read up on how a RDBMS is supposed to work.  Database design is important and crucial to creating a proper and helpful database.  Definitely do some reading on that before embarking on programming to use it.

And if you don't know what RDBMS means, you need to read up on that term.