0

Hello everyone as the topic says I am looking for alternative or advanced using of "LIKE".

I have column which contains a row of words p.e. "keyword1,keyword2,another_keyword" and when I use

$sql = mysql_query("SELECT * FROM table WHERE `column` LIKE '%keyword1%' ");

It hardly find it p.e. this example works but when i try to find shorter strings it has problems and sometimes it does not find anything.

I tried put a whitespace after comas and it helped but if there is a way where I can search for match with this specification of column I would be happy.

user3175393
  • 109
  • 1
  • 10
  • First of all why did you create such kind of schema. Thats very bad. And now if you want advanced search then try to use SOUNDEX functiuon. Search it on google and try to apply it. – Ankit Bajpai Sep 29 '14 at 07:08
  • 1
    Modify database structure, so that every `keyword#` would go as separate row in table with FK to `table` table (For e.g. `keywords_table`.`column_id`) – Justinas Sep 29 '14 at 07:08
  • If you have to search a comma-separated value column rather than a properly normalised database, then Use MySQL's [FIND_IN_SET()](http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set) – Mark Baker Sep 29 '14 at 07:10
  • it is not so easy as it looks like i can not change the way it is created i can only modify it after it is created i think ill just try to separate it to LIKE friendly schema – user3175393 Sep 29 '14 at 07:14
  • If you can't change it, then FIND_IN_SET() was written specifically to work with comma-separated lists.... it's not as performant as a properly normalised table structure, but it's faster than LIKE – Mark Baker Sep 29 '14 at 07:19

4 Answers4

0

You may move keywords into individual table.

Or you can use SET field type, if the list of your keywords don't change.

Aleksandr Mochalygin
  • 18
  • 3
0

Storing comma separated list of your words is a bad idea example using like in your scenario is hard to find the exact work in comma separated list instead you can add new table which relates to your current table and store each the word in a new row with the associated identity like 

table1
id  title
1    test1
2    test2

kewords_table

table1_id word
1         word1
1         word2
1         word3

and query will be

select t.*
from table1 t
join kewords_table k
  on(t.id = k.table1_id)
  where k.word = 'your_keyword'

If you can't alter your structure you can use find_in_set()

SELECT * FROM table WHERE find_in_set('your_keyword',`column`) > 0
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0

try something like this:

SELECT * FROM tablename
WHERE column LIKE '%keyword1%'
OR    column LIKE '%keyword2%';

for more info see here:Using SQL LIKE and IN together

Community
  • 1
  • 1
Suchit kumar
  • 11,809
  • 3
  • 22
  • 44
0

MySQL allows you to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators. This is why the full-text search in Boolean mode is suitable for experienced users.

First You have to add FULLTEXT index to that perticuler column :

ALTER TABLE table_name ADD search_column TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, ADD FULLTEXT search_column (search_column);

Run following query for searching :

SELECT * FROM table WHERE MATCH(search_column) AGAINST("keyword1")

for more info see here : https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

Mox
  • 61
  • 3