I have a php code for searching words in mysql database using fulltext, it splits the phrases into words and shows all the possible result containing the words in the phrase.The paging is doing good but my problem is if I enter a phrase enclose in double quote (“"”) characters literally, the result also shows all the possible result containing the words in the phrase and the paging is malfunctioning. When I click the next button, it shows an alert "Search Error. Please enter a search keyword...".
What I want is if I enter a phrase enclose in double quote (“"”) characters, the result should be the exact phrase only and the paging should work too.Help please!.
php CODE: (search.php)
Expand|Select|Wrap|Line Numbers
- <?php
- $hostname_logon = "localhost" ;
- $database_logon = "XXXX" ;
- $username_logon = "root" ;
- $password_logon = "" ;
- //open database connection
- $connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( "Unable 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 = 5;
- //get the search variable from URL
- $var = stripslashes($_REQUEST['q']);
- //get pagination
- $s = mysql_real_escape_string($_REQUEST['s']);
- //set keyword character limit
- if(strlen($var) < 3){
- $resultmsg = "<p>Search Error</p><p>Keywords with less then three characters are considered invalid...Try again.</p>" ;
- }
- //trim whitespace from the stored variable
- $trimmed = trim($var);
- $trimmed1 = trim($var);
- //separate key-phrases into keywords
- $trimmed_array = explode(" ",$trimmed);
- $trimmed_array1 = explode(" ",$trimmed1);
- // check for an empty string and display a message.
- if ($trimmed == "") {
- $resultmsg = "<p>Search Error</p><p>Please enter a search keyword...</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
- // MySQL "MATCH" is used for full-text searching. Please visit mysql for details.
- $query = "SELECT * , MATCH (title, author,yearpub,full_docs) AGAINST ('".$trimm."' IN BOOLEAN MODE) AS score FROM thesis WHERE MATCH (title, author,yearpub,full_docs) AGAINST ('+".$trimm."' IN BOOLEAN MODE) ORDER BY score 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);
- //If MATCH query doesn't return any results due to how it works do a search using LIKE
- if($row_num_links_main < 1){
- $query = "SELECT * FROM thesis WHERE title like '%$trimm%' or author like '%$trimm%' or description like '%$trimm%' or yearpub like '%$trimm%' or adviser like '%$trimm%' or keyword like '%$trimm%' ORDER BY id DESC";
- $numresults=mysql_query ($query);
- $row_num_links_main1 =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[ 'id' ];
- }while( $row= mysql_fetch_array($numresults));
- } //end foreach
- //Display a message if no results found
- if($row_num_links_main == 0 && $row_num_links_main1 == 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++;
- }
- //total result
- $row_num_links_main = $row_num_links_main + $row_num_links_main1;
- // display an error or, what the person searched
- if( isset ($resultmsg)){
- echo $resultmsg;
- }else{
- echo "</br>";
- echo "<p align = center><h1>Displaying search results for: <em>" . $var."</em></h1></p>";
- echo "<h4>Total number of result(s): $row_num_links_main</h4>";
- foreach($newarr as $value){
- // EDIT HERE and specify your table and field unique ID for the SQL query
- $query_value = "SELECT * FROM thesis WHERE id = '".$value."'";
- $num_value=mysql_query ($query_value);
- $row_linkcat= mysql_fetch_array ($num_value);
- $row_num_links= mysql_num_rows ($num_value);
- //create summary of the long text. For example if the field2 is your full text grab only first 165 characters of it for the result
- $introcontent = strip_tags($row_linkcat[ 'description']);
- $introcontent = substr($introcontent, 0, 165)."...";
- $introdocs = strip_tags($row_linkcat[ 'full_docs']);
- $introdocs = substr($introdocs, 50, 500)."...";
- //now let's make the keywods bold. To do that we will use preg_replace function.
- //Replace field
- $title = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" , $row_linkcat[ 'title' ] );
- $desc = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" , $introcontent);
- $link = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" , $row_linkcat[ 'author' ] );
- $docs = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" , $introdocs);
- foreach($trimmed_array as $trimm){
- if($trimm != 'b' ){
- $title = preg_replace( "'($trimm)'si" , "<strong>\\1</strong>" , $title);
- $desc = preg_replace( "'($trimm)'si" , "<strong>\\1</strong>" , $desc);
- $link = preg_replace( "'($trimm)'si" , "<strong>\\1</strong>" , $link);
- $docs = preg_replace( "'($trimm)'si" , "<strong>\\1</strong>" , $docs);
- }//end highlight
- }//end foreach $trimmed_array
- //format and display search results
- echo '<div class="search-result">';
- echo" <h3> <a href='View.php?id=",$row_linkcat[id],"'><u>",$title,"</a></u></h3>
- <ol><strong>AUTHOR:</strong>
- ",$link,"</ol><ol><strong>DESCRIPTION:</strong> ",$desc,"</ol>
- <ol><strong>FULL DOCUMENTS:</strong> ",$docs,"</ol>";
- echo "<hr/>";
- echo '</div>';
- } //end foreach $newarr
- 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.'"> Previous </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.'"> Next </a>';
- }
- }//end if $row_num_links_main > $limit
- // now you can display the results returned. But first we will display the search form on the top of the page
- }//end if search result
- ?>
Expand|Select|Wrap|Line Numbers
- <form action="search.php" method="post">
- <input name="q" type="text" id="q" size="60" />
- <input name="search2" type="submit" id="search2" value="Search" />
- </form>