PHP/MySQL search engine script

Here is a quick tutorial/script to show you how to create PHP/MySQL search engine with search keywords displayed in bold.

• trim() -  Strip whitespace (or other characters) from the beginning and end of a string
• explode() -  Split a string by string
• array_unique() -   Removes duplicate values from an array
• preg_replace() -  Perform a regular expression search and replace

Save the file as search.php and upload it to your server.

See the commented sections for details. 

< ?php
$hostname_logon = "localhost" ;   
$database_logon = "databaseName" ;  
$username_logon = "databaseUser" ;  
$password_logon = "databasePass" ;   
//open database connection
 $connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( "Unabale to connect to the database" );
 //select database
 mysql_select_db($database_logon) or die ( "Unable to select database!" );

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

// Get the search variable from URL
  $var = @$_GET['q'] ;
  $s = $_GET['s'] ;
//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 =  "
Search Error
Please enter a search...
" ;
  }

// check for a search parameter
if (!isset($var)){
  $resultmsg =  "
Search Error
We don't seem to have a search parameter! 
" ;
  }
// 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{
          $adid_array[] = $row[ 'fieldid' ];
      }while( $row= mysql_fetch_array($numresults));
 } //end foreach

if($row_num_links_main == 0 && $row_set_num == 0){
   $resultmsg = "
Search results for: ". $trimmed."
Sorry, your search returned zero results
" ;
}
   //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
? >


  

      
      
  


< ?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.
//Replace field
  $titlehigh = preg_replace ( "'($var)'si" , " \1" , $row_linkcat[ 'field1' ] );
  $linkhigh = preg_replace ( "'($var)'si" , " \1" , $row_linkcat[ 'field2' ] );
  $linkdesc = preg_replace ( "'($var)'si" , " \1" , $row_linkcat[ 'field3' ] );

foreach($trimmed_array as $trimm){
    if($trimm != 'b' ){
        $titlehigh = preg_replace( "'($trimm)'si" ,  " \1" , $titlehigh);
        $linkhigh = preg_replace( "'($trimm)'si" , " \1" , $linkhigh);
        $linkdesc = preg_replace( "'($trimm)'si" ,  " \1" , $linkdesc); 
     }
//end highlight

? >
 

< ?php echo $titlehigh; ? >

< ?php echo $linkhigh; ? >

< ?php echo $linkdesc; ? >

 
< ?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 "
< a href="$PHP_SELF?s=$prevs&q=$var&catid=$catid" >Previous " .$limit. "< /a >
";
      }
     // 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 "
< a href="$PHP_SELF?s=$n&q=$var&catid=$catid">Next " .$limit. "< /a >
";
        }
    }
}  //end foreach $newarr
? >

Here are some helpfull resources:

• PHP
• MySQL

Do you like this or find it useful? Drop me a note or treat me to a double-espresso from my favorite coffee shop.