Search Script help

[StrikeFreedom]

Hi I am developping a search script for my site and I'm stuck at one part. The script I did works fine but it looks for exact matches within the fields. For example if I type "John Smith" it would return all the rows with John Smith in them. But if I type Smith John I won't get those results how should I build my search query so it would return the rows even if I switch the order of the words?

my scripts are as follow:

The form:
<table width="236" border="0" cellspacing="0" cellpadding="0">
<tr><form id="search" method="post" action="search.php">
<td width="180"><input name="keyword" type="text" id="keyword" size="30" maxlength="50"></td>
<td width="56"><input type="submit" name="Search" value="Search"></td></form>
</tr>
</table>

Search Script:
$sql=mysql_query("SELECT * FROM stories WHERE title LIKE '%".$_POST."%'
OR author LIKE '%".$_POST."%' OR content LIKE '%".$_POST."%'");
$num_results=mysql_num_rows($sql);

echo 'You have searched for: '.$keyword.'<p>Number of Results: '.$num_results.'</p>';

[zippee]

Try this

$post=trim($_POST['keyword']);
$split = explode(' ', $post); // if there is two or more words
$count = substr_count($keyword,' '); // how many separate words
$keyword = '';
for ($i=0;$i<$count;$i++) 
{
$keyword = '%'.$split[$i].'%';
$condition .= title LIKE '$keyword' OR author LIKE '$keyword' OR content LIKE '$keyword';
}
$sql=mysql_query("SELECT * FROM stories WHERE $condition");
$num_results=mysql_num_rows($sql);

This script may earn me some $$$ :cheesy:

I use similar script for my store directory. See how it works at www.ecommerce-web-store.com/store_directory
.

[StrikeFreedom]

Thx a lot! The code works :)

[zippee]

That's great! So you have good time in daniweb then...

.

[StrikeFreedom]

I tested out that code further but I found that I have a little problem, when I type in one search word it doesn't return anything and when I enter two it looks for the first one more than two it's not returning things either. I checked your site and it seems to work did I do something wrong? The substr_count seems to be retuning the value of 0 when I enter one word and 1 for two words could it be that the problem? For some reason if the count goes beyond 1 th loop doesn't seem to work either

<?
$post=trim($_POST['keyword']); 
$split = explode(' ', $post); // if there is two or more words 
$count = substr_count($keyword,' '); // how many separate words 
$keyword = ''; 
for ($i=0;$i<$count;$i++) 
{ 
$keyword = '%'.$split[$i].'%'; 
$condition .= "title LIKE '$keyword' OR author LIKE '$keyword' OR content LIKE '$keyword'"; 
} 
$sql=mysql_query("SELECT * FROM stories WHERE $condition"); 
$num_results=mysql_num_rows($sql); 

echo '<table width="504" border="0" valign="top" cellspacing="0" cellpadding="0">';
echo  '<tr class="style1">';
echo    '<td width="200"><span class="style4">Title</span></td>';
echo    '<td width="100"><strong>Author</strong></td>';
echo    '<td width="30"><strong>Vol</strong></td>';
echo    '<td width="30"><strong>No</strong></td>';
echo    '<td><strong>Intro</strong></td>';
echo  '</tr>';

  
for ($i=0; $i<$num_results; $i++){
$row=mysql_fetch_array($sql);

$id=$row['storynum'];
$t=$row['title'];
$au=$row['author'];
$vol=$row['volume'];
$num=$row['number'];
$fp=$row['first_p'];

echo  '<tr>';
echo    '<td><a href="article.php?id='.$id.'">'.$t.'</a></td>';
echo    '<td>'.$au.'</td>';
echo    '<td>'.$vol.'</td>';
echo    '<td>'.$num.'</td>';
echo    '<td>'.$fp.'</td>';
echo  '</tr>';
}
echo '</table>';
?>

[StrikeFreedom]

If possible could I see the search part of the script from your site?
Sorry for the trouble and thanks!

[zippee]

Spot the error I made in the script above, replaced the $keyword with $post as below:
$count = substr_count($post,' ');

And, the for loop should less than and equal to $count:
for($i=0; $i<=$count; $i++)

[StrikeFreedom]

I tried the code with the modifications, but now it's only working when it's one word search, when I put two words it gives me this warning:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/comtact/public_html/search.php on line 100

It seems the loop isn't working if the count value isn't 0

The substr_count seem to still count one words as 0 and two as 1. I tried replacing $count = substr_count($post,' '); with $count = substr_count($split,' '); it works but then I'm back at the problem of mysql only searching for the first word because the substr_count returns 0 no matter the number of words

[digital-ether]

I tried the code with the modifications, but now it's only working when it's one word search, when I put two words it gives me this warning:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/comtact/public_html/search.php on line 100

It seems the loop isn't working if the count value isn't 0

The substr_count seem to still count one words as 0 and two as 1. I tried replacing $count = substr_count($post,' '); with $count = substr_count($split,' '); it works but then I'm back at the problem of mysql only searching for the first word because the substr_count returns 0 no matter the number of words

There're was a few typos in the script...

Heres one that should work...

// search all words like the input

//  trim whitespace from the ends of user input
$post=trim($_POST['keyword']);

// add slashes to prevent sql injection
if (!get_magic_quotes_gpc()) {

   $post = addslashes($post);
   
 }

// return an error if there isnt a search, or its too short?
if (!$post || strlen($post) <= 2) {
  // a two char or less word is probably too short
  //show an error or show form etc.
  die('error');
}

$split = explode(' ', $post); // if there is two or more words
$count = sizeof($split); // $split is an array of each word, so we take the size

// we already checked and there is at least one word in the search
// create query for first
$condition = "title LIKE '%$split[0]%' OR author LIKE '%$split[0]%' OR content LIKE '%$split[0]%'";

// append additional words to the query
if ($count > 1) {
    for ($i=1;$i<$count;$i++)
    {
    $keyword = '%'.$split[$i].'%';
    $condition .= " OR title LIKE '$keyword' OR author LIKE '$keyword' OR content LIKE '$keyword'";
    }
}

// query db
$sql=mysql_query("SELECT * FROM stories WHERE $condition");
$num_results=mysql_num_rows($sql);

// view your mysql search query
//echo "SELECT * FROM stories WHERE $condition";

You should always make sure you add slashes to the user input

I usually use this function when adding user input into a mysql query:

// safely escape intput to sql query
// if the string will be shown in html output, also use striptags or html_entities
function safeEscapeString($string)
{
 if (get_magic_quotes_gpc()) {
   return $string;
   
 } elseif (mysql_real_escape_string()) {
   return mysql_real_escape_string($string);
   
 } elseif (mysql_escape_string()) {
   return mysql_escape_string($string);
   
 } else {
   return addslashes($string);
 }
}

it safely checks the functions supported by your php version, and implements the 'best' one.

mysql_real_escape_string() uses mysql's C++ API so is preferred.

[digital-ether]

just a note:

You may also want to add: $split = array_unique($split);
after the line:
$split = explode(' ', $post); // if there is two or more words

This will make sure there are no duplicate keywords entered...

[StrikeFreedom]

thanks Ether! I'll try that, I know my reply is late but I haven't been here for a while :p