I have a search engine that searches through my sql table called sheets. Inside this table are the following columns:
id int(11) No
artist varchar(100) No
title varchar(100) No
url varchar(2083) No
timesdownloaded int(11) No
lastdownloaded date No
todaydownloads int(11) No
date date No
active varchar(3) No
duplicate int(1)
My search script searches for matches inside the Artist or Title columns.
However there is one problem.
Let's say I have 5 records (I will show the first three columns):
ID .. Artist .. Title
1 .. ABBA .. Super Trouper
2 .. Super Castlevania .. First Stage
3 .. Super Mario Brothers .. Underwater
4 .. Mario .. Theme
5 .. Super Junior .. Sorry, Sorry
So if I search for "Super Mario", right now it is displaying the following results (So right now it is searching phrases, rather than single words):
3 .. Super Mario Brothers .. Underwater
However, I need to modify my code so it will display the following results:
1 .. ABBA .. Super Trouper
2 .. Super Castlevania .. First Stage
3 .. Super Mario Brothers .. Underwater
4 .. Mario .. Theme
5 .. Super Junior .. Sorry, Sorry
Here is my full code, and any suggestions appreciated. I'll be working on it.
<?php
session_start();
include_once('inc/connect.php');
if (isset($_SESSION['username'])){
$loginstatus = "logout";
}
else{
$loginstatus = "login";
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Search - Sheet Music Haven</title>
<link rel="stylesheet" type="text/css" href="styles/style.css">
</head>
<body bgcolor="#343331" OnLoad="document.form.q.focus();">
<?php include('inc/reporterrors.php'); ?>
<!-- Content Middle -->
<div id="contentmiddle">
<div id="content">
<?php
include_once('inc/functions.php');
// Get the search variable from URL
$var = @mysql_safe($_GET['q']) ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=10000;
$date = date("Y-m-d");
$ip = $_SERVER['REMOTE_ADDR'];
// check for an empty string and display a message.
if ($trimmed == "")
{
$error = "<tr><td colspan='2' style='text-align: center; border-style: solid; border-color: #f43636; background-color: #fe6a6a;'><strong>Type In A Sheet To Search For</strong></td></tr>";
}
// check for a search parameter
if (!isset($var))
{
$error = "<tr><td colspan='2' style='text-align: center; border-style: solid; border-color: #f43636; background-color: #fe6a6a;'><strong>Type In A Sheet To Search For</strong></td></tr>";
}
// Build SQL Query
$query = "select * from `sheets` where `active`='yes' AND (artist like \"%$trimmed%\" OR title like \"%$trimmed%\") ORDER BY `artist`";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
if ($numrows == 0 || $var=="delete" || $var=="DELETE")
{
// If search was not found
$error = "<tr><td colspan='2' style='text-align: center; border-style: solid; border-color: #f43636; background-color: #fe6a6a;'><strong>Unfortunately that sheet was not found. Please request it by clicking below:</strong></td></tr><tr><td colspan='2' style='text-align: center; border-left-style: solid; border-bottom-style: solid; border-right-style: solid; border-color: #f43636; background-color: #fe6a6a;'>Tip: Keep your search phrase short and simple for best results!</td></tr><tr><td colspan='2' style='text-align: center; border-left-style: solid; border-bottom-style: solid; border-right-style: solid; border-color: #f43636; background-color: #f5f5f5;'><a href='request.php'>Request A Sheet Here</a></td></tr>";
// Insert search phrase ($var) into 'Search' table. id, phrase, date, resultsfound
$word = explode(" ", $var);
$num = 0;
foreach($word as $key=>$value){
$wordexist = mysql_query("SELECT word FROM searchedwords WHERE word='$word[$num]'");
$wordcount = mysql_num_rows($wordexist);
if($wordcount!=0){
//UPDATE
$wordget = mysql_query("SELECT * FROM searchedwords WHERE word='$word[$num]'");
$wordrow = mysql_fetch_assoc($wordget);
$todayword = $wordrow['today'];
$totalword = $wordrow['total'];
$newtoday = $todayword+1;
$newtotal = $totalword+1;
$updateword = "UPDATE `searchedwords` SET `today`='$newtoday', `total`='$newtotal' WHERE `word`='$word[$num]'";
mysql_query($updateword);
$num++;
}
else{
$addone = 1;
$wordinsert = mysql_query("INSERT INTO searchedwords VALUES ('','$word[$num]','$addone','$addone')");
$num++;
}
}
$searchphrase = mysql_query("INSERT INTO search VALUES ('','$var','$date','no','$ip')");
if($var!=""){
$search = "Search:";
$break = "";
}
}
else{
// 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");
$search = " ";
$break = "<br>";
if($var!=""){
$search = "Search:";
$break = "";
// Insert search phrase ($var) into 'Search' table. id, phrase, date, resultsfound
$word = explode(" ", $var);
$num = 0;
foreach($word as $key=>$value){
$wordexist = mysql_query("SELECT word FROM searchedwords WHERE word='$word[$num]'");
$wordcount = mysql_num_rows($wordexist);
if($wordcount!=0){
//UPDATE
$wordget = mysql_query("SELECT * FROM searchedwords WHERE word='$word[$num]'");
$wordrow = mysql_fetch_assoc($wordget);
$todayword = $wordrow['today'];
$totalword = $wordrow['total'];
$newtoday = $todayword+1;
$newtotal = $totalword+1;
$updateword = "UPDATE `searchedwords` SET `today`='$newtoday', `total`='$newtotal' WHERE `word`='$word[$num]'";
mysql_query($updateword);
$num++;
}
else{
$addone = 1;
$wordinsert = mysql_query("INSERT INTO searchedwords VALUES ('','$word[$num]','$addone','$addone')");
$num++;
}
}
$searchphrase = mysql_query("INSERT INTO search VALUES ('','$var','$date','yes','$ip')");
}
}
?>
<br><div id='headsearch'></div>
<div style='position: relative; float: left; left: 540px;'><?php if($error==""){echo $numrows." Results";} ?></div>
<div style="width: 220px; margin-left: auto; margin-right: auto; text-align: center;">
<form name="form" action="search.php" method="get">
<div style="float: left;"><input type="text" name="q" /></div>
<div style="float: right;"><input type="image" src="img/search.png" alt="Search" name="Submit" value="Search" /></div>
</form>
</div>
<?php
// display what the person searched for
echo "<center><div style='min-width: 210px; margin-left: auto; margin-right: auto; text-align: center;'>$search <span style='color: #6aa504; margin-left; auto; margin-right: auto;'>" . stripslashes($var) . "</span></div></center>";
?>
<?php
// begin to show results set
$count = 1 + $s ;
$greenboxleft = "greenboxleft";
$greenboxright = "greenboxright";
$grayboxleft = "grayboxleft";
$grayboxright = "grayboxright";
$colorvalue = 0;
echo "$break<table width='700px' align='center' style='border-collapse:separate;
border-spacing:0px;'><th style='background-color: #93DB70; border-bottom-style: solid; border-color: #6aa504;'>Artist</th><th style='background-color: #93DB70; border-bottom-style: solid; border-color: #6aa504;'>Title</th>";
if($error==""){
// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$artist = $row["artist"];
$title = $row["title"];
if($artist!="DELETE"){
if(($colorvalue%2)==0){
$styleleft = $greenboxleft;
$styleright = $greenboxright;
}
else{
$styleleft = $grayboxleft;
$styleright = $grayboxright;
}
echo "<tr>";
echo "<td align='center' width='350px' id='$styleleft'><div id='songsboxleft'><strong>". ucwords($row['artist']). "</strong></div></td>";
echo "<td align='center' width='350px' id='$styleright'><div id='songsboxright'><a target='_blank' name='downloadclick' href='download.php?sheet=".$row['url']."&artist=".$row['artist']."&title=".$row['title']."'>" .ucwords($row['title']). "</a></div></td>";
echo "</tr>";
$colorvalue++;
}
}
}
else{
echo $error;
}
echo "</table>";
?>
</div>
</div>
</div>
<!-- Content Bottom -->
<div id="contentbottom">
</div>
</body>
</html>
Full Code:
