Modify PHP & MySQL Search Problem?

PHP
1

Hey Guys,
I have got a search on my website that searches exact phrases and not each word. I have been looking around for code that I can use to modify my original but I can’t seen to find a simple way to do it. I am not experienced in this sort of thing but I know the basics.
I am looking to add in the explode() function and do a search of each word the user types into the single search textbox.
I have looked at this site:

It is helpful if I wanted to use all the code but I have other codes that are hooked into the search I am using and it would be too hard to modify it again. Also as it works and looks like my other pages I have.

Here is my code:

<?
$searchfield=$_POST['searchfield'];
$sort=$_GET['sort'];
if ($searchfield != '') { 

 //trim whitespace from the search
 $searchfield = trim($searchfield);
       
if ($searchfield == "") {
                <p>Please enter a search</p>
} else {	

$dbhost = '*******';
$dbuser = '*******';
$dbpass = '*******';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('MYSQL N/A - Please Contact Admin');

$dbname = 'database';
mysql_select_db($dbname);

// How many rows of results
$rowsPerPage = 10;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page'])) {
    $pageNum = $_GET['page'];
}

// Count the offset so each page will know how many rows to skip in order to get to the rows meant for each page
$offset = ($pageNum - 1) * $rowsPerPage; 

$query="SELECT * FROM table WHERE field like '%$searchfield%' OR field2 like '%$searchfield%' ORDER BY $sortcode LIMIT $offset, $rowsPerPage";
$result=mysql_query($query);

$num=mysql_numrows($result);

if ($num == '0') {
?>              
                   <p>No results found</p>           
<?
mysql_close($conn);
 } else {
if ($descriptionandpic == 'yes') { ?>				
			    <table border="0" width="100%" id="table1">

 <?php        } else { ?>  
             
			    <table border="0" width="100%" id="table1">
                  <tr>
                    <td><b><font size="2">Title:</font></b></td>
                    <td><b><font size="2">Categories:</font></b></td>
                    <td><b><font size="2">Duration:</font></b></td>
                    <td><b><font size="2">Date Added:</font></b></td>
                    <td><b><font size="2">Rating:</font></b></td>
                  </tr>                
                            
  <?php        }

$i=0;
while ($i < $num) {

$title=mysql_result($result,$i,"title");
$categorys=mysql_result($result,$i,"categorys");
$dateadded=mysql_result($result,$i,"dateadded");
$duration=mysql_result($result,$i,"duration");
$rating=mysql_result($result,$i,"rating");
?>

SHOW RESULTS HERE
        
<?	// We have to query the database again to count how many rows we have total
$query2   = "SELECT COUNT(id) AS numrows FROM table WHERE field like '%$searchfield%' OR field2 like '%$searchfield%'";
$result2  = mysql_query($query2) or die('Error, query2 failed');
$row     = mysql_fetch_array($result2, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);  

// Print page number links
$self = $_SERVER['PHP_SELF'];
$nav = '';

for($page = 1; $page <= $maxPage; $page++) {
    if ($page == $pageNum) {
            $nav .= " $page ";   // no need to create a link to current page
    } else {
            $nav .= " <a href=\\"$self?page=$page&rowsPerPage=$rowsPerPage&sort=$sort&searchfieldsort=$searchfield&descriptionandpic=$descriptionandpic\\" class='page'>$page</a> ";
    }
}
$i++;
}
    // creating previous and next link
    // plus the link to go straight to
    // the first and last page
    if ($pageNum > 1) {
        $page = $pageNum - 1;
        $prev = " <a href=\\"$self?page=$page&rowsPerPage=$rowsPerPage&sort=$sort&searchfieldsort=$searchfield&descriptionandpic=$descriptionandpic\\" class='page'> < </a> ";
         
        $first = " <a href=\\"$self?page=1&rowsPerPage=$rowsPerPage&sort=$sort&searchfieldsort=$searchfield&descriptionandpic=$descriptionandpic\\" class='page'> << </a> ";
    } else {
        $prev  = '&nbsp;'; // we're on page one, don't print previous link
        $first = '&nbsp;'; // nor the first page link
    }
    if ($pageNum < $maxPage) {
        $page = $pageNum + 1;
        $next = " <a href=\\"$self?page=$page&rowsPerPage=$rowsPerPage&sort=$sort&searchfieldsort=$searchfield&descriptionandpic=$descriptionandpic\\" class='page'> > </a> ";
        $last = " <a href=\\"$self?page=$maxPage&rowsPerPage=$rowsPerPage&sort=$sort&searchfieldsort=$searchfield&descriptionandpic=$descriptionandpic\\" class='page'> >> </a> ";
    } else {
        $next = '&nbsp;'; // we're on the last page, don't print next link
        $last = '&nbsp;'; // nor the last page link
    }

// print the navigation link
if ($numrows > $rowsPerPage) {
echo '<br><br>' . $numrows . ' results found for &quot;<b>' . $searchfield . '</b>&quot;:</p><br><br>' . $first . $prev . $nav . $next . $last;
} else {
echo '<br><br>' . $numrows . ' results found for &quot;<b>' . $searchfield . '</b>&quot;:</p><br><br>';
}

mysql_close();

?>  
                </table>
<? 
} }

if ($numrows > $rowsPerPage) {
echo '<br>' . $first . $prev . $nav . $next . $last;
} ?>

Thanks in advance!
freddoco

2

The last bit referred to modifying the new query in place of

 
<?    // We have to query the database again to count how many rows we have total 
$query2   = "SELECT COUNT(id) AS numrows FROM table WHERE field like '%$searchfield%' OR field2 like '%$searchfield%'";

in your original code.

3

Thanks! Looks good I will try it out this week end. I’m Just not too sure about that last bit:

After you have the query set up correctly you will have to use it again to calculate the total number of rows for all the pages.

Do you mean when calculating the pages and page numbers etc?

4

That’s even better - and then only use a “bad” words array for longer words you definitely want to exclude for some reason.

5

It looks like this line is your main query

 
$query="SELECT * FROM table WHERE field like '&#37;$searchfield%' OR field2 like '%$searchfield%' ORDER BY $sortcode LIMIT $offset, $rowsPerPage";

So to modify it to search for every word instead of a phrase, one option is to use something similar to this

 
<?php
 
$phrase = 'my test phrase';
$searchWords = explode(' ',$phrase);
$numWords = count($searchWords);
$count = 1;
 
$query = 'select * from myTable where ';
 
foreach($searchWords as $word) {
     $word = trim($word);
     if($count > 1) {
       $query .= ' or ';
     }
     $query .= 'field1 like "%'.$word.'%" or field2 like "%'.$word.'%" ';
     if($count++ < $numwords) {
       $query .= ' or ';
     }
}
 
$query .= ' order by someColumn';
 
//for debugging - display the query to make sure it is correct.
 
echo $query; die();
 
?>

I haven’t included any error checking in the code.

After you have the query set up correctly you will have to use it again to calculate the total number of rows for all the pages.

6

One way is to put all the “bad” words into an array and then check if each “exploded” word is in the array before including it in the query.

 
<?php
$badWords = array('is','if');
$phrase = 'this is my test phrase';
$searchWords = explode(' ',$phrase);
$numWords = count($searchWords);
$count = 1;
 
$query = 'select * from myTable where ';
 
foreach($searchWords as $word) {
     $word = trim($word);
     if(!in_array($word,$badWords)) {
         if($count > 1) {
           $query .= ' or ';
         }
         $query .= 'field1 like "&#37;'.$word.'%" or field2 like "%'.$word.'%" ';
         if($count++ < $numwords) {
           $query .= ' or ';
         }
     }
}
 
$query .= ' order by someColumn';
 
//for debugging - display the query to make sure it is correct.
 
echo $query; die();
 
?>

also, if you haven’t already done so, you will probably need to include code to make the exploded search words case-insensitive to what is in your database and also handle situations where there is more than one blank space between the words in the phrase.

7

You could also impose a minimum word length (for example 3 characters) and discard all smaller words. How many meaningful 2 letter search words are there?

8

Thanks heaps tried it out and it worked well. Any simple way of not searching for words such as ‘and’ ‘if’ ‘or’ ‘i’ etc