search engine problem

[skoff]

Hi! First you need to know that my table contains video games. Everything is working fine with this search engine! the only thing is that if I search for example for a game called burnout duty it echo me all burnout games plus all call of duty games.. but this isnt true because there is no game called burnout duty... it should return nothing. How can I modify this code to correct this error?

Thanks, and here's the code!

<?php


//get data
$button = $_GET['submit'];
$search = $_GET['search'];


$s = $_GET['s'];
if (!$s)
$s = 0;


$e = 10; // Just change to how many results you want per page


$next = $s + $e;
$prev = $s - $e;




 if (strlen($search)<=2)
 echo "Must be greater then 3 chars";
 else
 {
 echo "<br /><table><tr><td><img src='juzzy.jpg' /></td><td><form action='search.php' method='GET'><input type='text' onclick=value='' size='50' name='search' value='$search'> <input type='submit' name='submit' value='Search'></form></td></tr></table>";
 
 //connect to database
 mysql_connect("localhost","#*$!x","#*$!x");
 mysql_select_db("search");
 
 //explode out search term
 $search_exploded = explode(" ",$search);
 
 foreach($search_exploded as $search_each)
 {
 
  //construct query
 $x++;
 if ($x==1)
  $construct .= "keywords LIKE '%$search_each%'";
 else
  $construct .= " OR keywords LIKE '%$search_each%'";
 
 }
 
 //echo outconstruct
 $constructx = "SELECT * FROM searchengine WHERE $construct";
 
 $construct = "SELECT * FROM searchengine WHERE $construct LIMIT $s,$e";
 $run = mysql_query($constructx);
 
 $foundnum = mysql_num_rows($run);


 $run_two = mysql_query("$construct");
 
 if ($foundnum==0)
 echo "No results found for <b>$search</b>";
 else
 {
 echo "<table bgcolor='#0000FF' width='100%' height='1px'><br /></table><table bgcolor='#f0f7f9' width='100%' height='10px'><tr><td><div align='right'>Showing 1-10 of <b>$foundnum</b> results found for <b>$search.</b></div></td></tr></table><p>";
 
 while ($runrows = mysql_fetch_assoc($run_two))
 {
 //get data
 $title = $runrows['title'];
 $desc = $runrows['description'];
 $url = $runrows['url'];
 
 echo "<table width='300px'>
 <h4><a href='http://$url'><b>$title</b></a><br />
 $desc<br>
 <font color='00CC00'>$url</font></table></h4>
 ";
 }
?>

<table width='100%'>
<tr>
<td>
<div align="center">

<?php
if (!$s<=0)
 echo "<a href='search.php?search=$search&s=$prev'>Prev</a>";

$i =1; 
for ($x=0;$x<$foundnum;$x=$x+$e)
{


 echo " <a href='search.php?search=$search&s=$x'>$i</a> ";


$i++;


}

if ($s<$foundnum-$e)
 echo "<a href='search.php?search=$search&s=$next'>Next</a>";

}
} 


?>
</div>
</td>
</tr>
</table>

[milocold]

I think exploding the search terms and searching against each one is causing the issue you're having.

//explode out search term
$search_exploded = explode(" ",$search);

So it's grabbing all DB results with 'burnout' or 'duty' (*snicker*) in it.

Hope that helps!

M.Cold

[skoff]

Thanks a lot but do you ahve any idea how could I fix this? :P

[milocold]

Well, you can opt to query the DB on the entire phrase.

Or, you could explode the search phrase and store the resulting game titles in an array and search the array for the title "Burnout Duty". That will provide you with a "Not found" result and a suggestion list: "Did you mean 'Burnout SpaceChimps', 'Burnout City', or 'Call of Duty'".

M. Cold

[rocknbil]

Correct . . the like operator is used a bit liberally here. :-) Additionally you will discover a problem with this:

$next = $s + $e;
$prev = $s - $e;

If it's the first set of records there is no $prev (-1) and there will be issues with the last record set. But first things first.

The best way to manage this is a set of radios at the front end:

<input type="radio" name="searchtype" id="searchtype-exact" value="exact" selected> <label for="searchtype-exact">Exact</label>
<input type="radio" name="searchtype" id="searchtype-all" value="all"> <label for="searchtype-all">All Words</label>
<input type="radio" name="searchtype" id="searchtype-any" value="any"> <label for="searchtype-any">Any of these words</label>


<input type="radio" name="phrase-location" id="phrase-location-start" value="start" selected> <label for="phrase-location-start">Starts with</label>
<input type="radio" name="phrase-location" id="phrase-location-end" value="end"> <label for="phrase-location-end">Ends with</label>
<input type="radio" name="phrase-location" id="phrase-location-any" value="any"> <label for="phrase-location-any">Anywhere</label>

Then when submitted, use those to build your $where.

// A little error checking never hurt anyone . . .
$required = array('exact','all','any','start','end');

if ((! isset($_POST['searchtype']) or (! isset($_POST['phrase-location']) or (! isset($_POST['search']))) {
die ("required parameters not set.");
}

if ((! in_array($_POST['searchtype'],$required)) or (! in_array($_POST['phrase-location'],$required))) {
die("required parameters missing.");
}

// This is still dirty input - see note below
$search = preg_replace('/[^a-z\d\s]/i','',$_POST['search']);

$where=null; // squelch concatenation errors

if (($_POST['searchtype']=='any') or ($_POST['searchtype']=='all')) { 
 $and_or = ($_POST['searchtype']=='any')?'or':'and'; 
 $search_exploded = explode(" ",$search); 
 foreach($search_exploded as $search_each) { 
 // No counter needed - if there's a where, add an OR 
 // or an AND, depending . . .  
 if ($where) { $where .= " $and_or "; } 
 $where .= ' keyword like \''; 
 if (($_POST['phrase-location']=='start') or ($_POST['phrase-location']=='any')) { 
 $where .= '%'; 
 } 
 $where .= $search_each; 
 if (($_POST['phrase-location']=='end') or ($_POST['phrase-location']=='any')) { 
 $where .= '%'; 
 } 
 $where .= '\' '; 
 } 
 // Surround it in () in case there are subsequent and's 
 // Or's can be tricky in mysql 
 if ($where) { $where = "($where)"; } 
} 
// Otherwise it's an exact phrase. 
else { 
 $where = ' keyword like \''; 
 if (($_POST['phrase-location']=='start') or ($_POST['phrase-location']=='any')) { 
 $where .= '%'; 
 } 
 $where .= $search; 
 if (($_POST['phrase-location']=='end') or ($_POST['phrase-location']=='any')) { 
 $where .= '%'; 
 } 
 $where .= '\' '; 
} 

Then

$query = "select * from table";
if ($where) " $query .= " where $where";
$query .= "$order $limit";
// you WILL need this. :-)
// echo "$query <br>\n";

NOT working code . . . but note the logic. You also have some work to do in respect to cleansing the variables, but this will get a handle on wildly unexpected search results.