Searching MySQL

cheezyfries

I'm doing a search function with PHP and MySQL for a football site that you can search for players from all over. The person would type in the last name of the player and it would then pull the information out of the database to display it. pretty simple.

But if someone types the name wrong, nothing will show up. Is there anyway to do a search with PHP and MySQL that would search and pull up somethiing similiar to what they typed?

An example:
Say a players last name is Patterson, but the person searches for Paterson with only one "t", is there a way to let the script now to pull out the closet match?

This is what I have:
Here is the search page:
<form name="search" action="search.php" method="post">
<input type="text" name="name" size="24">
<input type="submit" name="Search Players">
</form>

And here is search.php:
<?
// open connection to database
$connection = mysql_connect("localhost", "user", "pass") or die ("Unable
to connect!");
mysql_select_db("databaseName") or die ("Unable to select database!");

$search = $_POST['name']; // the name the user entered and submitted.
$name_array = array(); // to hold all the unique names returned from your queries.
$len = strlen($search);
if ($len >= 1) {
$on = 1;
do {
$q = "SELECT concat(name_l, ', ', name_f) as pname FROM " .
"databaseName WHERE name_l like '" .
substr($search, 0, $on) . "%' OR name_f like '" .
substr($search, 0, $on) . "%'";
$res = mysql_query($q);
for ($i = 0; $i < mysql_num_rows($res); $i++) {
$row = mysql_fetch_array($res);

       $pname = $row['pname'];
       if (!in_array($pname, $name_array)) {
           $name_array[] = $pname;
       }
   }
   $on++;

} while ($on <= $len);
}
?>

I want people to be able to do a search on the players last name. That field in the database is name_l
I would like it to pull out a name(s) name even if they spell the name wrong.
Is there anyone that can help me get this to work?

leatherback

Hehe,

That is a difficult one. I think you'd have to generate a regular expression 'on the fly'. but complex...

kburger

Never tried it myself, but you might find some useful information in the manual for metaphone().

Edit: This would be an interesting challenge. The example given for levenshtein() might give you a start. The logic might be something along the lines of -- query the DB and put all unique names into an array. Then if there wasn't an exact match for what they typed in, use levenshtein() to find out which names in the array were closest to what they entered.

leatherback

kburger wrote:
Never tried it myself, but you might find some useful information in the manual for metaphone().

Edit: This would be an interesting challenge. The example given for levenshtein() might give you a start. The logic might be something along the lines of -- query the DB and put all unique names into an array. Then if there wasn't an exact match for what they typed in, use levenshtein() to find out which names in the array were closest to what they entered.

Hm.. Looks intersting!
But would still remain quite an investment, programwise, I suppose..

Any takers?

kburger

Give me a few minutes. I'm working on it. Just a simple example.

kburger

OK. Here's a very simple example based upon the example from the manual that should get you started. You should be able to modify it to suit your needs.

if ($_POST['submit']) {
	// build an array $names[] of distinct last names
	$query = "SELECT DISTINCT name_l FROM databaseName";
	$result = mysql_query($query);
	$names = array();
	while (list($name) = mysql_fetch_row($result)) $names[] = $name;

// no shortest distance found, yet
$shortest = -1;

// loop through words to find the closest
foreach ($names as $name) {
	// calculate the distance between the input word,
	// and the current word
	$lev = levenshtein($_POST['name'], $name);

	// check for an exact match
	if ($lev == 0) {
		// closest word is this one (exact match)
		$closest = $name;
		$shortest = 0;
		// break out of the loop; we've found an exact match
		break;
	}

	// if this distance is less than the next found shortest
	// distance, OR if a next shortest word has not yet been found
	if ($lev <= $shortest || $shortest < 0) {
		// set the closest match, and shortest distance
		$closest  = $name;
		$shortest = $lev;
	}
}

echo "Input search: " . $_POST['name'] . "<br>";
if ($shortest == 0) {
	echo "Exact match found: $closest\n";
} else {
	echo "Did you mean: $closest?\n";
}



} else {
	?>
	<form method="post" action="">
	<input type="text" size="20" name="name">
	<input type="submit" name="submit" value="submit">
	</form>
	<?
}