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

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.

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

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...

Be a part of the DaniWeb community

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