Help With a Contains - Fuzzy Text Search
742821Dec 23 2009 — edited Dec 23 2009Greetings all-
I'm using Oracle 10g on a Linux server. I'm running a contains query in a web script using PHP5. My contains basic contains query works, but I'm now trying to expand it to include a Fuzzy search. I'm reading an online doc at: [ http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cqoper.htm#i997330] and an Oracle 10g book, "The complete Reference".
Here is my query in the script. This matches what is in the online doc I mentioned.
$sql = "SELECT CC_TITLE , CC_ID, CC_REF_NO, TYPE, CC_MONTH
FROM WEBADMIN.CHR_CONTENT CC
WHERE CONTAINS(cc_search, 'fuzzy(?, 70, 6, weight)', 1) > 0
AND CC_ISLIVE = ? ORDER BY CC_TITLE";
$sqlParams = array($word_search,1);
For this, I'm getting the following error:
[nativecode=ORA-01036: illegal variable name/number]"]
For clarity of my sql code. The question marks (?) are parameter-bindings. Each ? is bound to a value in the $sqlParams array. The first ? gets the value of $word_search and the second gets the value of 1. $word_search is the search word a user would enter in the search field on the search form. I need to use param bindings like this because I do not know what the search term(s) are that any user might enter. The use of the 70 and the 6 are explained in the online doc by this line:
This query expands to the first six fuzzy variations of government in the index that have a similarity score over 70.
The Oracle book says to write the query like so.
$sql = "SELECT CC_TITLE , CC_ID, CC_REF_NO, TYPE, CC_MONTH
FROM WEBADMIN.CHR_CONTENT CC
WHERE CONTAINS(cc_searc, '?$word_search') > 0
AND CC_ISLIVE = 1 ORDER BY CC_TITLE";
If I use param bindings for $word_search and the 1 my query would look like so:
$sql = "SELECT CC_TITLE , CC_ID, CC_REF_NO, TYPE, CC_MONTH
FROM WEBADMIN.CHR_CONTENT CC
WHERE CONTAINS(cc_searc, '??') > 0
AND CC_ISLIVE = ? ORDER BY CC_TITLE";
How do I escape the leading '?' in the contains '??'
Can anyone tell me what I'm doing wrong? Thanks in advance.
Cheers!