CODEWITHSUNDEEP
phpmysql
sys_debug
sys_debug

Reputation: 4003

MySQL PHP match words in query with database column

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

Upvotes: 0

Views: 2427

Answers (4)

Omesh
Omesh

Reputation: 29081

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', ' ', '.+ ');

Upvotes: 0

Stegrex
Stegrex

Reputation: 4024

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

Upvotes: 1

complex857
complex857

Reputation: 20753

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.

Upvotes: 1

Raptor
Raptor

Reputation: 54212

Try this :

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

Upvotes: 2

Related Questions