Search

[Gimlin]

Could someone point me to an easy tutorial on making a MySQL search engine?

[The Hobo]

A small example: http://www.snippetlibrary.com/viewht...d=12&siteid=88

[Gimlin]

I think I a going to need some help to understand whats going on and what happens next.

[chrisjk]

The idea in that snippet is a sound one, except for the fact the search terms are hardcoded into the sql string and there are only 3 of them

You have to build the statement on the fly, like this

<?php
// simple search
// $query = search query from form
$query = 'hello world';

$sql = 'SELECT * FROM table WHERE ';

// split into terms on space
$terms = explode(' ', $query);

// loop through array building sql statement
for ($i=0; $i < count($terms); $i++) {
    $sql = $sql."Field LIKE '%$terms[$i]%' AND ";
}
//remove final 'AND '
$sql = substr($sql, 0, -4);
// add ending SQL
$sql = $sql.'ORDER BY Field';

// do whatever with SQL string
print $sql;

?>

[Gimlin]

this the best way to do it?

[chrisjk]

I'd be amazed to hear a better one...

[The Hobo]

You could also add an option to "Match Exact Text" and then do:

<?php
// simple search
// $query = search query from form
$query = 'hello world';

if ($_REQUEST['exact']) { //exact is the name of our option button
  $sql = "SELECT * FROM table WHERE Field LIKE '%$query%' ";
} else {
  $sql = 'SELECT * FROM table WHERE ';

  // split into terms on space
  $terms = explode(' ', $query);

  // loop through array building sql statement
  for ($i=0; $i < count($terms); $i++) {
    $sql = $sql."Field LIKE '%$terms[$i]%' AND ";
  }
  //remove final 'AND '
  $sql = substr($sql, 0, -4);
}

// add ending SQL
$sql .= 'ORDER BY Field';

// do whatever with SQL string
print $sql;

?>

Just a thought.

[Gimlin]

Originally posted by chrisjk
I'd be amazed to hear a better one...

Hey, I was just asking I dont know alot about this stuff ok

[chrisjk]

if you want to do a pure Exact Match only search then you don't need to build the SQL statement on the fly at all, that's the only advantage but your users will soon get pissed off so from a usability point of view the best way IMO is to do as Hobo suggests and give the user a choice between All, Any and Exact or something

[Gimlin]

what would be cool, if you could do some sort of teir system. Like some types in "hello world". It will first find all the exact matches and print, then break "hello world" into "hello" and "world" and search and post results.

[scoutt]

tht wouldn't be very good as someone will type in "the" and it will find "the" and that can return a lot of results. I would go that direction.

beside add $query to chris's and his will do that.