I want to make a fulltext search with metaphone. Everythings works fine. I have 4 fields ie.
ID |Category | Type |Title |Meta
1 |Vehicle |4 Wheelers |Farrari Car for Sale |FHKL WLRS FRR KR FR SL
2 |Real Estate |Residential Apt|3BHK for sale |RL ESTT RSTN APT BK FR SL
3 |Music |Instruments |Piano for sale |MSK INST PN FR SL
4 |Stationary |College |Bag for $50 |STXN KLJ BK FR
5 |Services |Job |Vacancy for Jr.Web Developer |SRFS JB FKNS FR JRWB TFLP
The above is the sample data. Here I want to use metaphone and fulltext search using match()against().
Everything works fine. However Some words like Bag, Job and Car are ignored as the default minimum character 4. The issue is now that I use shared hosting and the hosting provider has told me that he cannot provide me a mysql config file nor can they change this so doing this in config file
ft_min_word_len = 2
is not an option.
//Code for generating metaphone
<?php
$string = "Vacancy for Jr.Web Developer";
$a = explode(" ", $string);
foreach ($a as $value) {
echo metaphone($value,4)."<br>";
}
?>
I am using normal
SELECT * FROM tbl_sc WHERE MATCH(META) AGAINST('$USER_SEARCH');
All the information in the database are user generated so I cannot supervise. Since I use mysql, PHP and on a shared hosting. I cannot use any elastic search library or solr like things. I have searched google and stack overflow however I am not able to get anything
One options is using LIKE operator but I want to use MATCH() AGAINST() if possible.
Kindly help me out with some work around or alternate route.
first there are three types of fulltext searches
Natural language full text search
Boolean fulltext searches
Query expansion searches
what suits your question here is the natural language full-text search, since your queries are mostly in free language and uses no special characters.the syntax goes like this
in your case first, add the fulltext functionality to your table
your query should be something like this
to alter the
ft_min_word_len
you have to go the the my.cnf file, change it to the desired value, restart the server and rebuild your indexes like sothen
but since you are in shared hosting account, you cannot access the
my.cnf
file. However, you usingSHOW VARIABLES
andINFORMATION SCHEMA
you can see all set variables and even change them usingSET
in your session such that all db connections will be based on the newly set valuesfor instance to
SHOW VARIABLES
in sql you can useSELECT * FROM information_schema.global_variables;
this shows all existing variables in your current session, for a variable likeflush time
it can be set to1
usingSET flush_time = 1;
so now the database will have a flushtime of 1 onwards, in your case i suppose the variablesft_max_word_len
andft_min_word_len
are dynamically changeable and i would therefore suggest tryingSET ft_min_word_len = 2;
within your current session, for more information see server system variables