Results 1 to 3 of 3

Thread: mysql search

  1. #1

    mysql search

    atm i have a website with search options and i have this info on table:
    | field |
    | row with bla and ble
    | row with bloom and bluuuum

    i use this to search the DB:
    SELECT * FROM table WHERE field LIKE '%$string%';

    where $string contains "bla"
    and it returns the 1st example

    but if i search with 2 words (eg: "bla ble")
    doesn't return anything

    how can i search for both words in all text? i only want to get results that match both words and not matching ANY of them... oh, and if i search for "blo" it should return the 2nd row...

  2. #2
    Join Date
    Nov 2002
    Well.. the second row has no instance of 'bla' or 'ble' in it...!

    but yeah, say you search for "blo bla", and you'd expect the have all rows that contain either blo or bla, then you simply have to replace spaces by some 'SQL syntax sugar': try this;

    PHP Code:
    // say this is your search string    
    $string "bla blo";

    // split it in words
    $string explode ' '$string );

    // glue it back with some SQL 'sugar' (!), 
    // for:           this will return:
    // "bla"          "bla"
    // "bla blo"      "bla%' OR field like '%blo"
    // "bla blo ble"  "bla%' OR field like '%blo%' or field like '%ble"
    $string implode "%' OR field like '%"$string );

    // build your select statement...
    $sql "SELECT * from table where ( field like '%$string%' )"
    Alternatively you could try...
    PHP Code:
    // spaces before and after would messup your sql big time!
    $string trim $string );

    $string str_replace ' '"%' or field like '%"$string );

    $sql "SELECT * from table where ( field like '%$string%' )"
    dunno which one ends up faster.. but on a few words... it's not much of an issue really! The first one tho allows you to easily restrict the number of words searched for, just strip the $string array (after the explode) down to whatever the limit should be.

    Hope this helps!


  3. #3
    works, thank you

Posting Permissions

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