Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    4

    Unanswered: FULLTEXT search probs

    Hi,
    I have a problem with a fulltext search. When trying to search for strings like 2007-03-23, all the rows in the database are shown (even if they don't include the date or numbers). If I search for names or words, the search works just the way I wants it.

    $sql = "SELECT * FROM `private` WHERE MATCH (date, names, words) AGAINST ('$search' IN BOOLEAN MODE) order by id DESC";

    Do anybody know how I can solve this problem? Have I done something wrong?

  2. #2
    Join Date
    Mar 2007
    Posts
    4
    *bump*

    please?

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Firstly : surely your date column is of type date or datetime ???
    Secondly : if it's not a date and is instead a string then have you enabled fulltext searching on it?

    If it is a date then you can't do a fulltext search on it.

    Also bear in mind that MySQL might see that as a calculation
    i.e. 2007 - 3 - 23 = 1981

    How it treats that i'm not sure. I'm guessing you are using inverted commas to keep it a string test.

  4. #4
    Join Date
    Mar 2007
    Posts
    4
    Firstly : surely your date column is of type date or datetime ???
    Secondly : if it's not a date and is instead a string then have you enabled fulltext searching on it?

    The column is of the type date and I have made a fulltext index of the column together with some other columns.

    Hmm.. if I can't do a fulltext search, can I do like this? (for example)

    Code:
    if (preg_match("/^\"?(\d{2})-(\d{2})-(\d{4})\"?$/",$fritext)) {
    $search = explode("-", $fritext);
    $search = "`".$search[2]."-".$search[1]."-".$search[0]."`";
    
    $sql  = "SELECT * FROM `private` WHERE date = $search order by id DESC";
    $result = mysql_query($sql) or die("SQL: $sql <br>".mysql_error());
    }
     
    else {
    $search = $fritext;
    
    $sql  = "SELECT * FROM `private` WHERE MATCH (name, date, words) AGAINST ('$search' IN BOOLEAN MODE) order by id DESC";
    $result = mysql_query($sql) or die("SQL: $sql <br>".mysql_error());
    }
    Thanks in advance!

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I'm guessing going by the preg_match statement that users a typing in the date i.e. 26-3-2007 or 26/3/2007 or 26/03/07 .

    My recommendation is that if they need to put a date in constrict their options with dropdown boxes (thus giving you the date format you require). The reason being as I demonstrated just above is that you never know how your date format is going to come in.

    Thus your SQL statement should become
    SELECT * FROM `private` WHERE date='{$date}' AND MATCH(name,words) AGAINST ('{$search}' IN BOOLEAN MODE) ORDER BY id DESC


    n.b. i'm assuming dd-mm-yyyy . Which might not be the case with your preg statement. It could be mm-dd-yyyy . Beware of this as it will perform a bad query.
    Last edited by aschk; 03-26-07 at 09:08.

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Ooh I could be wrong here. Never tried two WHERE clauses with FULLTEXT searching.

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Either way you should NOT be fulltext searching on a date column. Fulltext is only really applicable for TEXT types where you're trying to find an instance of a phrase inside it. For short phrases use LIKE('%variable%')
    Last edited by aschk; 03-26-07 at 09:08.

  8. #8
    Join Date
    Mar 2007
    Posts
    4
    Hi again,
    thanks for all the help!

    I dropped the fulltext thing for the date column and made two different actions depending on the input. If it's a date I only do a search in the date column, and if it's a word or name, I do a fulltext search instead.

    Btw, I have a calendar (Tigra calendar) that writes out dates in the wrong order, thats why I have to change the order in the search

    Thanks again!

Posting Permissions

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