MATCH AGAINST using my 'Terms' table and with 'Links'

[oz11]

Hey... 

My now normalized database looks like this..

+---------------+
| links          |
+---------------+
| link_id (PK)   |-----+
| url, title           |     |+---------------+
+---------------+     |      | terms          |
                      |      +---------------+
                      |      | id       (PK) |
                      +-----<| link_id  (FK) |
                             | term          |
                             +---------------+
                             

TABLE: links                        TABLE: terms

My code looks like this..

SELECT *, 
MATCH(terms.term) AGAINST(?) + MATCH(links.title) AGAINST(?) + MATCH(links.url) AGAINST(?) as `rank`
FROM links, terms
WHERE MATCH(terms.term) AGAINST(?) OR MATCH(links.title) AGAINST(?) OR MATCH(links.url) AGAINST(?) 
GROUP BY title ORDER BY `rank` ASC LIMIT 200

How would I go about associating my term column with the link_id and make this search function work, so that the terms are working with the 'rank' and the results work with said rank? It worked before normalization, but not sure how to make it work in this new scenario. 

Thanks, chaps.

Edited January 20 by oz11
sql

[oz11]

To illustrate see the screens below..

 

Edited January 20 by oz11

[Barand]

21 minutes ago, oz11 said:

GROUP BY title

You need to join the two tables on the link_id so you match the terms to their parent link

Do you have a specific reason for using a fulltext search? Would a simpler "LIKE" suffice. For example

SELECT l.id as linkID
     , l.url
     , t.term
FROM links l 
     JOIN terms t ON l.id = t.link_id
WHERE t.term LIKE '%ine%'
    OR l.url LIKE '%ine%';

linkID    |  url           |  term
----------+----------------+-----------------
    2     |  page2.php     |  zine
    2     |  page2.php     |  magazine
    2     |  page2.php     |  online

[oz11]

Thanks. I changed it abit just so it would make my brain work.. 

SELECT links.link_id as linkID
     , links.url
     , terms.term
FROM links 
     JOIN terms ON links.link_id = terms.link_id
WHERE terms.term LIKE '%music search%'
    OR links.url LIKE '%music search%';

However, i need it to use MATCH AGAINST so it supports multiple terms and uses a rank to order them. Think of it like a search engine.

[Barand]

Have you defined FULLTEXT indexes?

[oz11]

14 minutes ago, Barand said:

Have you defined FULLTEXT indexes?

Sorry, not too familiar with that.. what would that achieve?

Edit: found this [ https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html ] but still need help.

Edited January 20 by oz11

[oz11]

Not sure what column to set as FULLTEXT

[oz11]

SELECT *, MATCH(terms.term) AGAINST("search") + MATCH(links.title) AGAINST("search") + MATCH(links.url) AGAINST("search") as `rank` FROM links, terms WHERE links.link_id = terms.link_id AND MATCH(terms.term) AGAINST("search") OR MATCH(links.title) AGAINST("search") OR MATCH(links.url) AGAINST("search") GROUP BY title ORDER BY `rank` ASC LIMIT 200;

Hey.. added a join and it works now. Just had to dust off my university SQL brain for this one  Thanks pal.

 

Quote

You need to join the two tables on the link_id so you match the terms to their parent link

Ps. this forum has saved my ass so many times. Thanks. Love ya.

Edited January 20 by oz11

[Barand]

TABLES

CREATE TABLE `links` (                            +---------+-------------------------------+----------------------------------------------------+
  `link_id` int(11) NOT NULL AUTO_INCREMENT,      | link_id | url                           | title                                              |
  `url` varchar(45) DEFAULT NULL,                 +---------+-------------------------------+----------------------------------------------------+
  `title` varchar(100) DEFAULT NULL,              |       1 | https://ektoplazm.com/explore | Ektoplazm - Free music portal and psytrance netlab |
  PRIMARY KEY (`link_id`),                        |       2 | http://www.google.com         | Google                                             |
  FULLTEXT KEY `idx_links_url` (`url`,`title`)    |       3 | http://yahoo.com              | Yahoo                                              |
) ENGINE=InnoDB;                                  +---------+-------------------------------+----------------------------------------------------+



CREATE TABLE `terms` (                            +---------+---------+-----------+
  `term_id` int(11) NOT NULL AUTO_INCREMENT,      | term_id | link_id | term      |
  `link_id` int(11) DEFAULT NULL,                 +---------+---------+-----------+
  `term` varchar(45) DEFAULT NULL,                |       1 |       1 | radio     |
  PRIMARY KEY (`term_id`),                        |       2 |       1 | music     |
  FULLTEXT KEY `idx_terms_term` (`term`)          |       3 |       1 | psytrance |
) ENGINE=InnoDB;                                  |       4 |       1 | trance    |
                                                  |       5 |       1 | goa       |
                                                  |       6 |       2 | search    |
                                                  |       7 |       3 | search    |
                                                  |       8 |       3 | engine    |
                                                  |       9 |       3 | yahoo     |
                                                  +---------+---------+-----------+

FULLTEXT SEARCH

mysql> SELECT l.link_id
    ->      , l.url
    ->      , l.title
    ->      , t.term
    ->      , SUM(MATCH(t.term) AGAINST('search music' IN BOOLEAN MODE) + MATCH(url, title) AGAINST("yahoo")) as `rank`
    -> FROM links l
    ->      JOIN terms t ON l.link_id = t.link_id
    -> WHERE MATCH(t.term) AGAINST('search music' IN BOOLEAN MODE)
    ->       OR MATCH(url, title) AGAINST("yahoo")
    -> GROUP BY title
    -> ORDER BY `rank` DESC LIMIT 200;
+---------+-------------------------------+----------------------------------------------------+--------+--------------------+
| link_id | url                           | title                                              | term   | rank               |
+---------+-------------------------------+----------------------------------------------------+--------+--------------------+
|       3 | http://yahoo.com              | Yahoo                                              | search | 1.7925547659397125 |
|       1 | https://ektoplazm.com/explore | Ektoplazm - Free music portal and psytrance netlab | music  | 0.9105787873268127 |
|       2 | http://www.google.com         | Google                                             | search | 0.4266865849494934 |
+---------+-------------------------------+----------------------------------------------------+--------+--------------------+

 

[oz11]

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, unexpected '+' in /opt/lampp/htdocs/sites/codany/000001 (copy 1)/test/submit0r.php:544 Stack trace: #0 /opt/lampp/htdocs/sites/codany/000001 (copy 1)/test/submit0r.php(544): PDOStatement->execute(Array) #1 {main} thrown in /opt/lampp/htdocs/sites/codany/000001 (copy 1)/test/submit0r.php on line 544

Hey. Do you know why I get this error when i search for "c++"?  I'm thinking it could be a vulnerability maybe?

[Barand]

From the manual...

Quote

InnoDB full-text search does not support the use of multiple operators on a single search word, as in this example: '++apple'. Use of multiple operators on a single search word returns a syntax error to standard out. MyISAM full-text search successfully processes the same search, ignoring all operators except for the operator immediately adjacent to the search word.

InnoDB full-text search only supports leading plus or minus signs. For example, InnoDB supports '+apple' but does not support 'apple+'. Specifying a trailing plus or minus sign causes InnoDB to report a syntax error.

InnoDB full-text search does not support the use of a leading plus sign with wildcard ('+*'), a plus and minus sign combination ('+-'), or leading a plus and minus sign combination ('+-apple'). These invalid queries return a syntax error.

Also I am pretty sure that words of 3 chars or less are ignored.

[oz11]

Know a good work around?  Maybe error exceptions?.. Like if there is an error, is there a way of displaying a custom error message instead of this?

Edited January 21 by oz11

[Barand]

 

SELECT ...
WHERE term = 'c++'