SQL Again!

[andychev]

Small problem which is probably quite easy if you know how but after spending hours search for an answer i am still struggling.

The problem i have is trying to search an sql table for an exact term that is entered into a search box. I have everything working but in using the LIKE command with wildcards if you search for something like 'red' it will also return any words that contain that term so it would return 'colouRED'

The searchy bit is just basically

"products_name like '%$search_keywords[$i]%' ";

If it was sql 2000 i belive i could just use CONTAINS instead of LIKE and it would all be sorted. What would the equivilent of this be on Linux?

[jmarcv]

Well like is working properly. nIf you want an exact match use
"products_name = '$search_keywords[$i]' ";

Now you mention sort order? Thats another issue. What are we missing?

[andychev]

Sorry,

The field in the table contains a whole load of text eg.

'Coloured bits and bobs for use in the home'

so searching for 'red' would bring up that product using the Like with the % wildcards. However knock out the % and it wouldnt return that product unless the entire 'coloured bits and bobs for use in the home' was entered as the search term.

What im after is for it to search for complete words so searching for 'red' wouldnt return this product but searching for coloured would

[Dmitriy]

andychev, you didn't metioned DB name you're using: MySQL or PostgreSQL. I presume it is MySQL.

To search exact term in the varchar or text field you can use LIKE. If from the records like:
- this is red
- this is_red
- this is reddddd
... you want only the first, you should use query like:

SELECT * FROM tbl_name WHERE text_field LIKE '% red %';

Note the gaps before & after 'red'. I sure that search terms will not be always enclosed by gaps, so we should modify our query:

SELECT * FROM tbl_name WHERE text_field LIKE '% red %' OR text_field LIKE '%red %' OR text_field LIKE '% red%' ORDER BY field_name;

Notice, I've added other alternatives + ORDER statement. The only minusofthis query is that if the fieldwill contain only 'red' - it will not be selected.
If you want to order selected items by the number of searched terms (relevancy) you can use MATCH AGAINS syntax.
Let me know if you need futher explanations.

[andychev]

Of course!

I should have thought about putting spaces in

Thanks Dmitriy you were nearly spot on with what you came up with:

SELECT * FROM tbl_name WHERE text_field LIKE '% red %' OR text_field LIKE '%red %' OR text_field LIKE '% red%' ORDER BY field_name;

the only problem came with %red % as this would pick up words ending in 'red' and % red% came up with words begining in red such as redress. However putting spaces in was the clue i was after. The final solution was.

SELECT * FROM tbl_name WHERE text_field LIKE '% red %' OR text_field LIKE 'red %' OR text_field LIKE '% red' ORDER BY field_name;

The only problem with this is punctuation so if somewhere in the description it has "this product is red." then this wouldnt be found. However i can just expand the code with another OR '% red.' OR
'% red. %'

All in all a huge improvement on the old search algorithm which was frankly broken! So thank you

[andychev]

Originally Posted by jmarcv

Well like is working properly. nIf you want an exact match use
"products_name = '$search_keywords[$i]' ";

Now you mention sort order? Thats another issue. What are we missing?

Sorry, not literally sorted but the problem would be 'sorted' (solved)

[jmarcv]

Note that like '% red' means any fields containing red at the VERY END of the field with a space in front.
Try this:

PHP Code:

...where products_name REGEXP '[^a-zA-Z]red[^a-zA-Z]' 


Using regular expressions, we can tell it to pick every instance of red that does not have a character before or after it. I think that will cover what you want without a string of comparisons.

Regular expressions are real powerful, but also very difficult to learn.

[andychev]

...where products_name REGEXP '[^a-zA-Z]red[^a-zA-Z]'

That seems to work the same as '% red %' it doesnt return the record if red apears right at the start or right at the end of the field.

I could add
LIKE %red %' OR text_field LIKE '% red%'

on the end to also return the record if it is at the start or end of the field. but then am i back to the same problem with puntuation . Ie if the field ends in 'red.' the record wouldnt be returned.

Is REGEXP more efficient than using LIKE?

[jmarcv]

Let me look closer.
REGEXP is probably more resource intensive than LIKE for sure, but I'll bet it is less intensive than 2-3 LIKES.

I Managed a server that did a lot of REGEXP on datasets of up to 10,000 records withing a query set that had to be exanined, with some 200-800 queries per second, so unless your even within 20% of that even on a sared server, I don't think it will be noticable.

I'll get back on this

[jmarcv]

Well, that stinks. It SHOULD work, but.....

Heres a workaround:

 ...where concat(' ',products_name,' ') REGEXP '[^a-zA-Z]+red[^a-zA-Z]+';