searching matches within a field?

[commun1]

I'm using PHP to execute MySQL commands.

I have a MySQL table that consists of two fields:

id, product_title

each product has its own id and rows look like this:

1, "this is product widget number one"
2, "this is product widget number two"
3, "this is product widget number three"
4, "this is product widget number four"


I have HTTP_POST_DATA which look for matches within "product_title", so my query is:

SELECT * FROM table WHERE product_title LIKE '%$searchstring%'

this works for simple queries, i.e. if types in only "widget" it gives me all 4 rows with with "widget" in it but if someone is looking for "widget four" it doesn't give any results since the word "number" is between "widget" and "four".

how can I achieve it to get results with such queries?

 

[r937]

use php to replace embedded spaces with percent signs, so that the query you generate looks like this --

WHERE product_title LIKE '%widget%four%'

alternatively, split the search string on the spaces, and generate the query to look like this --

WHERE product_title LIKE '%widget%'
AND product_title LIKE '%four%'

which would allow you to find titles like "this is the fourth widget"



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)