Mysql Query With Php To Match Keywords And Rank According To Most Match

[phpsane]

My Dear Php Buddies,

 

Consider a mysql tbl index.tbl that looks like this with entries:

id | Url | Description

0| http://cheapnames.it | Register Domain Names from $1 to $25.

1|http://lownames.usa | Get Domain Names from $1 to $20.

2| http://lowcostnames.uk | Register Domains for as low as $1.

3| http://getdoman.biz | Register Names from between $1 to $25.

 

Now, imagine you did a search for "register domain names".

Now you can see the keywords matches in bold below:

id | Url | Description

0| http://cheapnames.it | Register Domain Names from $1 to $25.

1|http://lownames.usa | Get Domain Names from $1 to $20.

2| http://lowcostnames.uk | Register Domains for as low as $1.

3| http://getdoman.biz | Register Names from between $1 to $25.

 

Now, I want the query to rank the rows based on the most matched keywords. So, from our example, the ranking should be like this:

0| http://cheapnames.it | Register Domain Names from $1 to $25.

1|http://lownames.usa | Get Domain Names from $1 to $20.

3| http://getdoman.biz | Register Names from between $1 to $25.

2| http://lowcostnames.uk | Register Domains for as low as $1.

 

Note that, the id=0 has 3 KWs matches. Hence on top.

Both the id=1 & id=3 have 2 matches. Hence 2nd & 3rd. Amongst these 2, priority of rank is given based on "id" (link submission id).

And ofcourse, the last matched row is at the bottom.

Q1a. How would you write the Sql query be doing the querying & ranking like I described ?

Q1b. Is just the Sql querying enough here to do the ranking display like described or have to get php to do the ranking display aswell ? Or, maybe Sql should not be doing the ranking display atall but php ? In that case, my I see a php sample code ?

This no good which I got so far:

	$query = "SELECT * FROM notices WHERE description = register  AND Domain AND Names"; 
	

Edited November 13, 2018 by phpsane

[Barand]

A good place to start would be mysql fulltext searches

[phpsane]

Just now, Barand said:

A good place to start would be mysql fulltext searches

Thanks.

Checking  it out now.

Bearing in mind that the link description would be limited like you see in searchengine result pages (SERPs), should I choose VARCHAR(255) or  TinyText ?

[Barand]

I'd stick with varchar() unless you need 1000's of characters.

[phpsane]

2 minutes ago, Barand said:

I'd stick with varchar() unless you need 1000's of characters.

Cheers!

Reason why I asked because the link you pointed deals with FullText.

[Barand]

Suppose we start with this data in table "notices"

+------------------------+---------------------------------------+
| link                   | description                           |
+------------------------+---------------------------------------+
| http://gopapa.uk       | Domains for as low as $1              |
| http://cheapnames.it   | Register domain names from $1 to $25  |
| http://lownames.usa    | Get domain names from $1 to $20       |
| http://lowcostnames.uk | Register domains for as low as $1     |
| http://petemporium.com | Puppies and kittens free              |
| http://gamblersrus.com | Bingo, roulette and poker             |
| http://gomama.uk       | Register your domain name for $1      |
| http://getdoman.biz    | Register names from between $1 to $25 |
+------------------------+---------------------------------------+

and our keywords are "register", "domain" and "name". Some of the above contain "domains" or "names" so we amend out keyword list to

 'register domain* name*' 

Then with a fulltext search on the description column (assuming the fulltext index has been defined in the table)

SELECT id
     , link
     , description
     , MATCH(description) against ('register name* domain*' IN boolean mode) as score
FROM notices
ORDER BY score DESC;

+----+------------------------+---------------------------------------+----------------------+
| id | link                   | description                           | score                |
+----+------------------------+---------------------------------------+----------------------+
|  1 | http://cheapnames.it   | Register domain names from $1 to $25  |  0.04764682427048683 |
|  6 | http://gomama.uk       | Register your domain name for $1      |  0.04764682427048683 |
|  4 | http://getdoman.biz    | Register names from between $1 to $25 | 0.038255274295806885 |
|  2 | http://lownames.usa    | Get domain names from $1 to $20       |  0.02851918712258339 |
|  3 | http://lowcostnames.uk | Register domains for as low as $1     |  0.02851918712258339 |
|  5 | http://gopapa.uk       | Domains for as low as $1              | 0.009391550906002522 |
|  7 | http://petemporium.com | Puppies and kittens free              |                    0 |
|  8 | http://gamblersrus.com | Bingo, roulette and poker             |                    0 |
+----+------------------------+---------------------------------------+----------------------+

 

[Barand]

There is also an alternative solution you could use without FULLTEXT. EG (using same data)

SELECT id
     , link
     , description
     , IF(INSTR(description,'register'),1,0)
        + IF(INSTR(description,'domain'),1,0)
        + IF(INSTR(description,'name'),1,0)  as rank
FROM notices
ORDER BY rank DESC;

+----+------------------------+---------------------------------------+------+
| id | link                   | description                           | rank |
+----+------------------------+---------------------------------------+------+
|  1 | http://cheapnames.it   | Register domain names from $1 to $25  |    3 |
|  6 | http://gomama.uk       | Register your domain name for $1      |    3 |
|  2 | http://lownames.usa    | Get domain names from $1 to $20       |    2 |
|  3 | http://lowcostnames.uk | Register domains for as low as $1     |    2 |
|  4 | http://getdoman.biz    | Register names from between $1 to $25 |    2 |
|  5 | http://gopapa.uk       | Domains for as low as $1              |    1 |
|  7 | http://petemporium.com | Puppies and kittens free              |    0 |
|  8 | http://gamblersrus.com | Bingo, roulette and poker             |    0 |
+----+------------------------+---------------------------------------+------+