SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2008
    Location
    Plano, TX
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL search loop issues

    OK. I have a search engine i've been working on for a while now. i'm almost finished with it but I have one serious problem. It's a keyword search so when somebody types in "pizza hut" it was only searching "pizza hut" well it's not bringing up enough results and we're short on content. so i got a loop that should search "Pizza hut", "pizza", and "hut". and display the results accordingly. i'm not very great with php loops and i'm not even sure about the sql. It does bring up the results. but when i search for "pizza hut" now it only searches "hut" when i type in "Scooby snacks with a side of butter" it only searches "butter". i'm trying to fix that. can anybody help. it'd be greatly appreciated.


    You can view what i'm talking about here. it's not in action with the actual site. it's just a test area i'm using.
    http://www.mylocallookup.com/extended_data/search.php


    Here's the php/mySql that's running that portion.
    PHP Code:
    //trim whitespace from variable
    $name trim($name);
    $name preg_replace('/\s+/'' '$name);

    //seperate multiple keywords into array space delimited
    $keywords explode(" "$name);

    //Clean empty arrays so they don't get every row as result
    $keywords array_diff($keywords, array(""));

    //Set the MySQL query
    if ($name == NULL or $name == '%'){
    } else {
    for (
    $i=0$i<count($keywords); $i++) {
    $queryx "SELECT DISTINCT Name, Telephone, Address, City, State, Zip, Keywords1, Keywords2 FROM $tabledad " .
    "WHERE Name LIKE '%".$keywords[$i]."%'".
    " OR Keywords1 LIKE '%".$keywords[$i]."%'" .
    " OR Keywords2 LIKE '%".$keywords[$i]."%'" .
    " GROUP BY Address ORDER BY Name";
    }
    //Store the results in a variable or die if query fails
    $t mysql_query($queryx) or die(mysql_error());


  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    first, create a fulltext index on (name, keywords1, keywords2). then scrap all of your code and run just this query:
    PHP Code:
    $name mysql_real_escape_string($name);
    $query "
    SELECT Name
         , Telephone
         , Address
         , City
         , State
         , Zip
         , Keywords1
         , Keywords2
      FROM 
    $tabledad 
     WHERE MATCH(name, keywords1, keywords2) AGAINST ('
    $name')"
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2008
    Location
    Plano, TX
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok... what's a fulltext index. i've heard of it. but i've never used it. just keep in mind that my db has as many tables as there are states in the US and almost 3/4 of the businesses in the US are in there.


Bookmarks

Posting Permissions

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