Member Avatar for safi.najjar1

hello guys i am trying to do something wierd in my query

here is my query

SELECT * FROM products INNER JOIN smartdevices ON products.id = smartdevices.productId WHERE products.clearName LIKE :keyword1 OR products.clearName LIKE :keyword2 OR products.clearName LIKE :keyword3

when the user enter the search phrase

my php code will split the phrase to 3 equal parts (keyword1 , keyword2 , keyword3)

i want my result to include all smart devices that contains all of these keywords together in its name

but if one of these keywords was missspelled (let us say keyword1 for example)

then i want my result to include all smart devices that contains keyword2 and keyword3 together in its name

the query i posted above will not do that so please help

thanks in advance for any help you may provide :D

  • 2 Contributors
  • 4 Replies
  • 143 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by safi.najjar1

Recommended Answers

All 4 Replies

Member Avatar for Taywin

When you use like keyword and give a value, you must ensure that the value has wildcard in it or SQL would try to look for the exact given value. (i.e. ... LIKE 'bla' will match only string containing bla in the selected field, but not 'blah' or 'blabla'.) You may need to include % to both front and back of your keyword (i.e. ... LIKE '%bla%' will match 'bla', 'blabla', 'blah', etc).

If you want records that contain both keyword2 & 3, simply use operand AND instead of OR.

Edited by Taywin
Member Avatar for safi.najjar1

but if i used AND and the user misspelled i will get zero result

Member Avatar for Taywin

That's the user problem, not yours. There is always a chance that a user will type in wrong. If you want to implement the guessing functionality, then it may be helpful (but I don't see that it is worth the effort).

Unless you want to lessen the chance of typo, you may throw in wild card (% symbol) by taking up to a certain number of characters, and then replace the rest with the wild card. i.e. a user enters 'banana' but may type in 'banane'. You take the first 4 characters and produce 'bana%' as a keyword in your query search. This approach does NOT eliminate the error from users, but lessen the chance that you cannot find the data. However, it could increases the false negative results into your result.

Still, you need to weight in your effort and what you would gain from doing so...

Edited by Taywin
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.