[SOLVED] PhP MySql Search problem

[StefanRSA]

For the first time I am trying to build a search section for a site.

I read up on my biggest friend "google" and found a sollution that worked... Somehow.... Sometimes...

 

My Script:

<form name="form" action="test.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>

<?php
$sfield = trim($_POST['searchfield']);
$catkey = trim($_POST['catid']);
//check if $sfield is was posted or not
if ($sfield != '') {
$var = $sfield;
}
else {
$var = @$_GET['q'];
}
$trimmed = trim($var); //trim whitespace from the stored variable
//echo $sfield;
// rows to return
$limit=5; 

// check for an empty string and display a message. 
//THIS DOES NOT WORK!!!??? Have to FIX it!!!???
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

// Build SQL Query  
//Check if $catkey was selected and posted
if ($catkey !== ''){
$csearch = "catid = '$catkey' AND MATCH";
}
else {
$csearch = 'MATCH';
}
/////
$query = ("SELECT text, header FROM ads WHERE $csearch (text, header) AGAINST ('%$trimmed%')  
  ORDER BY addate") or die(mysql_error()); // EDIT HERE and specify your table and field names for the SQL query

$numresults=(mysql_query($query)) or die(mysql_error());
$numrows=(mysql_num_rows($numresults)) or die(mysql_error());

// If we have no results, offer a google search as an alternative
echo 'Number Rows - '.$numrows;
if ($numrows == '0')
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";

// google
echo "<p><a href=\"http://www.google.com/search?q=" 
  . $trimmed . "\" target=\"_blank\" title=\"Look up 
  " . $trimmed . " on Google\">Click here</a> to try the 
  search on google</p>";
  }

// next determine if s has been passed to script, if not use 0
$s = $_GET['s'];
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";

// begin to show results set
echo "<P><br><b>Results</b><br>";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row["header"];
  

  echo "Howzit! - $count.) $title<br>" ;
  $count++ ;
  }

$currPage = (($s/$limit) + 1);

//break before paging
  echo "<p><br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&nbsp ";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
  
?>

 

My problem is:

One entry has got the word "farm" in the "text" column in the table and if I search for it it display the result... It even display the result if I enter "farm animals" and the word "farm" is in the header column and the word "animals" in the text column....

 

If two or more entries has got the word "investment" in it... Nothing displays????

Does the Full Text indexing work only after a certain time and after MySql has indexed the fields or am I doing things wrong?

 

If this is not a good way of doing things... What will be a better way in achieving what I need?

[StefanRSA]

I now have changed

$query = ("SELECT text, header FROM ads WHERE $csearch (text, header) AGAINST ('%$trimmed%')  
  ORDER BY addate") or die(mysql_error());

To

$query = ("SELECT text, header FROM ads WHERE $csearch (text, header) AGAINST ('%$trimmed%' IN BOOLEAN MODE)  
  ORDER BY addate") or die(mysql_error());

 

I added "IN BOOLEAN MODE" and now it works???

Can anybody please explain??? And is this a good way of doing a search on my site?

[aggrav8d]

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

This may answer your question.