Jump to content

search issues

Smudly

By Smudly
in PHP Coding Help

 Share

Recommended Posts

Smudly Regular Member

Smudly

Posted
Posted

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>
<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:

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

Right now you have this:

 

$query = "select * from `sheets` where `active`='yes' AND (artist like \"%$trimmed%\" OR title like \"%$trimmed%\") ORDER BY `artist`";

 

Instead, try something like this:

 

$query = "select * from `sheets` where `active`='yes' AND (";
$words = explode(' ', $trimmed);
$words = array_map('trim', $words);
$sep = '';
foreach ($words as $w) {
  $query .= $sep . "artist like \"%$w%\" OR title like \"%$w%\"";
  $sep = ' OR ';
}
$query .= ") ORDER BY `artist`";

 

The important part there is the loop to build the query by adding "OR" conditions for each word in the query string, instead of just one pair of conditions for the entire string.

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

Did you add brackets when using "AND"?  If not it may not act how you expect.  If you want the results you mentioned in the first post, ie all results with either "super" or "mario", then you should use "OR".  If you want all results with "super" and "mario" then you should use AND but also add brackets around each word.

Link to comment
Share on other sites
Smudly Regular Member

Smudly

Posted
  • Author
Posted

I guess I was a bit confused as to how I wanted it. The problem with using OR, was I was getting about 832 results when I was only looking for one. After I changed:

$sep = "OR"

to ...

$sep = "AND"

 

I got about 30 results. What will adding brackets do? :) If you want to test the search to see if it is working correctly you can check it out here:

www.sheetmusichaven.com

 

thanks

Link to comment
Share on other sites
Smudly Regular Member

Smudly

Posted
  • Author
Posted

After further testing, I have run into a few obstacles.

 

When typing in any of the following characters:

 

/*()+?[

 

I receive this error:

 

Warning: preg_match() [function.preg-match]: Unknown modifier '/' in /home/content/46/6746946/html/search.php on line 149

 

Also, if I type in _

 

all 4233 results are posted.

 

I've attempted to include some if statements in the code I'm using, but it seems to cause problems. Here is how my current query looks.

 

$words = explode(' ', $trimmed);
$words = array_map('trim', $words);
$sep = '';
    
$query = "select * from `sheets` where `active`='yes' AND (";

foreach ($words as $w) {
    if(!$var==""){

    
    if (preg_match("/$w/i", "abcdefghijklmnopqrstuvwxyz")) {
        $skip = 1;
    }
    }
    
    if($skip==0){
  $query .= $sep . "artist like \"%$w%\" OR title like \"%$w%\"";
  $sep = ' AND ';
  }
  else{
// For now, I just set $blank equal to "garblygook28472"
  $query .= $sep . "artist like \"%$blank%\" OR title like \"%$blank%\"";
  $sep = ' AND ';
  $skip = 0;
}
}
$query .= ") ORDER BY `artist`";

 

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

Do you want to be able to search for those characters?  If not, it's probably easier to remove them and just allow letters and perhaps numbers through.  preg_match() has a lot of special characters.

 

The reason "_" gives you everything is that "_" means "any character" for the mysql like operator.  You'll see the same if you searched for "%" which means "any number of any character".

 

What i mean with brackets is changing this

 

  $query .= $sep . "artist like \"%$w%\" OR title like \"%$w%\"";
  $sep = ' AND ';

 

to this

 

  $query .= $sep . "(artist like \"%$w%\" OR title like \"%$w%\")";
  $sep = ' AND ';

 

That makes it clear to mysql and to people reading your code that you want each word matched at least once in either artist or title, and not any other interpretation, such as 'artist like "foo" OR (title like "foo" AND artist like "mario") OR title like "mario"'

 

I don't know if it actually will affect behaviour of the query, but it's generally a good idea.

Link to comment
Share on other sites
Smudly Regular Member

Smudly

Posted
  • Author
Posted

Hey :) Yeah, I would like it to be able to search for those characters.

I tried doing another pregmatch, to check if the search the user typed included these characters, but I didn't do it right.

Now that you explain what's happening with "_" and %, that make a bit more sense.

 

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

Hang on, what exactly is your preg_match() supposed to do?  I don't understand it.  If you can tell me what it's supposed to do then I can give you some code that does it.

 

Also this might not be doing what you expect:

 

if (!$var=="")

 

That's saying:

 

If (not $var) is equal to ""

 

Not

 

If $var is not equal to ""

 

which would be:

 

if ($var != "")

 

So it's taking the boolean inverse of $var, and comparing it to the empty string

Link to comment
Share on other sites
Smudly Regular Member

Smudly

Posted
  • Author
Posted

My preg_match is checking if the user types in single letter words in a search phrase. For example, I have a sheet on my site called "A Whole New World" from Aladdin.

 

If the user types in "A Whole New World", the results that are shown are every artist & title that have "A" in them.

 

And thanks for tip. Yeah I typed it wrong :)

 

Thanks

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

Ah, in that case you could do something like this:

 

if (preg_match('|^[a-z]$|i', $w)

 

which means "One character from a-z which is both at the beginning (^) and the end ($) of the string", another way of saying "a single alphabetical character".

 

or

 

if (strlen($w) == 1 && ctype_alpha($w))

 

It's much easier if you compare a fixed regexp against the word, rather than using the word as a regexp against a fixed string.  Then you don't need to check for special characters in the input.

 

Plus, your original regexp would match things like "ab" or "nop"

Link to comment
Share on other sites
Smudly Regular Member

Smudly

Posted
  • Author
Posted

Cool thanks for the examples. I implemented the bottom into my code and it all works great.

This also fixed the problem with special characters.

 

For a last feature, I'm trying to determine which artist/title (combined) have the most matching words with what the user typed in to search for. Then this sheet (artist/title) will be highlighted.

 

Is there anyway to determine something like this or am I left to do preg_match?

 

 

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

I don't know how to do that in sql.  But you could do this:

 

1.  Read all the results into an array

2.  Go through the array counting how many times each term appears on artist and title, and add up the results, then store that back into that array entry.  You could do this by breaking artist and title into words and checking each word.

3.  Sort the array by number of matches descending.  This could be done using usort(), which is very powerful but can take some time to learn how to use..

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.