0

i have table

 CREATE TABLE IF NOT EXISTS `posts` (
      `post_id` int(11) NOT NULL,
      `post_slug` text CHARACTER SET utf8 NOT NULL,
      `title` longtext CHARACTER SET utf8 NOT NULL,
      `created_at` date NOT NULL,
      `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

i am implementing search on this table.say example i have a number of posts in which i am searching Keyword "Art" when i searched for "art" it's working fine .i am getting all art having art in their title but when i search for "Arts" i am not getting any records as there is a no posts that contains "arts" in their title.the keyword is not fixed.it's user keyword that user will search for what i have tried is below query :

SELECT * 
FROM `posts` 
WHERE title LIKE "%art%" 
ORDER BY `post_id` ASC
kiran gadhvi
  • 228
  • 2
  • 16
  • So what is your question? You can use an underscore `_` to [match](http://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html) a single character or you could use a [regex](http://dev.mysql.com/doc/refman/5.7/en/regexp.html) and narrow down the possible following character to a single `s`. Prefixing with `%` does not help you, I think (because you also find "parts", for exmaple). – syck Jun 17 '16 at 11:55
  • SELECT * FROM `posts` WHERE title LIKE "art%" ORDER BY `post_id` ASC – Pardeep Pathania Jun 17 '16 at 11:57
  • @PardeepPathania MySQL pattern matching is case insensitive. – syck Jun 17 '16 at 11:59
  • @PardeepPathania your query will only return posts starting with "art". – kiran gadhvi Jun 17 '16 at 12:02
  • @kirangadhvi Please try to accept/upvote answers (if any) that were useful to you, or ask for more help if your question is not resolved. – samayo Jun 25 '16 at 08:09

3 Answers3

1

You can switch to full text with boolean operators.

Change your query to :

SELECT * 
FROM `posts` 
WHERE('art*' IN BOOLEAN MODE)
ORDER BY `post_id` ASC
User2403
  • 317
  • 1
  • 5
0

This problem may not be as simple as it seems. For 'arts', it is indeed pretty easy to get the singular 'art', however for other words (e.g. 'query' with plural 'queries') more logic will be required.

One possible approach would be to store the word stems of the words in the title in addition to the actual title. You can then determine the word stems of the words in the search query only search for those stems.

This has the additional benefit that you will not only be able to find words independent of their singular/plural forms, but e.g. also verbs in different tenses ('worked' vs. 'works').

You could for example have a look at this PHP word stemming library.

TimoStaudinger
  • 41,396
  • 16
  • 88
  • 94
  • thanks for your answer but is there any other way except that? – kiran gadhvi Jun 17 '16 at 12:06
  • The other answers already proposed much simpler approches to this problem. Which one you should use ultimately depends on you exact requirements. In the end, for problems like this, there are always multiple good solutions, depending on how much flexibility and features you need. – TimoStaudinger Jun 17 '16 at 12:10
0

I am a MySQL newbie-like guy, but I assume something like this should work. 

SELECT * FROM `posts` WHERE title REGEXP 'art(s?)'
samayo
  • 16,163
  • 12
  • 91
  • 106
  • thanks for your answer buddy. can you please write query using table i provided.how it works in my case. – kiran gadhvi Jun 17 '16 at 12:11
  • @kirangadhvi Can you check my edit? If it does not work as you intended, then please mention a couple of texts which you want the query to match .. or not! – samayo Jun 17 '16 at 12:25
  • thanks for your reply but in that case i only get post who has "arts" string in it's title.i won't get post which has string "art" in their title. – kiran gadhvi Jun 17 '16 at 12:29
  • @kirangadhvi Ok, I didn't understand at first. `art` isn't the first word for your titles. If you have the words `art` and `arts` anywhere in the title, my edit should work. try it. – samayo Jun 17 '16 at 12:38