multiple search terms+fulltext search

[kraze_101]

Hey everyone,
i have a Mysql database and Php setup on a linux server. all are updated.

WEll i have a form in which a user can enter three words.(not all at once) to perform the search on the database.

Forms and php coding shown below.

I have one index with companyname,city,state,keyword1,keyword2,keyword3,keyword4,keyword5

Well i can get the first box to search on the database and return results from that index tthat i have created. i would like for the search to return specific results. Like for example if box 1 has a business type and if box three has an area it will find businesses in that area pertaining to the search. Lets say we are looking for restaurants in texas it will return a list of restaurants in texas.

FORM:

    <form name="searchform" action="search.php" method="get" id="search_form">
   <p><input type="text" name="q" />Business Type</p>

    <p><input type="text" name="r" />BusinessName</p>

    <p><input type="text" name="s" />State/City</p>


    <p><input type="submit" name="Submit" value="Search" /></p>
   </form>

Now for the php code

<?php

  // Get the search variable from URL

  $var1 = @$_GET['q'] ;
  $trimmed1 = trim($var1); //trim whitespace from the stored variable
  $var2 = @$_GET['r'] ;
  $trimmed2 = trim($var2); //trim whitespace from the stored variable
  $var3 = @$_GET['s'] ;
  $trimmed3 = trim($var3); //trim whitespace from the stored variable


// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($trimmed1 == ""  && $trimmed2 == "" && $trimmed3 == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

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

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("dbname","host","passwd") or die("Unable to select LOG ON");; //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("dbname") or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "select *, 
              MATCH(companyname,city,province,keyword1,keyword2,keyword3,keyword4,keyword5)
              AGAINST ('+$trimmed1 +$trimmed2 +$trimmed3') AS score FROM db_contacts
              WHERE MATCH(companyname,city,province,keyword1,keyword2,keyword3,keyword4,keyword5)
              AGAINST ('+$trimmed1 +$trimmed2 +$trimmed3') ORDER BY score DESC";



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

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; 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
  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: &quot;" . $var . "&quot;</p>";

// begin to show results set
echo "Results";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {


  $title = $row["companyname"];
  $title1 = $row["address"];     
  $title2 = $row["city"];
  $title3 = $row["province"];
  $title4 = $row["pcode"];
  $title5 = $row["contact"];
  $title6 = $row["phone"];
  $title7 = $row["fax"];
  $title8 = $row["email"];
  $title9 = $row["website"];

      echo "$count.)&nbsp;" ;
    echo '<strong>COMPANY NAME: '.stripslashes(htmlspecialchars($title)).'</strong><br />';
      echo '<strong>ADDRESS:</strong> '.stripslashes(htmlspecialchars($title1)).'<br />';
     echo '<strong>CITY: </strong>'.stripslashes(htmlspecialchars($title2)).''; 
     echo '    '; 
      echo '<strong>STATE:</strong> '.stripslashes(htmlspecialchars($title3)).'<br />';
      echo '<strong>POSTAL CODE: </strong>'.stripslashes(htmlspecialchars($title4)).'<br />';
      echo '<strong>CONTACT: </strong>'.stripslashes(htmlspecialchars($title5)).'<br />';

      echo '<strong>PHONE NUMBER:</strong> '.stripslashes(htmlspecialchars($title6)).'';
      echo '    '; 
      echo '<strong>FAX NUMBER:</strong> '.stripslashes(htmlspecialchars($title7)).'<br />';
        echo '<strong>EMAIL: </strong>'.stripslashes(htmlspecialchars($title8)).'<br />';  
      echo '<strong>WEBSITE:</strong> '.stripslashes(htmlspecialchars($title9)).'<br />';
      echo '<hr size="1" />';

$count++ ;
  }

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

//break before paging
  echo "<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 "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
  Prev 10</a>&nbsp&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 "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</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 that i cannot get the search to work with two inputs i tried using three but it still wouldnt work. Do i need to create three indexes one for each search box?

Thank you very much in advance for your help..

Ok i think i will have to use two queries but i have to use an if statement to check if the variables are there. the first query will check for the company name alone with that search field and then the other query will match against the business type and the location. I really do not know how to do this but i am trying different methods that i know in the meantime i get a reply.