Improving a search script ( 5 Views )

1. I've made a script that searches for either albums, artists or song titles in a mysql db with a lot of soundtrack-data. So far one can search with options such as "Starts with" and "Ends with" which is easily accomplished by the use of % before or after the search string in the SELECT-statement. A default search without the starts/ends with-option enabled has the % both before and after ('%$searchtext%') so that one can search for something in the middle of a word/title. My problem now is how to make the search more flexible. Lets take the soundtrack "Indiana Jones And The Last Crusade" by John Williams as an example. With my script one has to search for e.g. "Indiana Jones" or "Last Crusade" for the script to return this soundtrack. Searching for something like "Indiana Jones Last Crusade" will not. (The quotes are not used in the actual search, just here to show where things start and end :-) Anyone got any experience doing this stuff? I've been looking in forums, books, newsgroups, tutorials etc. but cant find anything of use. My guess is that I have to use explode() and use the single-word values I get from that in the SELECT. If this is the right way to do it any examples or guidelines is appreciated.
   
   
   
   Here's the code for the albumsearch part of my script.
   

   PHP Code:

   ---

   mysql_select_db("SAMDB",$db);
if ($place == "startswith") {
$searchresult = mysql_query("SELECT DISTINCT album, artist, albumyear, info FROM songlist WHERE album like '$searchtext%' ORDER BY '$orderby' $order LIMIT $limit",$db);
}
elseif ($place == "endswith") {
$searchresult = mysql_query("SELECT DISTINCT album, artist, albumyear, info FROM songlist WHERE album like '%$searchtext' ORDER BY '$orderby' $order LIMIT $limit",$db);
}
else {
$searchresult = mysql_query("SELECT DISTINCT album, artist, albumyear, info FROM songlist WHERE album like '%$searchtext%' ORDER BY '$orderby' $order LIMIT $limit",$db);
}
echo "<th class=\"th01\">Album - Artist</th><th class=\"th01\">Year</th>";
while ($searchoutput = mysql_fetch_array($searchresult))
{
echo "<tr>";
echo "<td class=\"td01\">";
echo "<a href=\"albuminfo.php?albumasin=";
echo $searchoutput["info"];
echo "\">";
echo $searchoutput["album"];
echo "&nbsp;-&nbsp;";
echo $searchoutput["artist"];
echo "</a>";
echo "</td>";

echo "<td class=\"td01\" width=\"50\" align=\"center\">";
echo $searchoutput["albumyear"];
echo "</td>";
echo "</tr>";
}


   ---

   
   
   (cengiz, Bouvet Island)


2. I haven't got the answer to your query; the only thing I thought of was, as you said, to use explode(). But while I was here I thought I'd tidy up your code and save the file-size.
   
   

   PHP Code:

   ---

   <?php
mysql_select_db("SAMDB",$db);
if ($place == "startswith" ) {
$searchresult = mysql_query("SELECT DISTINCT album, artist, albumyear, info FROM songlist WHERE album like '$searchtext%' ORDER BY '$orderby' $order LIMIT $limit",$db);
}
elseif ($place == "endswith" ) {
$searchresult = mysql_query("SELECT DISTINCT album, artist, albumyear, info FROM songlist WHERE album like '%$searchtext' ORDER BY '$orderby' $order LIMIT $limit",$db);
}
else {
$searchresult = mysql_query("SELECT DISTINCT album, artist, albumyear, info FROM songlist WHERE album like '%$searchtext%' ORDER BY '$orderby' $order LIMIT $limit",$db);
}
echo "<th class=\"th01\">Album - Artist</th><th class=\"th01\">Year</th>";
while ($searchoutput = mysql_fetch_array($searchresult))
{
?>
<tr>
  <td class="td01">
    <a href="albuminfo.php?albumasin=<?=$searchoutput["info"]?>"><?=$searchoutput["album"]?> - <?=$searchoutput["artist"]?></a>
  </td>

  <td class="td01" width="50" align="center">
    <?=$searchoutput["albumyear"]?>
  </td>
</tr>

   ---

   Good luck with the script!
   
   - Will
   
   
   (hasan, Martinique)


3. I too don't have the 100% correct answer however one thing that will help is the use of mysql 4's fulltext column type.
   
   Defining a column as fulltext allows for more efficient searching abilities in mysql.
   
   Take a look at it at mysql.com
   
   
   HTH
   
   GM
   
   
   (mekansýz, Georgia)


4. Thanks for the tips. I worked on the script this evening and with the use of explode() and some modifications to my SELECTS I have reached my goal. You can test it out here.
   
   
   (murat, Azerbaijan)

Related Topics ... (or search in 1.720.883 topics !)