Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    238
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Handling user input of "and" in an SQL search

    I'm putting together a search form that queries an Oracle table of books by thier title.

    The echoed sql is:

    Code:
    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:

    PHP Code:
     $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?
    Last edited by htcilt; 01-29-2010 at 12:36 PM.


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •