custom mysql database search assistance

xXandarXx

12-24-2010, 12:49 AM

I have a working search script, but I need to modify it to search better.

Example: "Faith Hill" returns only "Faith Hill". I need both "Faith Hill" and Hill, Faith" to be returned no matter which way the user inputs the string.

Here's my code, any ideas?
<html>
<head>
<title>karaoke search script</title>
</head>
<body onLoad="form.q.focus()">

<form name="form" action="searcho.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>

<?php

// Get the search variable from URL

$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
$trimmed = trim($trimmed, ",");

// rows to return
$limit=100;

// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}

// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","username","password"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("songsdb") or die("Unable to select database"); //select which database we're using

// Build SQL Query
$query = "select * from songs where Title like \"%$trimmed%\" or Artist like \"%$trimmed%\"
order by Title"; // EDIT HERE and specify your table and field names for the SQL query

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
}

// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}

// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<br>You searched for: "" . $var . """;

// begin to show results set
// echo "Results";
$count = 1 + $s ;

// now you can display the results returned
echo "<table border=1><TR><td> </TD><td><b>Title</b></TD><td><b>Artist</b></TD><td><b>Number</b></TD></TR>";
while ($row= mysql_fetch_array($result)) {
$title = $row["Title"];
$artist = $row["Artist"];
$number = $row["Number"];

echo "<tr><td>$count.)</td><td>$title</td><td>$artist</td><td>$number</td></tr>" ;
$count++ ;
}

echo "</table>";
$currPage = (($s/$limit) + 1);

//break before paging
echo "";

// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print "";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

// not last page so give NEXT link
$news=$s+$limit;

echo "";
}

$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<br>Showing results $b to $a of $numrows";

?>

</body>
</html>

abduraooft

12-24-2010, 07:07 AM

You'd need to use mysql's Fulltext search instead of LIKE keyword.

See http://devzone.zend.com/article/1304
http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

xXandarXx

12-24-2010, 10:46 PM

Thanks for the info! I didn't know mysql had added fulltext functionality. Took care of that on my mysql server.

I still don't know how to create that sort of search, however. Simple searches work fine, ex "Faith Hill" returns anything with "Faith Hill" in the string. What doesn't work, however, is when the information in the database is "Hill, Faith". The people searching in the database are only going to type it one way. I need the search to return BOTH sets of results.

Any idea how to do that?

abduraooft

12-25-2010, 01:36 PM

Any idea how to do that? The idea is already given. What you need is to follow it.

xXandarXx

12-27-2010, 06:57 PM

The first one you sent me to has good ideas, but the example code he gave and SAID should work has more holes in it than a brick of swiss cheese. see for yourself:
<?php
/* call this script "this.php" */
if ($c != 1) {
?>
<form action="this.php?c=1">
<input type="text" name="keyword">
<input type="submit" value="Search!">
</form>
<?php
} else if ($c==1) {
MySQL_connect("hostname", "username", "password");
MySQL_select_db("database");
$sql = "
SELECT *,
MATCH(title, body) AGAINST('$keyword') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('$keyword')
ORDER BY score DESC
";
$res = MySQL_query($sql);
?>
<table>
<tr><td>SCORE</td><td>TITLE</td><td>ID#</td></tr>
<?php
while($row = MySQL_fetch_array($rest)) {
echo "<tr><td>{$sql2['score']}</td>";
echo "<td>{$sql2['title']}</td>";
echo "<td>{$sql2['id']}</td></tr>";
}
echo "</table>";
}
?>

Notice most specifically $rest and $sql2 which are not strings anywhere else in the code and have no content. One tends to lose their trust in such rather quickly, but no matter.
After I fixed it I tried to run it. Results, nada. The only thing it will do is to return to it's starting point.

I'm running php 5.2.9 and MySQL 5.0.91 so a natural language search like this one should work. My database is indexed to fulltext in both of the fields I am searching through.

I looked at the page and implemented the code myself using one of my working scripts. The results were the same as if I had never used it. I did the usual MATCH AGAINST WHERE ORDER BY....
My syntax was good and no errors were thrown, but the same results.

What I'm beginning to understand is that MySQL DOES care about word order. What I need is a php script that searches for each of the words in the input string individually, then selects the only results that have all of the words in them to be returned back to the client. According to the pages you gave me, that should have happened. It didn't.

xXandarXx

12-27-2010, 08:02 PM

I can and have done this same thing is asp. The server I am currently on has no asp support, only php.
I'm learning, but I do need some help. As you can see from my reply, I DID read what was given to me and tried very hard to implement it. I was not able to achieve any results.
I'm beginning to think that a different approach to the problem is needed.

xXandarXx

12-27-2010, 10:51 PM

fulltext didn't help me as mysql STILL returns only what's IN ORDER.
soooo....
split()

That worked. No fulltext needed no matter how many people pointed me towards it.

If you are interested I'll post the code.

student101

12-28-2010, 12:04 PM

If you are interested I'll post the code.
Yes please!

Interested in learning from ways of those that tried & tested!
I almost always use ...LIKE %$searchedterm%..., haven't been sold on that fulltext stuff yet.