Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I current have two different tables.

The first table has a list of titles and IDs associated to these titles, the second table is a list of random heading.

What I would like to know is if there is a way to match up all the titles in table2 to the closest matching title in table1 is this possible?

Ive tried :

SELECT title_table .*,random_titles.*, MATCH (title_table.title)AGAINST (random_titles.title) AS relevance FROM title_table ORDER BY relevance DESC

But that did not work.

I know I could use this query as each title is being put in table2 with PHP but I already have a lot of titles in the database.

Any help would be amazing thanks

share|improve this question
add comment

2 Answers 2

AGAINST() only accepts a string literal:

AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

-- http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

This rules out any construct I can think of where an ordinary query could be used to join tables based on a fulltext index match.

...however...

You can wrap the fulltext query in a stored function:

DELIMITER $$

DROP FUNCTION IF EXISTS `best_fulltext_match` $$
CREATE FUNCTION `best_fulltext_match` (search_string TEXT) RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN

  RETURN (SELECT id FROM title_table WHERE MATCH(title) AGAINST(search_string)
           ORDER BY MATCH(title) AGAINST (search_string) DESC LIMIT 1);

END $$

DELIMITER ;

Now...

SELECT best_fulltext_match('your title here');

...returns the id of the best match according to the fulltext index in title_table, and this function will accept a variable, no problem.

You could then use this to update your random_title_table.

UPDATE random_title
   SET title_id = best_fulltext_match(title);

The function is invoked once per row in random_title, and gives the fulltext search a static value to search with.

You could also use that same function when creating new entries in random_title.

share|improve this answer
add comment

The "the closest matching" constraint make me think to the MySQL function SOUNDEX.

I never used this feature but maybe it's well designed for you :)

Max.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.