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 Code:
<?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>
<meta name="description" content="Free Piano Sheet Music - Sheet Music Haven">
<meta name="keywords" content="free,piano,sheet,music,download,keyboard,haven,lyrics,notes,chords,score,top,modern,popular,jazz,classical,sheetmusichaven">
<meta name="author" content="Sheet Music Haven - Free Piano Sheet Music. Download all types of piano sheet music for free. Popular sheets are added often">
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title>Search - Sheet Music Haven</title>
<link rel="stylesheet" type="text/css" href="styles/style.css">
<script type="text/javascript">
function make_blank()
{
if(document.login.username.value =="Username"){
document.login.username.value ="";
document.login.username.style.color ="#000000";
}
}
function make_blank1()
{
if(document.login.password.value =="Password"){
document.login.password.value ="";
document.login.password.type ="password";
document.login.password.style.color ="#000000";
}
}
function undoBlank() {
if(document.login.username.value == ""){
document.login.username.value ="Username";
document.login.username.style.color="#ccc";
}
}
function undoBlankpass() {
if(document.login.password.value == ""){
document.login.password.value ="Username";
document.login.password.style.color="#dddddd";
}
}
</script>
</head>
<body bgcolor="#343331" OnLoad="document.form.q.focus();">
<?php include('inc/reporterrors.php'); ?>
<!-- Header -->
<div id="header">
<div id="headerleft">
<div style="position: relative; top: 30px; width: 165px; margin-left: auto; margin-right: auto; text-align: center;">
<form name="form1" action="search.php" method="get">
<div style="float: left;" class="searchboxdiv"><input type="text" name="q" class="searchbox" /></div>
<div style="float: right;"><input type="image" src="img/search.png" alt="Search" name="Submit" value="Search" /></div>
</form>
</div>
</div>
<div id="headermiddle"><a href="index.php"><img src="img/logo.png"></a></div>
<div id="headerright">
<?php echo "<form name='login' action='inc/$loginstatus.php' method='POST'>";?>
<div class="loginboxdiv" id="username">
<input type="text" class="loginbox" name="username" value="Username" onFocus="make_blank();" onBlur="undoBlank();">
</div>
<div class="loginboxdiv" id="password">
<input class="loginbox" type="text" name="password" type="text" value="Password" onFocus="make_blank1();" onBlur="undoBlankpass();">
</div>
<div id="login">
<?php echo "<input type='image' src='img/$loginstatus.png' alt='".ucfirst($loginstatus)."'>";?>
</div>
</form>
<div id="register">
<a href="register.php"><img src="img/register.png"></a>
</div>
<div id="forgotpassword">
<a href="resetpassword.php" class="forgot">Forgot Password?</a>
</div>
</div>
</div>
<!-- Content Top -->
<div id="contenttop">
<div id="links">
<table cols="7">
<tr>
<td align="center" valign="middle" width="100px" height="48px"><a href="index.php"><img src="img/home.png"></a></td>
<td align="center" valign="middle" width="100px" height="48px"><a href="member.php"><img src="img/member.png"></a></td>
<td align="center" valign="middle" width="100px" height="48px"><a href="addsheet.php"><img src="img/addsheet.png"></a></td>
<td align="center" valign="middle" width="100px" height="48px"><a href="advertise.php"><img src="img/advertise1.png"></a></td>
<td align="center" valign="middle" width="100px" height="48px"><a href="faq.php"><img src="img/faq.png"></a></td>
<td align="center" valign="middle" width="100px" height="48px"><a href="terms.php"><img src="img/terms.png"></a></td>
<td align="center" valign="middle" width="100px" height="48px"><a href="contact.php"><img src="img/contact.png"></a></td>
</tr>
</table>
<!-- 92x30 -->
</div>
</div>
<!-- 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:
