0

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:

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

$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

<?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>";  
?>
2
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by hielo
0

try:

<?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"; 
//#LIKE_PART# is just a temporary place holder
//IMPORTANT: Since you are doing 'AND' BEFORE the #LIKE_PART#, you MUST have parentheses around #LIKE_PART#
$query = "select * from `sheets` where `active`='yes' AND (#LIKE_PART#) ORDER BY `artist`";

//here you call function to build LIKE expression per field
$q[]=buildLike($trimmed,'`artist`');
$q[]=buildLike($trimmed,'`title`');

//here you join the LIKE expressions above with 'OR'. Ultimately $q ends up with:
// ( (`artist` like 'term1') OR (`artist` like 'term2') OR (`artist` like 'termN') )
// OR
// ( (`title` like 'term1') OR (`title` like 'term2') OR (`title` like 'termN') )
$q=implode('OR', $q);
//echo $q;

//here you replace that #LIKE_PART# with the actual LIKE expression
$query = str_replace('#LIKE_PART#', $q, $query);
//echo $query;

 $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>"; 
	
function buildLike($termList,$field)
{
	$t='';
	
	if( preg_match_all('(\S+)',$termList,$result) )
	{
		$termList=$result[0];
		$t.=' (';
		//$t.=sprintf("(%s LIKE '%s')", $field, '%'.implode(' ',$termList) . '%');$t.=' OR ';
		$t.='('.$field .' LIKE \'%'. implode('%\') OR ('.$field.' LIKE \'%', $termList).'%\')';
		$t.=') ';
	}
return $t;
}
?>
0

Hey, i tried your code and edited the areas you specified. I'm not getting any errors, however, the results from the search are still the same.
I have a row that has the value Moulin Rouge, and another row that has the value Rogue.
If I type "Moulin Rouge" in the search field though, all that displays is Moulin Rouge. It's only when I type "Rouge", that both of these rows display.

0

..i tried your code and edited the areas you specified

I'm not clear on what you "edited". I gave you a copy and paste post. I was expecting you to backup your original first, then copy and paste what I posted.

I have a row that has the value Moulin Rouge, and another row that has the value Rogue.

Well, Rouge!=Rogue

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.