Positive Look Ahead not working as thought

[fskreuz]

i have a regular expression that's supposed to match any string containing certain words in no particular order.

the pattern is in PHP:

var $pattern = /^(?=.*\bword1\b)(?=.*\bword2\b)(?=.*\bword3\b).*$/gmi

this pattern works well both in javascript and php but in SQL, it does not work. I searched and found out that some of the patterns are not alike in SQL so i changed it and tried this pattern on the phpmyadmin to search for a specific title of a book containing some key words in the database like this:

SELECT * FROM userbooks WHERE book_title REGEXP '^(\\?=.*[[:<:]]word1[[:>:]])(\\?=.*[[:<:]]word2[[:>:]])(\\?=.*[[:<:]]word3[[:>:]]).*$'

but it doesnt return anything. i tried it back on php and javascript to match the title in a variable using the above code in PHP/JS and it worked fine but doesnt return anything in the database. i already tried using it via php to access the database but to no avail. i think it has something to do with the LOOK AHEAD ?= coz it works the same on PHP/JS/SQL without it. also tried doing two slashes, three and even four before the ?=

i also tried retrieving a book year which was 3333 using this code and it did not work either:

SELECT * FROM userbooks WHERE book_year REGEXP '33(\\?=33)'

is there any way to make this work or an alternative? or am i doing it wrong

[tyson.crouch]

have you had a look at maybe using the LIKE statement after your WHERE clause? It may be a good option to search for words/strings in a blob text field

[fskreuz]

Also tried that, replacing the ".*" with % also. i also tried removing the word boundaries "[[:<:]]" and "[[:>:]]" because i thought there were problems with that both in the LIKE and REGEXP but both return the same result, none.

[drjohn]

SELECT * FROM userbooks WHERE book_title LIKE '%yourWord%' or book_title LIKE '%otherWord%';

alternatively if both words must be present.

SELECT * FROM userbooks WHERE book_title LIKE '%yourWord%' AND book_title LIKE '%otherWord%';

is the correct way to do this.

Similarly

SELECT * FROM userbooks WHERE book_year LIKE '%33%';

[fskreuz]

i already thought of that but the problem is im going to find these words over multiple columns using OR. so technically its like "find word1 and word2 in column1 or find word1 and word2 in column2" or simply put short, return rows containing word1 and word2 in any column.

i prefer to have regexp so queries will be short for i use it on like 5-6 columns and what if many words are being searched. im open to alternatives though. :)

[fskreuz]

sorry for doublepost, i posted the previous one in my mobile and can't seem to edit it when i used my PC. I have done this today:

SELECT * FROM bb_cu_user63books WHERE (book_title LIKE '%higgins' AND book_title LIKE '%jack%') OR (book_author LIKE '%higgins%' AND book_author LIKE '%jack%')

and it seems to do what i want to do, can SQL be done this way?

[drjohn]

yes, that is standard sql

If you wish to query on multiple columns, you just add them with their WHERE conditions, and using ( ) to guarantee the correct priority of the ANDs and ORs

(although why someone would enter the author as the title...)

PS LIKE '% higgins %' will only detect higgins as a single word,
while LIKE '%higgins%' will also return thiggingson, but you probably know that.

[fskreuz]

thanks for the info drjohn. what im making is a quick search form where you can just about type anything into it and search everywhere for it, just like a search engine where you type any word and return anything containing them. i think my problem is solved.