Page 1 of 1

search engine problems

Posted: Thu Sep 23, 2010 12:40 am
by Smudly
Hi, I'm having two issues with my Search engine for my website. It is created to search through my database (it doesn't crawl through pages). The first problem I'm having is when I type in for example:
Super Mario Bros
and there is a row in my table with a field called Super Mario Brothers
this row is not included as a successful result to the search. I need to somehow modify my code to search every word in every table cell in the database.

So another example. I have 5 rows, each with one cell, named as follows:
One
Two
Three
Four
Five
If I was to type in the search box:
One Two Three Four Five
it should display all rows (it obviously doesn't do that right now lol)

The second issue has to do with my sql query.
It looks like this:

Code: Select all

$query = "select * from sheets where artist like \"%$trimmed%\" OR title like \"%$trimmed%\" 
  order by artist"; 
 $numresults=mysql_query($query);
I need this query to search in the columns artist and title (like it is doing above) AS WELL as search only those rows that have an active status set to 'yes'.
I tried to type something like:

Code: Select all

$query = "select * from sheets where active='yes' && artist like \"%$trimmed%\" OR title like \"%$trimmed%\" 
  order by artist"; 
but this obviously causes problems. How do i require the query to include rows that are active, but have it look through artist OR title as well?

Here is my code below. Any insight appreciated. Will be working on it til someone is available. Thanks

Code: Select all

<?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; 
// 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 artist like \"%$trimmed%\" OR title like \"%$trimmed%\" 
  order by artist"; 

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

// If we have no results, DISPLAY ERROR LETTING USER KNOW THE SHEET WAS NOT FOUND. INCLUDE LINK TO ALLOW THEM TO REQUEST SHEET

if ($numrows == 0)
  {
// 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, however, 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: #f5f5f5;'><a href='request.php'>Request A Sheet Here</a></td></tr>";
  }

// 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 = "&nbsp;";
  $break = "<br />";
if($var!=""){
	$search = "Search:";
	$break = "";
}
?>
<br /><div id='headsearch'></div>
<div style="width: 210px; 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='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(($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>";  
?>

Re: search engine problems

Posted: Thu Sep 23, 2010 3:06 am
by requinix
Use MATCH AGAINST inside of LIKE. You'll need a FULLTEXT index on whatever columns you're matching against.

Code: Select all

SELECT ... WHERE MATCH(field) AGAINST ("Super Mario Bros") ...