MySQL PHP match words in query with database column

Question

I have the following structure of a table [id - title - tag - ..] I want to achieve the following:

If there is a record in table with title "I love my job and it is my hobby" If a query is submitted having two words from the sentence then this sentence should be selected. E.g. query "love hobby". It should give me the above title and not for example "I love my job". At least the sentence with more words matching the query keywords first then the less ones later.

how can I do this search on the title column of my table?

I apologize if explanation not clear...more than happy to help clarify.

Thank you all

Raptor · Accepted Answer · 2012-07-31 07:05:28Z

2

Try this :

SELECT title FROM your_table WHERE title LIKE '%love%' AND title LIKE '%hobby%'

answered Jul 31, 2012 at 7:05

Raptor

54.4k47 gold badges254 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sys_debug Over a year ago

well the keywords I gave are an example...so am assuming I will replace them with $keyword1 and $keyword2....but will this achieve what I really need i.e. identifying the one with both keywords present before the title with one keyword present?

Raptor Over a year ago

only values in title with both love and hobby will be shown.

sys_debug Over a year ago

aha and what about if i wish to include the titles with one of them only? I want to display them after the ones matching both keywords. btw what if I don't know the number of keywords inserted?

sys_debug Over a year ago

sorry should clarify that search query is all in one keyword...so should I separate them first?

complex857 · Accepted Answer · 2012-07-31 07:10:00Z

1

Look into mysql's built in full text search capabilities. In boolean mode, you could transform your query to +love +hobby and have results returned without full table scans. Be aware that this only works with myisam tables, might want to move the indexed data out of the main tables since myisam doesn't support things like foreign keys or transactions.

For more advanced free text indexing you could try sphinx (have mysql look-and-feel interface too) or solr.

answered Jul 31, 2012 at 7:10

complex857

20.9k6 gold badges54 silver badges56 bronze badges

Comments

Stegrex · Accepted Answer · 2012-07-31 07:10:42Z

1

If you're using MyISAM or innoDB, you can use the MySQL fulltext search:

SELECT * FROM table_name WHERE MATCH (title) AGAINST ('love hobby' IN BOOLEAN MODE);

It'll also search for individual words as well.

Read this: https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

answered Jul 31, 2012 at 7:10

Stegrex

4,0441 gold badge19 silver badges19 bronze badges

Comments

Omesh · Accepted Answer · 2012-07-31 07:16:19Z

0

You can also use MySQL REGEXP

SELECT title FROM table WHERE title REGEXP ' love .+ hobby';

If you have it as a single string then try:

SELECT title FROM table WHERE title REGEXP REPLACE('love hobby', ' ', '.+ ');

edited Jul 31, 2012 at 7:16

answered Jul 31, 2012 at 7:09

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

MySQL PHP match words in query with database column

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related