4

I'm trying to search for a word in a string in mysql database using PHP.

the issue that I am having is that it searches and spits out the result on my PHP page but any word that is close to the searched word is also gets a result on my page.

to explain this better, lets say I have a string which is like this:

women's clothing

now if i search for a word which is women the results are being displayed on my page correctly But If i search for the word men the same results are being displayed on my page because the word women has the word men in it.

this is my current sql query:

SELECT id, category_name, image_url FROM data WHERE `category_name` LIKE '%$sub%' GROUP BY category_name

I tried to change '%$sub%' to '$sub' but that doesn't return any results at all!

could someone please advise on this?

Edit:

the strings in the category_name column vary and they cannot be changed since they are coming from an external API.

these strings could vary. so one might look like Women's Dresses & Skirts and another one might look like Women's shoes and another one might look Women's bags and another one might look like men's shoes etc etc....

so what i need to do is to display all the results for the keyword women and display the results for all the men. I hope that makes sense.

but at the moment, the results of men and women being displayed at the same time because the word Women has the word Men in it as I explained above.

Improve this question
18
  • 2
    Well how about using LIKE '% $sub %'? This won't match men's but I hope you get the idea Commented Nov 11, 2015 at 12:03
  • @mrun, you mean just putting spaces before and after the $sub ? Commented Nov 11, 2015 at 12:04
  • 1
    @H basically yes but you'll have to work out cases such as men followed by !, ., etc. Commented Nov 11, 2015 at 12:06
  • 1
    @swidmann I'm confused as well. I think we need more insight on the situation of OP since I have a feeling the way he's doing it is wrong. Commented Nov 11, 2015 at 12:24
  • 1
    @swidmann, ok thanks. done that now. Commented Nov 11, 2015 at 12:49

4 Answers 4

Reset to default
2

You can do this with MySQL's REGEXP operator, using word match.

SELECT ... WHERE column REGEXP '[[:<:]]$sub[[:>:]]' ...
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

okay guys, thanks for all your inputs. this is how I did it and its working now:

SELECT id, category_name, aw_image_url FROM data WHERE category_name RLIKE '[[:<:]]".$sub."[[:>:]]' GROUP BY category_name
Improve this answer

Comments

1

The thing with what you want, is that you want men's clothing to show up but nothing that has something like women. The only difference between these 2 results is that woMEN has characters in front of it and MEN's clothing has characters after.

Mrun his idea is if you want to search for the exact word followed by nothing. So when you use: LIKE '% $sub %' only results that have the word MEN followed by nothing and nothing in front of the word MEN directly. There has to be a space in between.

Now for your result to work, you can use: LIKE $sub% (without the space at the end), however this does mean the word MEN can be followed by anthing. So for example the word MENACE would show up. If you want specific characters only allowed like @mrun suggested, you can add anything between $sub and the % sign. You'd have to escape it though.

EDIT:

Now that I think of it, you'd probably want MENS to be allowed as well but that means you'll have to choose your own conditions cause there's no way for MYSQL to automatically detect that the plural form of a word is allowed as well etc.

Improve this answer

6 Comments

FWIW, I think this is worse than my suggestion since (as you've acknowledged) this matches anything beginning with the search word. I think the men/women case was just a quick example and that the matching shouldn't be limited to word starts, but whole words.
@Antti29 You do realize that part was not my definite answer right? the LIKE % $sub% is not my definite answer but more an explanation. Everything that comes after that is my complete answer.
I do indeed. I'm afraid LIKE % $sub% would not cut it, since it wouldn't match category names such as men's whatnot when searching with men.
@Antti29 just for the record, LIKE % $sub% would give back anything that starts with MEN and everything after that doesnt matter. OP's conditions are too much to actually use 1 LIKE statement I guess. I'm just explaining to him how to work with the %% which you'd need in the LIKE statement when you want to do stuff like this.
You might want to try that again. LIKE "% men%" will return lines whose columns have the substring " men" (that is "men" after a single space) in them. I will not, however, return lines with the column starting with "men".
|
1

There are several options for this. Here's one of them:

SELECT id, category_name, image_url FROM data WHERE `category_name` LIKE '% $sub %' OR `category_name` LIKE '% $sub' OR `category_name` LIKE '$sub' OR `category_name` LIKE '$sub %' OR `category_name` LIKE '% $sub\' %' OR `category_name` LIKE '% $sub' OR `category_name` LIKE '$sub\'' OR `category_name` LIKE '$sub\' %' GROUP BY category_name

Another way to do this is to use the MATCH AGAINST, but you will need to use the FULLTEXT index, here's how:

SELECT id, category_name, image_url FROM data WHERE MATCH (`category_name`) AGAINST ('+$sub' IN BOOLEAN MODE) GROUP BY category_name
Improve this answer

2 Comments

Tried your first suggestion and nothing gets returned from mysql and the second suggestion is out of question as well as I cannot use FULTEXT index as my server wont allow it.
Can you update your answer with a sample of your content?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.