[SOLVED] A Simple Search [needs improvement].

[pwnuspoints]

Hello again fellow Phpfreaks!

 

First off, Any help you can provided is sincerely appriciated; thank you in advance for your time!

 

My issue is as follows: I have built a simple 'search' form using Php to locate relevant items in a database on my remote server. Using an HTML form I collect a keyword input by the user, store it as a $variable and then use the $variable in my sql query to filer out rows with matching strings in my database. Naturally, I then echo those rows on the page. All of this is accomplished on a single page named 'search.php'

 

The HTML form code looks like this:

 

<?php

$keyword = $_GET["keyword"];

?>

<form action="search.php" method="get" name="search">

      <input name="keyword" type="text" value="<?php echo $keyword; ?>" size="15">

      <input name="search" type="submit" value="Search">

  <p id="small">Examples: "Agendas", "2009", "A", "pdf"</p>

</form>

 

///////////////////////////////////

 

As you can see I'm using the "get" method to pass the variable to the following php code:

 

//////////////////////////////////

 

<?php

if (empty($keyword)) {

echo "<p>Please Enter Your Search</p>";

} else {

 

include '../../library/config.php';

include '../../library/opendb.php';

 

$result = mysql_query("SELECT * FROM tbfind WHERE coli LIKE '%$keyword%' OR colii LIKE '%$keyword%' OR coliii LIKE '%$keyword%' OR coliv LIKE '%$keyword%' ORDER BY coli");

 

$num_rows = mysql_num_rows($result);

 

echo "<br /><p id='red' align='center'>Your search returned $num_rows result(s).</p> ";

 

echo "<table width='460' border='0' cellspacing='0' cellpadding='0' align='center'>";

echo "<tr>";

echo "<td width='20'> </td>";

echo "<td width='340'>Name</td>";

echo "<td width='100'>Date Created</td>";

 

while($r=mysql_fetch_array($result))

{

 

  $id=$r["id"];

  $coli=$r["coli"];

  $colii=$r["colii"];

  $coliii=$r["coliii"];

  $coliv=$r["filetitle"];

   

  //display the row

 

  echo "<tr>";

  echo "<td><span id='alert'><img src='icon.jpg' width='12' height='14' border='0'></span></td>";

  echo "<td id='cell'><a href='$coli$colii'>$coliii</a></td>";

  echo "<td id='cell'>$coliv</td>";

  echo "</tr>";

}

}

include '../library/closedb.php';

?>

 

/////////////////////////////////////

 

The concept is fairly simple- However, I find the user is limited by it's simplicity. For example, if a user enters a query with spaces (i.e. "Apple Sauce" or "I want apple sauce") the search returns no results. Another issue is the pesky single quotation mark ' . If a user decided to input a string with a single quote, the search would again return zero results.

 

Before we get too carried away with the many suggestions I'm sure you all have; I have kept this search simple for a reason. My grasp of php can be a little rough here and there If you have any ideas It would be especially helpful if you could explain where your ideas fit in with my code!

 

Just to clarify: I'm looking for simple suggestions to optimize this simple database search! Any help is greatly appriciated!

 

Thank-you in advance!

 

 

NOTE: My database stores the filenames, file descriptions, date file created, direct paths, and relative paths of pdf and doc files I have uploaded to the server using a series of other forms which work perfectly. I do my best to follow a strict file naming convention for these files. Hopefully that gives you some insight into the metadata housed in my databases.

 

 

 

[bluesoul]

With regards to quotes, you should add this line immediately after your else statement:

 

$keyword = mysql_real_escape_string($keyword);

[pwnuspoints]

With regards to quotes, you should add this line immediately after your else statement:

 

$keyword = mysql_real_escape_string($keyword);

 

This is interesting, when I do as you said and place the code right after my else statement- the search becomes non-descriminatory.

Meaning, I can type anything in the input box and it returns every row in my database. Nothing seems to get filtered.

 

Any suggestions?

[phparray]

This should fix both problems. Escape the quote with addslashes and break up a sentence into an array of single words building the sql query with each word.

 

Replace

$result = mysql_query("SELECT * FROM tbfind WHERE coli LIKE '%$keyword%' OR colii LIKE '%$keyword%' OR coliii LIKE '%$keyword%' OR coliv LIKE '%$keyword%' ORDER BY coli");

 

With

$keyword = addslashes($keyword);
$sqlWhere = 'WHERE coli LIKE "%'.$keyword.'%" OR colii LIKE "%'.$keyword.'%" OR coliii LIKE "%'.$keyword.'%" OR coliv LIKE "%'.$keyword.'%"';

$keywordArray = explode(" ",$keyword);
foreach($keywordArray as $word)
{
	$word = addslashes($word);
	$sqlWhere .= ' OR coli LIKE "%'.$word.'%"  
			OR colii LIKE "%'.$word.'%" 
			OR coliv LIKE "%'.$word.'%" ';	
}


$result = mysql_query("SELECT * FROM tbfind ".$sqlWhere." ORDER BY coli");

[pwnuspoints]

This should fix both problems. Escape the quote with addslashes and break up a sentence into an array of single words building the sql query with each word.

 

Replace

$result = mysql_query("SELECT * FROM tbfind WHERE coli LIKE '%$keyword%' OR colii LIKE '%$keyword%' OR coliii LIKE '%$keyword%' OR coliv LIKE '%$keyword%' ORDER BY coli");

 

With

$keyword = addslashes($keyword);
$sqlWhere = 'WHERE coli LIKE "%'.$keyword.'%" OR colii LIKE "%'.$keyword.'%" OR coliii LIKE "%'.$keyword.'%" OR coliv LIKE "%'.$keyword.'%"';

$keywordArray = explode(" ",$keyword);
foreach($keywordArray as $word)
{
	$word = addslashes($word);
	$sqlWhere .= ' OR coli LIKE "%'.$word.'%"  
			OR colii LIKE "%'.$word.'%" 
			OR coliv LIKE "%'.$word.'%" ';	
}


$result = mysql_query("SELECT * FROM tbfind ".$sqlWhere." ORDER BY coli");

 

This works fantastically; Thank-you so much phparray!