How to Implement a Keyword Search in MySQL

How to implement a Keyword Search in MySQL?

For a single keyword on VARCHAR fields you can use LIKE:

SELECT id, category, location
FROM table
WHERE
(
    category LIKE '%keyword%'
    OR location LIKE '%keyword%'
)

For a description you're usually better adding a full text index and doing a Full-Text Search (MyISAM only):

SELECT id, description
FROM table
WHERE MATCH (description) AGAINST('keyword1 keyword2')

How to implement a keyword Search for specific words in MySQL?

This is a bit tricky. But for your examples, the following will work:

select *
from BIO
where concat(' ', replace(info, '-', ' '), ' ') LIKE '% LTR %';

What is the logic? It places a space at the beginning and end of info and replaces all hyphens with spaces. For the examples in your question, the occurrence of LTR that you want would then be " LTR ".

Note: You could also work on a regular expression to do the same thing. But it is tricky with the word boundaries and the matches at the beginning and end of the string.

mySql - search query based on keywords

I ended up using this structure and query here:

$q = $_REQUEST['search'];
$q_comma = array_filter(explode(' ', str_replace(',', ' ', $q)));
$count = count($q_comma);
$dbQuery = "select id, link, description, tags from db where ";
$searchTerms = '';

foreach ($q_comma as $q) {
    $counter++;
    if ($counter == 1) {
        $searchTerms .= "tags LIKE '%$q%'";
    } else {
        $searchTerms .= "and tags LIKE '%$q%'";
    }
}

$sql_res = $dbQuery . $searchTerms;
$result = mysqli_query($db, $sql_res) or die(mysqli_error($db));
$return_arr = array();
$data = array();
if (mysqli_num_rows($result) == 0) {
    echo "Nothing";
} else {
    while ($row = mysqli_fetch_object($result)) {
        $data[] = $row;
    }
    mysqli_free_result($result);
    echo json_encode($data);
}

How to implement keyword search?

SELECT   Items_tbl.*
FROM     Items_tbl
    JOIN Cross_tbl   USING (item_idx)
    JOIN Keyword_tbl USING (kw_idx)
WHERE    kw_description IN ('aquamarine','blue','green')
GROUP BY item_idx
HAVING   COUNT(*) = 3

This assumes that keywords can only be associated with items at most one time (i.e. a UNIQUE constraint on (item_idx, kw_idx) in Cross_tbl); if that is not the case, you will have to replace COUNT(*) with the less efficient COUNT(DISTINCT kw_idx), or the even less efficient COUNT(DISTINCT kw_description) if the same keyword can appear multiple times in Keyword_tbl (i.e. no UNIQUE constraint on the kw_description column).

php How to search for keywords in mysql

Assuming the field name in your form is 'keywords', I think this is what you wanted to do more or less.

if (isset($_POST['keywords']) && $_POST['keywords']) {

    //explode with space
    $keywordList = explode(' ', $_POST['keywords']);

    //create OR clause for each keyword submitted
    $keywordClauseArr = array();
    foreach ($keywordList as $keyword) {
        $keywordClauseArr[] = "keywords LIKE '%$keyword%'";
    }

    //implode with OR
    $keywordClause = implode(' OR ', $keywordClauseArr);

    //finally create your sql string
    $sql = 'SELECT * FROM photos WHERE ' . $keywordClause . ' ORDER BY downloads DESC';

    //and then do your mysql query and other stuffs..

}

This will fetch the results with the keywords Merry or Christmas ordered by maximum downloads. Thanks.

Searching for Phrase Keywords in MySQL

If we suppose that you have your column in a list as a pythonic way for such tasks you can use set.intersection to get the intersection between two set (the second element could be another iterables like list or tuple) :

>>> col={'Car','Car sales','Cars','Sports cars','Sports foo','Car bar','Statistics'}
>>> col={i.lower() for i in col}
>>> s="Find sports car sales statistics in Manhattan."
>>> col.intersection(s.strip('.').split())
set(['car', 'statistics'])

And in your case you can put the result of your query within a set or convert it to set.

Note : the following set comprehension will convert the elements if your column to lower case :

>>> col={i.lower() for i in col}

But this recipe will find the intersection between your column and the splitted string with white spaces. so the result will be :

set(['car', 'statistics'])

As another way you can use re.search :

>>> col={'Car','Car sales','Cars','Sports cars','Sports foo','Car bar','Statistics'} 
>>> s='Find sports car sales statistics in Manhattan.'
>>> for i in col:
...    g=re.search('{}'.format(i),s,re.IGNORECASE)
...    if g:
...      print g.group(0)
... 
statistics
car sales
car

As a simple way you can use a function like following to get a combinations of your phrases :

from itertools import permutations
def combs(phrase):
    sp=phrase.split()
    com1=[map(lambda x:' '.join(x),li) for li in [permutations(sp,j) for j in range(1,len(sp)+1)]]
    for i,k in enumerate(sp):
          if not k.endswith('s'):
             sp[i]=k+'s'
    com2=[map(lambda x:' '.join(x),li) for li in [permutations(sp,j) for j in range(1,len(sp)+1)]]
    return com1+com2

print {j for i in combs('Car sales') for j in i}
set(['Car', 'sales', 'sales Cars', 'Car sales', 'Cars sales', 'sales Car', 'Cars'])

Note that this function could be more efficient and complete.

Recommended method to Keyword Search in whole MySQL DB via JavaWeb

In the query building section

SELECT CONCAT('SELECT * FROM ',table_schema,'.',table_name,
            ' WHERE ',column_name,' LIKE ','searchString',';')
      FROM information_schema.columns
      WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
      AND (column_type LIKE 'char(%'
      OR column_type LIKE 'varchar(%'
      OR column_type LIKE '%text')

I have updated line

WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')

to

 WHERE table_schema IN ('<my_table_name>')

to generate all the search results in my_table_name and saved to ArrayList and executed them

ArrayList<String> queries =   new ArrayList<String>();
DBConInfoSchema db =   new DBConInfoSchema();
ResultSet rs    =   db.getData(sql_statement);
while(rs.next()){
  queries.add(rs.getString(1));
}

from there I sorted out the required data.

-Hope this may help some one.

NB: Even though it worked for me I still believe its Naive
Correct me If there's a better way yo achieve this

Mysql: Search database based on 'keyword' position

You can use the following solution using LOCATE on ORDER BY too:

SELECT *
FROM tags
WHERE `tag_name` LIKE '%test%'
ORDER BY CAST(`tag_name` LIKE 'test' AS UNSIGNED) DESC,
  CAST(`tag_name` LIKE '%test%' AS UNSIGNED) ASC,
  LOCATE('test', `tag_name`) ASC

You can debug the above query using this query. There you can see the ORDER BY values:

SELECT *,
  CAST(`tag_name` LIKE 'test' AS UNSIGNED) AS KeywordOnly,
  CAST(`tag_name` LIKE '%test%' AS UNSIGNED) AS KeywordExists,
  LOCATE('test', `tag_name`) AS KeywordPosition
FROM tags
WHERE `tag_name` LIKE '%test%'
ORDER BY CAST(`tag_name` LIKE 'test' AS UNSIGNED) DESC,
  CAST(`tag_name` LIKE '%test%' AS UNSIGNED) ASC,
  LOCATE('test', `tag_name`) ASC

demo on dbfiddle.uk

---

Related Topics