string search in mysql

[rafiq009]

hi, i am trying to play with string. so i need some suggestion.

for example i have a database containing id, book name, author name, publish year. now i would like to make a php script that will search book information from the database. User may type part of the book name or author name, may be uppercase or lowercase problem or spell mistake then how could i provide a good search result?

Database:
store(id, book_title, author, publish_year)

form:

<form method="post" action="result.php">
<table >
	<tr>
		<td ><strong>Book Name</strong></td>
		<td ><strong>Author</strong></td>
	</tr>
	<tr>
		<td><input name=book_name type=text ></td>
		<td><input name=author type=text ></td>
	</tr>
	<tr>
		<td colspan=2 align=center><input type=submit name=Search value=Search></td>
	</tr>
<table>
</form>

result.php :

<?php
$title=$_POST['book_name'];
$author = $_POST["author"];

$sql=mysql_query("select * from store where book_title like %$title% or author like %$author% ");
$db=mysql_query($sql);
?>

[misson]

This is not an easy task. You could try fulltext searching, though it won't fulfill all your requirements (it's case insensitive, but won't handle spelling mistakes). As per Nathan Ostgard's suggestion, to handle spelling differences you can create additional columns holding the soundex string for each column. You could also examine and incorporate code that gives spelling suggestions when there are no search results (e.g. Robert Douglass' Drupal spelling suggestion patch (yeah open source!)). Your best bet is probably to search the web for a search engine, rather than trying to write your own. Just be warned that anything other than a trivial search engine will require a high usage of server resources. If you have a free site, it could repeatedly get suspended for resource usage.

Also see the thread "Improved Search Algorythm (Alternative to FULLTEXT)".