Php mysql search - Search not working if with plural and singular

Question

If I search "sales order" it's fetching "sales order" and "sales orders" in results. It's fetching the result with "s" also.

But if I search "sales orders" it's fetching "sales orders" only but I want "sales order" will also fetch.

I am using php mysql query.

SELECT DISTINCT * FROM wp_posts as p 
    inner join wp_postmeta as pm on pm.post_id = p.ID 
    where (p.post_type = 'abc' or p.post_type = 'xyz') 
    and p.post_title LIKE '%sales order%' 
    or (pm.meta_key = 'xyzkeyword' and pm.meta_value LIKE '%sales order%') 
    GROUP by p.ID 
    ORDER BY p.id DESC

[hope this thread helps you](https://stackoverflow.com/questions/3088433/sql-plural-singular-searches) — Alive to die - Anant, Feb 15 '18 at 07:07
your query is right but your question is not sure what you want to achieve. if you are using like and if you search by 'sales order' with like you will get both — prakash tank, Feb 15 '18 at 07:17
read https://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html — helpdoc, Feb 15 '18 at 07:17

score 1 · Answer 1 · answered Feb 15 '18 at 07:04

1

Try with this without "%"

p.post_title LIKE 'sales order'

answered Feb 15 '18 at 07:04

Ravi Patel

641
1
10
18

then what is the purpose of like operator here? – prakash tank Feb 15 '18 at 07:14
@ravi patel - I don't want exact search. Search will be with like operator. – ANkit Joshi Feb 15 '18 at 07:37

score 0 · Answer 2 · answered Feb 15 '18 at 07:17

0

You can also try like this

and (p.post_title LIKE '%sales order%'  OR p.post_title LIKE '%sales orders%' )

I hope this will solve your issue.

answered Feb 15 '18 at 07:17

Sachin Raghav

452
5
14

score 0 · Answer 3 · answered Feb 15 '18 at 07:21

I am not much sure about this query but you can check as

SELECT DISTINCT * FROM wp_posts as p 
inner join wp_postmeta as pm on pm.post_id = p.ID 
where (p.post_type = 'abc' or p.post_type = 'xyz') 
and p.post_title LIKE '%sales%' AND  p.post_title LIKE '%order%'
or (pm.meta_key = 'xyzkeyword' and pm.meta_value LIKE '%sales order%') 
GROUP by p.ID 
ORDER BY p.id DESC

OR

SELECT DISTINCT * FROM wp_posts as p 
inner join wp_postmeta as pm on pm.post_id = p.ID 
where (p.post_type = 'abc' or p.post_type = 'xyz') 
and p.post_title LIKE '%sales%order%'
or (pm.meta_key = 'xyzkeyword' and pm.meta_value LIKE '%sales order%') 
GROUP by p.ID 
ORDER BY p.id DESC

score 0 · Answer 4 · answered Feb 15 '18 at 07:40

use MySQL Full text search,check if FULLTEXT indexes are there

SELECT DISTINCT * FROM wp_posts as p 
    inner join wp_postmeta as pm on pm.post_id = p.ID 
    where (p.post_type = 'abc' or p.post_type = 'xyz')
    match (p.post_title) against ('sales order')
    or (pm.meta_key = 'xyzkeyword' and pm.meta_value LIKE '%sales order%') 
    GROUP by p.ID 
    ORDER BY p.id DESC

Php mysql search - Search not working if with plural and singular

4 Answers4