Search field and search result troubles

mpar612

Hi everyone,

I am trying to create a search feature for a site. A user will enter a keyword, click submit and a query will execute and compare the users input to several fields in the database and the results will be displayed on the screen.

If it's not too difficult I would also like the search to find words that are similar to what typed in by the user. For example, if the user makes a typo, results similar to what they typed in will be displayed.

I am using PHP, MySQL and PEAR.

I'm a little lost with how this works. The code I have been trying is posted below. Any feedback would be greatly appreciated. Thanks!

<?php
require 'DB.php';
$db = DB::connect('');
if (DB::isError($db)) { die ("Can't connect: " . $db->getMessage()); }
$db->setErrorHandling(PEAR_ERROR_DIE);
$db->setFetchMode(DB_FETCHMODE_ASSOC);

if (isset($_POST['keyword'])) {
process_form();
} else {
print "This did not work!";
}
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form name="form1" method="post" action="">
  <input name="keyword" type="text" id="keyword">
  <input type="submit" name="Submit" value="Search">
</form>
</body>
</html>

<?php
function process_form() {
global $db;
$keywords = $_POST[keyword];
$rows = $db->getAll('SELECT isbn, artist_name, album_title, description, track_list, price, image FROM lounge WHERE MATCH(isbn) AGAINST(\'$keywords\')');
print "<table>";
foreach ($rows as $row) {
print "<tr><td>$row[isbn], $row[artist_name]</td></tr>";
}
}
?>

timus

Hey

There are a few things you may want to look at. See if the database you are using supports soundex() or sounds like function. This works by generating a value based on what the word should sound like and then comparing the value. For example "Wheel" and "Wheeell" would show the same result

Also look on the PHP website for metaphone function and other functions mentioned in relation. Hopefully that might give you a few ideas

Regards

Tim

mpar612

Thanks!

SOUNDEX does not work for me, but SOUNDS LIKE does. It doesn't really work well.

For example, if I use the following code. No matter what I enter in the text field, it returns all of my listings and the isbn #'s aren't anything alike.

$sql = "SELECT isbn, artist_name, album_title, description, track_list, price, image FROM lounge WHERE isbn SOUNDS LIKE $keywords";
$rows = $db->getAll($sql);

If I use the following code, I get a DB Error: no such field when I enter one word. I get a DB Error: syntax error if I enter two words. If I enter a number, it returns nothing.

$sql = "SELECT isbn, artist_name, album_title, description, track_list, price, image FROM lounge WHERE artist_name SOUNDS LIKE $keywords";
$rows = $db->getAll($sql);

Is there an issue with my query that I am missing?

Thanks!

ClarkF1

you need single quotes around $keywords in the query

mpar612

Thank you very much!!!

That works, but I have to type in exactly what is in the database to get results. How do I modify this code to allow for things that are slightly different. For example, if I have a name in the database "digital river" and a user types "digital" or "dig" in the search form.

Also, I would like this to search all of the fields in the database and not just one. Is this possible?

I am not asking for the exact answers, I am asking for some hints to send me in the right direction. Any input would be great.

Thanks!

ClarkF1

For multiple fields you could use OR and perform the check on each column.

As for people entering part of a string you use % so you have

 $sql = "SELECT isbn, artist_name, album_title, description, track_list, price, image FROM lounge WHERE artist_name SOUNDS LIKE '%$keywords%'";

This will search for anything with the value of $keywords in it regardless of what's before it or after it. You can limit it by removing either of the % so it only searches for the keyword and anything after it or the keyword and anything before it.

mpar612

Thanks for your response! The use of % really didn't do anything. Here is a link to the page I am trying to get to work http://www.mikeparkermusic.com/search_test.php. It is using the SQL statement below. If you type anything into the text box it returns everything (there are currently only 12 records) in the database.

Does anyone have any thoughts? Thanks!

$sql = "SELECT isbn, artist_name, album_title, description, track_list, price, image FROM lounge WHERE isbn OR artist_name SOUNDS LIKE '%$keywords%'";

ClarkF1

Try

 $sql = "SELECT isbn, artist_name, album_title, description, track_list, price, image FROM lounge WHERE isbn LIKE '%$keywords%' OR artist_name LIKE '%$keywords%'";

mpar612

That was simple. That did it. Thank you very much for your help!