Need Help with Reg_Exp in MySQL select statemnt??

bnownlater

How would I go about using regular expressions in my sql statement. I have a keyword search on my site that a user could enter multiple keywords. I split the keywords into an array and loop through the array and add it to my slq statement. Problem is I do not quite now how to search for these keywords in my database. I am trying to only match the beginnings of words within a string to the keywords supplied. (My fields are field1:text, feild2:varchar.)

For example: $keyword[0]=test....I have a string- "This is a testing mechanism.". So this record would match.

I tried using LIKE='$keyword[0]%', but does not work. I was referred to the MySQL Reg Exp but the "manual" does not explain well enough for a begginer. Thanks.

Here is my current sql statement (that is not returning any results):

SELECT * FROM db WHERE 
((PUB_NAME <> 'TIME' AND PUB_NAME<>'US News') 
OR 
(DATE >= '2002-7-12' AND (PUB_NAME='TIME' OR PUB_NAME='US News'))) 
AND 
((TITLE LIKE 'test%' AND TITLE LIKE 'prep%') OR (ABSTRACT LIKE 'test%' AND ABSTRACT LIKE 'prep%')) 
ORDER BY DATE DESC, TITLE LIMIT 0,5

Lars_Berg

SELECT * FROM db WHERE 
((PUB_NAME <> 'TIME' AND PUB_NAME<>'US News') 
OR 
(DATE >= '2002-7-12' AND (PUB_NAME='TIME' OR PUB_NAME='US News'))) 
AND 
((TITLE LIKE 'test%' or TITLE LIKE 'prep%') OR (ABSTRACT LIKE 'test%' or ABSTRACT LIKE 'prep%')) 
ORDER BY DATE DESC, TITLE LIMIT 0,5

A text can only begin in one way, hokay?

if you wish to search for records where title/abstract contains all words

SELECT * FROM db WHERE 
((PUB_NAME <> 'TIME' AND PUB_NAME<>'US News') 
OR 
(DATE >= '2002-7-12' AND (PUB_NAME='TIME' OR PUB_NAME='US News'))) 
AND 
((TITLE LIKE '%test%' and TITLE LIKE '%prep%') OR (ABSTRACT LIKE '%test%' and ABSTRACT LIKE '%prep%')) 
ORDER BY DATE DESC, TITLE LIMIT 0,5

This will be very slow, you better look at full text indexing.

bnownlater

A text can only begin in one way, hokay?

Not sure what you are saying??

That is the select statement I had before, but my boss complained that it brought up too much junk records. So now I need to search the rows for words that begin with the keywords that the user submitted.

tomhath

If you have 'prep%' the field must begin with 'prep', not any word in the field. Obviously your query won't return anything because the field cannot start with both 'prep' and 'test'.

This should be close (match the beginning of a word followed by the sequence of characters you want):

(TITLE REGEXP "[[:<:]]prep" and TITLE REGEXP "[[:<:]]test" )

bnownlater

tomhath,

Thanks. I believe this is what I was looking for. I am trying to get a grasp of reg exp, could you explain what the syntax [[:<:]] means? Thanks again.

tomhath

Kind of a funky looking trigraph isn't it? I'd call it a "word start boundary" which is usually the beginning of a line or whitespace, but could be other special characters according to the manual's definition:

http://www.mysql.com/documentation/mysql/bychapter/manual_Regexp.html

[[:<:]]
[[:>:]]
These match the null string at the beginning and end of a word respectively. A word is defined as a sequence of word characters which is neither preceded nor followed by word characters. A word character is an alnum character (as defined by ctype(3)) or an underscore (_).