php mysql search engine

Question

Currently i am trying to create a search feature for my website that will allow users to search for other users using their full name (first or last) or their username. Right now i have created a temporary search that uses mysql's LIKE feature and %name% to allow for wild cards. Its very simple. What I'm having trouble with is trying to search for names when a full name is provided and only a shortened version of the name is in the database. For instance:

Searching Richard will only come up with Richards and not Richs. I'm really stumped as to how to create something that will do this without it being very inefficient. I was going to use SOUNDEX but that would only be useful for english names and words.

Also I wanted to ask if its a better idea to use a search engine like Apaches Lucene to index the databases? I was contemplating this but wasn't sure if it would be worth it since the search engine would only be searching for small full text strings.

Thank you

Try using a `levensthein` filter http://stackoverflow.com/questions/4671378/levenshtein-mysql-php — elclanrs, Aug 30 '13 at 22:37
It seems this is a best way to go but is it the best method to use for large databases? Once the information is put back into the database I'm searching its going to be an inventory of over a million users. — tiantang, Aug 30 '13 at 23:05

score 0 · Accepted Answer · edited May 23 '17 at 10:27

0

Use Approximate string matching to rank results, and show all results within a given threshold.

You can start with levenshtein as mentioned in the comments, and return the lowest scores. Levenshtein computes difference, so a low score means high similarity. A score of 0 means exact match.

edited May 23 '17 at 10:27

Community

1
1

answered Aug 30 '13 at 22:42

Jo Are By

3,293
1
11
11

how i can install levenshtein in mysql? – Jeevan Roy dsouza Dec 10 '13 at 09:40

score 0 · Answer 2 · answered Aug 30 '13 at 23:02

If they put in full name you first have to seperate the names with PHP explode according to white spaces.
Then you have to take the first name and split it according to how many characters you want. eg. if you have Antonio, splitting to 4 characters give Anto.
Then you do your search function.

Here is how to split to 4 characters:
$str = "Antonia";
$arr2 = str_split($str, 4);
echo $arr2[0];

score -2 · Answer 3 · answered Aug 30 '13 at 22:40

You can try with the following:

search.html

<html>
    <head>
        <title>Search the Database</title>
    </head>

    <body>

    <form action="/test/search.php" method="post">
     Search: <input type="text" name="term" /><br />
    <input type="submit" name="submit" value="Submit" />
    </form>

    </body>
 </html>

search.php

<?php
mysql_connect ("localhost", "username","password")  or die (mysql_error());
mysql_select_db ("database");

$term = $_POST['term'];

$sql = mysql_query("select * from database where XXXXXX like '%$term%'");

while ($row = mysql_fetch_array($sql)){
    echo 'ID: '.$row['XXXXX'];
    echo '<br/> First Name: '.$row['XXXX'];
    echo '<br/> Last Name: '.$row['XXXX'];
    echo '<br/> Phone: '.$row['XXXX'];
    echo '<br/><br/>';
    }

?>

This is basically what I have except using PDO and what not. But if I enter a full name it will not come up with the abbreviated names. IE Richard => Richard , Rich => Richard, Rich — tiantang, Aug 30 '13 at 22:42

php mysql search engine

3 Answers3