WARNING: this project has now moved to https://src.atomjump.com/atomjump/db_searcher.git
Searches your database when you enter 'details [query]' into an AtomJump Messaging window
Note: this release should still be considered an Alpha.
Note: you should be using MySQL ver >= 5.6, as FULLTEXT indexes are not supported on innodb tables. See the notes below for an alternative.
You should set the config.json 'innodb' field to true.
Create the index first:
USE ssshout;
CREATE FULLTEXT INDEX searcher ON tbl_ssshout(var_shouted);
CREATE INDEX ispublic ON tbl_ssshout(int_whisper_to_id);
Then include the following SQL queries to provide a free text search in your config.json 'sqlQueries' array:
"sqlQueries": [
"DROP TABLE IF EXISTS best_options",
"CREATE TEMPORARY TABLE best_options SELECT var_shouted, int_whisper_to_id FROM tbl_ssshout WHERE MATCH(var_shouted) AGAINST('[SEARCH]' IN BOOLEAN MODE) LIMIT 200",
"SELECT var_shouted FROM best_options WHERE int_whisper_to_id IS NULL LIMIT 10"
]
Or, if you want to include other fields, such as a time-stamp in the result, you can use
"sqlQueries": [
"DROP TABLE IF EXISTS best_options",
"CREATE TEMPORARY TABLE best_options SELECT var_shouted, int_whisper_to_id, date_when_shouted FROM tbl_ssshout WHERE MATCH(var_shouted) AGAINST('[SEARCH]' IN BOOLEAN MODE) LIMIT 200",
"SELECT CONCAT(var_shouted, ' [', date_when_shouted , ']') FROM best_options WHERE int_whisper_to_id IS NULL LIMIT 10"
]
For small sites, you can potentially use a MYISAM table, rather than an innodb table before creating the index. We cannot guarantee that will work in multiple server sites, however.
ALTER TABLE tbl_ssshout ENGINE = MYISAM;
E.g. in your config.json 'sqlQueries' array:
SELECT CONCAT(var_name, ' ', var_description) AS result FROM tbl_bike WHERE var_name LIKE '%[SEARCH]%' OR var_description LIKE '%[SEARCH]%' LIMIT 10
Create the index first:
CREATE FULLTEXT INDEX searcher ON tbl_bike(var_name, var_description);
Then in your config.json 'sqlQueries' array:
SELECT var_name FROM tbl_bike WHERE MATCH(var_name, var_description) AGAINST('[SEARCH]' IN BOOLEAN MODE)
Or in this example, a filter at the end, and two fields concatenated:
SELECT CONCAT(COALESCE(var_name,''), COALESCE(var_description,'')) FROM tbl_bike WHERE MATCH(var_name, var_description) AGAINST('[SEARCH]' IN BOOLEAN MODE) AND enm_active = 'active'
Note: the COALESCE prevents NULL values from returning the whole record as NULL.
You will need to install the correct ODBC driver for your database, and create a DSN (whether this is on Windows or Unix).
Then enter the DSN name in the config.json 'dsnHere' field.