...

View Full Version : Handling user input of "and" in an SQL search



htcilt
01-29-2010, 11:17 AM
I'm putting together a search form that queries an Oracle table of books by thier title.

The echoed sql is:


SELECT SCORE(1), TITLE FROM BOOKS WHERE (CONTAINS(TITLE, :1||'%'||' AND '||:2||'%', 1) > 0) ORDER BY score(1) DESC, TITLE ASC

The bind variables in the contains statement is built up dynamically using:


$sql = "SELECT SCORE(1), TITLE FROM BOOKS WHERE (CONTAINS(TITLE, ";
// FOR EACH KEYWORD INSERT 'AND'
for ($i = 1; $i <= $counter; ++$i) {
$sql .= ":$i||'%'";
if ($i!=$counter) {
$sql .= "||' AND '||";
}
}
$sql .= ', 1) > 0)';
$sql .= ' ORDER BY score(1) DESC, TITLE ASC';

This all works as expected until dividing words like 'and', 'for', 'in' are used.
I then get no results even if the searched title is exactly as it appears in the database and in the same case.
I can strip them out using str_replace to make the results come back, but I really need to find a way to get them working.

Can anyone shed any light on this?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum