Advanced Search Script.

[Mouse]

Advanced Search Script.
I know nothing about this… HELP.

They say the difference between a wise man and a fool is that the wise man knows what he does not know… and while I am not omnipotent, I do know that search scripts aren’t my thing.

Below is a simple search script I have used on my fledgling site, and it seams to work… I admit I didn’t write this myself I found it orphaned on line, without credit being given to its author.

Could someone more experienced than I give me a hand in making it into an advanced search? The following variables are taken from advanced search form…

$name = “name”; //search in DB field ‘first_name’
$fname = “fname”; //search in DB field ‘family_name’

$resort = “location”; // six possible from drop down list
$job = “job”; // twenty six possible from drop down list
$locationjob = $location.$job; //search in DB field ‘where’

I assume that most of the editing needs to be done in this area…

[code]
$query = "SELECT * FROM tablename WHERE field1 LIKE \"%$trimm%\" OR field2 LIKE  \"%$trimm%\" OR field3 LIKE \"%$trimm%\" ORDER BY field1   DESC";
[/code]

The full script is below…

[code]
<?php

include = ‘db.php’;

//specify how many results to display per page
$limit = 10;

// Get the search variable from URL
  $var = @$_GET['q'];
//trim whitespace from the stored variable
  $trimmed = trim($var);  
//separate key-phrases into keywords
  $trimmed_array = explode(" ",$trimmed);

// check for an empty string and display a message.
if ($trimmed == "") {
  $resultmsg =  "<p>Search Error</p><p>Please enter a search...</p>";
  }

// check for a search parameter
if (!isset($var)){
  $resultmsg =  "<p>Search Error</p><p>We don't seem to have a search parameter! </p>";
  }
// Build SQL Query for each keyword entered
foreach ($trimmed_array as $trimm){
      
// EDIT HERE and specify your table and field names for the SQL query
     $query = "SELECT * FROM tablename WHERE field1 LIKE \"%$trimm%\" OR field2 LIKE  \"%$trimm%\" OR field3 LIKE \"%$trimm%\" ORDER BY field1   DESC";
     // Execute the query to  get number of rows that contain search kewords
     $numresults=mysql_query ($query);
     $row_num_links_main =mysql_num_rows ($numresults);

     // next determine if 's' has been passed to script, if not use 0.
     // 's' is a variable that gets set as we navigate the search result pages.
     if (empty($s)) {
         $s=0;
     }

      // now let's get results.
      $query .= " LIMIT $s,$limit";
      $numresults = mysql_query ($query) or die ( "Couldn't execute query" );
      $row= mysql_fetch_array ($numresults);

      //store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.
      do{
//EDIT HERE and specify your field name that is primary key
          $adid_array[] = $row[ 'fieldid' ];
      }while( $row= mysql_fetch_array($numresults));
} //end foreach

if($row_num_links_main == 0 && $row_set_num == 0){
   $resultmsg = "<p>Search results for:" . $trimmed  ."</p><p>Sorry, your search returned zero results</p>";
}
   //delete duplicate record id's from the array. To do this we will use array_unique function
   $tmparr = array_unique($adid_array);
   $i=0;
   foreach ($tmparr as $v) {
       $newarr[$i] = $v;
       $i++;
   }

// now you can display the results returned. But first we will display the search form on the top of the page
?>

<form action="search.php" method="get" name="search">
  <div align="center">
      <input name="q" type="text" value=" <?php echo $q; ?> " size="15">
      <input name="search" type="submit" value="Search">
  </div>
</form>

<?php
// display what the person searched for.
if( isset ($resultmsg)){
  echo $resultmsg;
  exit();
}else{
  echo "Search results for: " . $var;
}

foreach($newarr as $value){

// EDIT HERE and specify your table and field names for the SQL query
$query_value = "SELECT * FROM tablename WHERE fieldid = '$value'";
$num_value=mysql_query ($query_value);
$row_linkcat= mysql_fetch_array ($num_value);
$row_num_links= mysql_num_rows ($num_value);

//now let's make the keywods bold. To do that we will use preg_replace function.
//EDIT parts of the lines below that have fields names like $row_linkcat[ 'field1' ]
//This script assumes you are searching only 3 fields. If you are searching more fileds make sure that add appropriate line.
  $titlehigh = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'field1' ] );
  $linkhigh = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'field2' ] );
  $linkdesc = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'field3' ] );

foreach($trimmed_array as $trimm){
    if($trimm != 'b' ){
//IF you added more fields to search make sure to add them below as well.
        $titlehigh = preg_replace( "'($trimm)'si" ,  "<b>\\1</b>" , $titlehigh);
        $linkhigh = preg_replace( "'($trimm)'si" , "<b>\\1</b>" , $linkhigh);
        $linkdesc = preg_replace( "'($trimm)'si" ,  "<b>\\1</b>" , $linkdesc);
     }
//end highlight

?>
<p>
<?php echo $titlehigh; ?><br>
<?php echo $linkhigh; ?><br>
<?php echo $linkhigh; ?>
</p>

<?php
}   //end foreach $trimmed_array
   if($row_num_links_main > $limit){
   // next we need to do the links to other search result pages
      if ($s>=1) { // do not display previous link if 's' is '0'
        $prevs=($s-$limit);
         echo "<div align='left'><a href='$PHP_SELF?s=$prevs&q=$var&catid=$catid'>Previous " .$limit. "</a></div>";
      }
     // check to see if last page
     $slimit =$s+$limit;
       if (!($slimit >= $row_num_links_main) && $row_num_links_main!=1) {
     // not last page so display next link
          $n=$s+$limit;
           echo "<div align='right'><a href='$PHP_SELF?s=$n&q=$var&catid=$catid'>Next " .$limit. "</a></div>";
        }
    }
}  //end foreach $newarr
?>
[/code]

any and all hints, tips and or advice would be most welcome…

Mouse

[k.soule]

What are you trying to accomplish, I guess is the first question that needs to be answered. Yes, an advanced search...but that is an interesting term because any search is necessarily advanced :) Do you just want to search for multiple things in a specified field? Say, do you want your user to be able to say I want to search for Jon in the name field or do you it to search in a BUNCH of fields with one parameter?

I will do my best to set you on your way with little knowledge of the inquiry at hand --

First, most database searches will trim whitespace off of the input, this is standard for the next step...verify that the user entered something. So, we will use $name throughout this 'guide', $name = trim($name); then

if (!$name) {
die("You did not enter a search term");
}

because trim would have trimmed off a single space and left it blank, this simple test will verify that a term was entered or else it will murder the script with an error. Next, you are going to want to escape any user input that you are going to be searching for, you can get nasty MySQL injection hacks if you aren't careful to properly monitor all user input...so, get_magic_quotes_gpc() is a function that checks to see if MySQL escaping is automagically enabled, we proceed like so:

if (!get_magic_quotes_gpc()) {
$name = addslashes($name);
}

and there we now have a fairly safe term to search for! That is where the code you thought most of the work will be comes into play...

$query = "SELECT * FROM tablename WHERE field1 LIKE \"%$trimm%\" OR field2 LIKE \"%$trimm%\" OR field3 LIKE \"%$trimm%\" ORDER BY field1 DESC";

That is just horrid, you could set it up more attractively with something like this:

$query = "SELECT * FROM tablename WHERE name like '%$name%'";

that will search for name in the name table...now, if you want to search for the input $name (or general input) in a BUNCH of tables, like shown above:

$query = "SELECT * FROM tablename WHERE name LIKE '%$name%' OR keyword LIKE '%$name%' OR city LIKE '%$name%'";

'%...%' is just a fancy way of saying similar to instead of name=$name, more syntax to remember, in a way.

Now, if you want to search for multiple terms, you can easily figure that out. You could also explode the input string on spaces and iterate through the search for each element if you wanted to search for all terms, like they've done with the foreach loop, basically, it is taking the exploded array and assigning the variable $trimm to each value in the array and searching for it in the fields (field1, field2, etc.).

If you want to search by relevance, I think that is beyond the scope of a help forum :)

HTH

[Mouse]

[!--quoteo(post=353491:date=Mar 10 2006, 02:27 AM:name=Kyle Soule)--][div class=\'quotetop\']QUOTE(Kyle Soule @ Mar 10 2006, 02:27 AM) [snapback]353491[/snapback][/div][div class=\'quotemain\'][!--quotec--]
What are you trying to accomplish, I guess is the first question that needs to be answered. Yes, an advanced search...but that is an interesting term because any search is necessarily advanced :) Do you just want to search for multiple things in a specified field? Say, do you want your user to be able to say I want to search for Jon in the name field or do you it to search in a BUNCH of fields with one parameter?

[/quote]
Kyle…. Thanks!!!

In answer to your question(s).

My site is a reunion site with a potentially very large database so I feel a ‘simple’ word search may be counter productive. Imagine searching for “Chris” in a database where you have Christine’s, Christopher’s, Christian’s, some of whom may mention that they are Christians…

So I intend to provide six search fields:
1. first name – free input ($firstname field)
2. surname – free input ($familyname field)
3. nationality – drop down box, taken from sign up form (256 options)
4. keyword search – free input ($lifestory field)
5. work location – drop down box (six options)
6. department- drop down box (26 options)

(5 & 6 are the interesting ones because they have to be merged to make one field… i.e. if the selection for 5. was London and the selcction for 6. was butcher then the field to look in would be $londonbutcher … 156 fields…)

hope that answers your questions…

Mouse