CODEWITHSUNDEEP
phpmysqldatabasesearch
user1111042
user1111042

Reputation: 1531

how to implement the an effective search algorithm when using php and a mysql database?

I'm new to web design, especially backend design so I have a few questions about implementing a search function in PHP. I already set up a MySQL connection but I don't know how to access specific rows in the MySQL table. Also is the similar text function implemented correctly considering I want to return results that are nearly the same as the search term? Right now, I can only return results that are the exact same or it gives "no result." For example, if I search "tex" it would return results containing "text"? I realize that there are a lot of mistakes in my coding and logic, so please help if possible. Event is the name of the row I am trying to access. 

$input = $_POST["searchevent"];
while ($events = mysql_fetch_row($Event)) {
$eventname = $events[1];
$eventid = $events[0];
$diff = similar_text($input, $event, $hold)
if ($hold == '100') {
echo $eventname;

break;

else
echo "no result";

}

Thank you.

I've noticed some of the comments mentioned more efficient ways of performing the search than with the "similar text" function, if I were to use the LIKE function, how would it be implemented?

Upvotes: 2

Views: 5113

Answers (2)

bpeterson76
bpeterson76

Reputation: 12870

A couple of different ways of doing this:

The faster one (performance wise) is: select * FROM Table where keyword LIKE '%value%' The trick in this one is the placement of the % which is a wildcard, saying either search everything that ends or begins with this value.

A more flexible but (slightly) slower one could be the REGEXP function:

Select * FROM Table WHERE keyword REGEXP 'value'

This is using the power of regular expressions, so you could get as elaborate as you wanted with it. However, leaving as above gives you a "poor man's Google" of sorts, allowing the search to be bits and pieces of overall fields.

The sticky part comes in if you're trying to search names. For example, either would find the name "smith" if you searched SMI. However, neither would find "Jon Smith" if there was a first and last name field separated. So, you'd have to do some concatenation for the search to find either Jon OR Smith OR Jon Smith OR Smith, Jon. It can really snowball from there.

Of course, if you're doing some sort of advanced search, you'll have to condition your query accordingly. So, for instance, if you wanted to search first, last, address, then your query would have to test for each:

SELECT * FROM table WHERE first LIKE '%value%' OR last LIKE '%value%' OR address LIKE '%value'

Upvotes: 1

user319198
user319198

Reputation:

Look at below example :

$word2compare = "stupid";

$words = array(
    'stupid',
    'stu and pid',
    'hello',
    'foobar',
    'stpid',
    'upid',
    'stuuupid',
    'sstuuupiiid',
);

while(list($id, $str) = each($words)){
    similar_text($str, $word2compare, $percent);
    if($percent > 90)    // Change percentage value to 80,70,60 and see changes
    print "Comparing '$word2compare' with '$str': ";

}

You can check with $percent parameter for how strong match you want to apply.

Upvotes: 0

Related Questions