• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

help with sql for autocomplete script

hi,
i'm writing an AJAX/php autocomplete script for search..
and got some problems...

i have a db, with text like
"Hockey Night In Canda"

i have 2000+ records and growing,
i need some way to make it quick (as much as posible) even when it'll be 100,000+ records (and it will)

anyway, my script looks for user input in text box, when he inputs more then 3 chars, it starts checking the db, all works fine,
exept the sql =]

what i have now is:

SELECT title  FROM tbl_titles where (title like '%$search%' and cat='$cat');

what i wont is to select only the matching words, and not the whole phrase, and to output them one by one
like autocomplete... (where "comp" is "computer","compilation","comparison" .........)
any ideas?
0
svsvsv
Asked:
svsvsv
  • 5
  • 3
  • 2
  • +1
1 Solution
 
RoonaanCommented:
Hi,

Although ajax is nice, you are putting a real strain on your database.

You can however do it like this:

$search_words = preg_split('/\W/', $search);

if(count($search_words) > 0) {
  $query = 'SELECT title FROM tbl_titles where cat="'.mysql_real_escape_string($cat).'" ';
  $query .= 'AND (title like "%'.implode('%" OR title like "%', $search_words).'%" )';
} else {
  //no results
}

-r-
0
 
snoyes_jwCommented:
Do you really need that leading wildcard?  Isn't "title like '$search%'" enough?
0
 
svsvsvAuthor Commented:
but that shows me the whole title
i need just the keywords
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
RoonaanCommented:
That is easier done through php than through sql I think.

-r-
0
 
svsvsvAuthor Commented:
can you show me what you have in mind?
0
 
RoonaanCommented:
<?php

$keywords = preg_split('/\W/', $search);

And then in your output use:

$preg = '/^(.*)('.implode('|', $keywords).'(.*)$/i';

while($row = mysql_fetch_assoc($result)) {
  echo preg_replace($preg, '\2', $row['title']);
}

-r-
0
 
svsvsvAuthor Commented:
[function.preg-replace]: Compilation failed: missing ) at offset 14
0
 
svsvsvAuthor Commented:
ok, found the )
but it dosen't really work
0
 
aminerdCommented:
You'd probably be better off building a table of _just_ the keywords. Then you can index them (or parts of them), which will make the SELECT faster (especially since you'll need a trailing wildcard, which means you can actually _use_ the indexes).

Otherwise, you could do something like:

      $query = "SELECT title FROM tbl_titles where (title like '%$search%' and cat='$cat')";
      $result = mysql_query($query);
      
      $keywords = array();
      
      while ($rec = mysql_fetch_assoc($result))
      {
            
            // find just the matching keywords from the title
            if (preg_match_all('/\w*'.preg_quote($search, '/').'\w*/', $rec['title'], $matches))
            {
                  $keywords = array_unique(array_merge($keywords, $matches[0]));
            }
            
      }
      
      var_dump($keywords);
0
 
svsvsvAuthor Commented:
this one is the best sofar, thanx aminerd but 1 more thing, it shows me all matches,
i need to cut them down so there won't be any duplicates
0
 
aminerdCommented:
$keywords = array_unique(array_merge($keywords, $matches[0]));

That should only you a unique list, but it's case-sensitive, so try:

     $query = "SELECT title FROM tbl_titles where (title like '%$search%' and cat='$cat')";
     $result = mysql_query($query);
     
     $keywords = array();
     
     // lowercase it for matching purposes
     $search = strtolower($search);

     while ($rec = mysql_fetch_assoc($result))
     {
         
          // find just the matching keywords from the title
          if (preg_match_all('/\w*'.preg_quote($search, '/').'\w*/', strtolower($rec['title']), $matches))
          {
               $keywords = array_unique(array_merge($keywords, $matches[0]));
          }
         
     }
     
     var_dump($keywords);
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now