php mysql search question

justAnoob · June 2, 2009

Does anyone know of any good tutorials or examples to learn how to create a php mysql search engine.. Just something simple(I hope).. I have 2 columns in 1 table that I want to search...The search would take whatever words entered in the search box,, and then it would display any of the rows that had those keywoards in the 2 columns that I want to search... Is that simple or no?

dreamwest · June 2, 2009

http://www.tizag.com/mysqlTutorial/mysqlselect.php

justAnoob · June 2, 2009

I already know about selecting everything from a database... I'll keep looking.

.josh · June 3, 2009

Kind of bulky, but here's the general principle. Also, a lot of search engines do things like only search words that are n length and/or omit common words like 'a' 'the' 'an' etc... to avoid returning a million results. Also I have a preg_replace in there that strips out all non-alphanumeric chars. That may or may not suit you (like for instance if you were wanting to search for email addresses or urls, that's not going to work for you).

Another thing you can do to make it more efficient is to offer options like "exact" vs. "partial" matches. This query string builder will search for all words in the search string that are in the columns, regardless of where it is in the string. If you have an option to do exact matches you can streamline it to do for example

select * from table where column1 in ('word1','word2',etc..)

This will significantly reduce processing time.

<?php
$searchString = "foo bar"; // example search string

$table = "tablename"; // enter table name here
$columns = array('column1','column2'); // enter columns here

$searchString = explode(" ",$searchString); // break string into individual words
// for each column...
foreach ($columns as $c) {
// for each word...
  foreach($searchString as $w) {
    // remove all non-alphanumeric chars
    $w = preg_replace('~[^-0-9a-z_]~i','',$w);
    // add condition to string
    $querySearchStringParams .= " $c LIKE '%$w%' OR";
  } // end foreach word
} // end foreach column
// remove last ' OR' from the loop
$querySearchStringParams = substr($querySearchStringParams,0,-3);
// build query string
$queryString = "SELECT * FROM $table WHERE" . $querySearchStringParams;
?>

dreamwest · June 3, 2009

I already know about selecting everything from a database... I'll keep looking.

Thats it. All you need is the search query - simple


   $sql_search_id  = strip_tags( $_REQUEST['search_id'] );

//group by title - important to go before sortby and after where clause, otherwise distinct * wont work
$sql = "SELECT distinct * FROM table where (title like '%".$sql_search_id."%') group by title ";

justAnoob · June 3, 2009

I also heard that it is very easy for a hacker to enter something in the search box to destroy the database... Someone told me that the % was dangerous to enter in the search box also. What do I need to be carefull so I can add a search bar to my site for anyone to use?

.josh · June 3, 2009

It's no more dangerous than any allowing any other user input to reach the database. You would of course sanitize the user input before it goes to your database.

dreamwest · June 3, 2009

class.inputfilter_clean.php

<?php
/** @class: InputFilter (PHP4 & PHP5, without comments)
  * @project: PHP Input Filter
  * @date: 10-05-2005
  * @version: 1.2.2_php4/php5
  * @author: Daniel Morris
  * @contributors: Gianpaolo Racca, Ghislain Picard, Marco Wandschneider, Chris Tobin and Andrew Eddie.
  * @copyright: Daniel Morris
  * @email: dan@rootcube.com
  * @license: GNU General Public License (GPL)
  */
class InputFilter {
var $tagsArray;
var $attrArray;
var $tagsMethod;
var $attrMethod;
var $xssAuto;
var $tagBlacklist = array('applet', 'body', 'bgsound', 'base', 'basefont', 'embed', 'frame', 'frameset', 'head', 'html', 'id', 'iframe', 'ilayer', 'layer', 'link', 'meta', 'name', 'object', 'script', 'style', 'title', 'xml');
var $attrBlacklist = array('action', 'background', 'codebase', 'dynsrc', 'lowsrc');
function inputFilter($tagsArray = array(), $attrArray = array(), $tagsMethod = 0, $attrMethod = 0, $xssAuto = 1) {		
	for ($i = 0; $i < count($tagsArray); $i++) $tagsArray[$i] = strtolower($tagsArray[$i]);
	for ($i = 0; $i < count($attrArray); $i++) $attrArray[$i] = strtolower($attrArray[$i]);
	$this->tagsArray = (array) $tagsArray;
	$this->attrArray = (array) $attrArray;
	$this->tagsMethod = $tagsMethod;
	$this->attrMethod = $attrMethod;
	$this->xssAuto = $xssAuto;
}
function process($source) {
	if (is_array($source)) {
		foreach($source as $key => $value)
			if (is_string($value)) $source[$key] = $this->remove($this->decode($value));
		return $source;
	} else if (is_string($source)) {
		return $this->remove($this->decode($source));
	} else return $source;	
}
function remove($source) {
	$loopCounter=0;
	while($source != $this->filterTags($source)) {
		$source = $this->filterTags($source);
		$loopCounter++;
	}
	return $source;
}	
function filterTags($source) {
	$preTag = NULL;
	$postTag = $source;
	$tagOpen_start = strpos($source, '<');
	while($tagOpen_start !== FALSE) {
		$preTag .= substr($postTag, 0, $tagOpen_start);
		$postTag = substr($postTag, $tagOpen_start);
		$fromTagOpen = substr($postTag, 1);
		$tagOpen_end = strpos($fromTagOpen, '>');
		if ($tagOpen_end === false) break;
		$tagOpen_nested = strpos($fromTagOpen, '<');
		if (($tagOpen_nested !== false) && ($tagOpen_nested < $tagOpen_end)) {
			$preTag .= substr($postTag, 0, ($tagOpen_nested+1));
			$postTag = substr($postTag, ($tagOpen_nested+1));
			$tagOpen_start = strpos($postTag, '<');
			continue;
		} 
		$tagOpen_nested = (strpos($fromTagOpen, '<') + $tagOpen_start + 1);
		$currentTag = substr($fromTagOpen, 0, $tagOpen_end);
		$tagLength = strlen($currentTag);
		if (!$tagOpen_end) {
			$preTag .= $postTag;
			$tagOpen_start = strpos($postTag, '<');			
		}
		$tagLeft = $currentTag;
		$attrSet = array();
		$currentSpace = strpos($tagLeft, ' ');
		if (substr($currentTag, 0, 1) == "/") {
			$isCloseTag = TRUE;
			list($tagName) = explode(' ', $currentTag);
			$tagName = substr($tagName, 1);
		} else {
			$isCloseTag = FALSE;
			list($tagName) = explode(' ', $currentTag);
		}		
		if ((!preg_match("/^[a-z][a-z0-9]*$/i",$tagName)) || (!$tagName) || ((in_array(strtolower($tagName), $this->tagBlacklist)) && ($this->xssAuto))) { 				
			$postTag = substr($postTag, ($tagLength + 2));
			$tagOpen_start = strpos($postTag, '<');
			continue;
		}
		while ($currentSpace !== FALSE) {
			$fromSpace = substr($tagLeft, ($currentSpace+1));
			$nextSpace = strpos($fromSpace, ' ');
			$openQuotes = strpos($fromSpace, '"');
			$closeQuotes = strpos(substr($fromSpace, ($openQuotes+1)), '"') + $openQuotes + 1;
			if (strpos($fromSpace, '=') !== FALSE) {
				if (($openQuotes !== FALSE) && (strpos(substr($fromSpace, ($openQuotes+1)), '"') !== FALSE))
					$attr = substr($fromSpace, 0, ($closeQuotes+1));
				else $attr = substr($fromSpace, 0, $nextSpace);
			} else $attr = substr($fromSpace, 0, $nextSpace);
			if (!$attr) $attr = $fromSpace;
			$attrSet[] = $attr;
			$tagLeft = substr($fromSpace, strlen($attr));
			$currentSpace = strpos($tagLeft, ' ');
		}
		$tagFound = in_array(strtolower($tagName), $this->tagsArray);			
		if ((!$tagFound && $this->tagsMethod) || ($tagFound && !$this->tagsMethod)) {
			if (!$isCloseTag) {
				$attrSet = $this->filterAttr($attrSet);
				$preTag .= '<' . $tagName;
				for ($i = 0; $i < count($attrSet); $i++)
					$preTag .= ' ' . $attrSet[$i];
				if (strpos($fromTagOpen, "</" . $tagName)) $preTag .= '>';
				else $preTag .= ' />';
		    } else $preTag .= '</' . $tagName . '>';
		}
		$postTag = substr($postTag, ($tagLength + 2));
		$tagOpen_start = strpos($postTag, '<');			
	}
	$preTag .= $postTag;
	return $preTag;
}
function filterAttr($attrSet) {	
	$newSet = array();
	for ($i = 0; $i <count($attrSet); $i++) {
		if (!$attrSet[$i]) continue;
		$attrSubSet = explode('=', trim($attrSet[$i]));
		list($attrSubSet[0]) = explode(' ', $attrSubSet[0]);
		if ((!eregi("^[a-z]*$",$attrSubSet[0])) || (($this->xssAuto) && ((in_array(strtolower($attrSubSet[0]), $this->attrBlacklist)) || (substr($attrSubSet[0], 0, 2) == 'on')))) 
			continue;
		if ($attrSubSet[1]) {
			$attrSubSet[1] = str_replace('&#', '', $attrSubSet[1]);
			$attrSubSet[1] = preg_replace('/\s+/', '', $attrSubSet[1]);
			$attrSubSet[1] = str_replace('"', '', $attrSubSet[1]);
			if ((substr($attrSubSet[1], 0, 1) == "'") && (substr($attrSubSet[1], (strlen($attrSubSet[1]) - 1), 1) == "'"))
				$attrSubSet[1] = substr($attrSubSet[1], 1, (strlen($attrSubSet[1]) - 2));
			$attrSubSet[1] = stripslashes($attrSubSet[1]);
		}
		if (	((strpos(strtolower($attrSubSet[1]), 'expression') !== false) &&	(strtolower($attrSubSet[0]) == 'style')) ||
				(strpos(strtolower($attrSubSet[1]), 'javascript:') !== false) ||
				(strpos(strtolower($attrSubSet[1]), 'behaviour:') !== false) ||
				(strpos(strtolower($attrSubSet[1]), 'vbscript:') !== false) ||
				(strpos(strtolower($attrSubSet[1]), 'mocha:') !== false) ||
				(strpos(strtolower($attrSubSet[1]), 'livescript:') !== false) 
		) continue;
		$attrFound = in_array(strtolower($attrSubSet[0]), $this->attrArray);
		if ((!$attrFound && $this->attrMethod) || ($attrFound && !$this->attrMethod)) {
			if ($attrSubSet[1]) $newSet[] = $attrSubSet[0] . '="' . $attrSubSet[1] . '"';
			else if ($attrSubSet[1] == "0") $newSet[] = $attrSubSet[0] . '="0"';
			else $newSet[] = $attrSubSet[0] . '="' . $attrSubSet[0] . '"';
		}	
	}
	return $newSet;
}
function decode($source) {
	$source = html_entity_decode($source, ENT_QUOTES, "ISO-8859-1");
	$source = preg_replace('/&#(\d+);/me',"chr(\\1)", $source);
	$source = preg_replace('/&#x([a-f0-9]+);/mei',"chr(0x\\1)", $source);
	return $source;
}
function safeSQL($source, &$connection) {
	if (is_array($source)) {
		foreach($source as $key => $value)
			if (is_string($value)) $source[$key] = $this->quoteSmart($this->decode($value), $connection);
		return $source;
	} else if (is_string($source)) {
		if (is_string($source)) return $this->quoteSmart($this->decode($source), $connection);
	} else return $source;	
}
function quoteSmart($source, &$connection) {
	if (get_magic_quotes_gpc()) $source = stripslashes($source);
	$source = $this->escapeString($source, $connection);
	return $source;
}
function escapeString($string, &$connection) {
	if (version_compare(phpversion(),"4.3.0", "<")) mysql_escape_string($string);
	else mysql_real_escape_string($string);
	return $string;
}
}
?>

search.php

    include( "class.inputfilter_clean.php" );
    $myFilter = new inputfilter( );
    $search = $myFilter->process( $search );
    $search = str_replace( "\"", " ", $search );
    $search = str_replace( "?", " ", $search );
    $search = str_replace( ">", " ", $search );
    
$sql_search_id  = strip_tags( $search );

//group by title - important to go before sortby and after where clause, otherwise distinct * wont work
$sql = "SELECT distinct * FROM table where (title like '%".$sql_search_id."%') group by title ";

justAnoob · June 3, 2009

Wow,,,, ok. Isn't there a lot of lines in the filter php file that I would have to change to see how it works(with my setup)

I don't want to just copy and paste something into my site. Although it looks pretty impressive from a newbie point of view.

I was hoping of something a little more basic, just to start off with.

dreamwest · June 3, 2009

class.inputfilter_clean.php is your best defense for users and passwod login, it doesnt need configuring at all

$search = $_REQUEST['q']; // is the only thing youll need to configure

justAnoob · June 3, 2009

I do not see that line anywhere.,,,,Anyways after the sql select statement could I use a header statement to then post all the results from the search?

justAnoob · June 3, 2009

Ok,, so I wanted to try this out,, my results are not appearing... Here is what I got.

Form

<?php
<form id="form99" name="form99" method="post" action="search.php">
      <div id="search">
        <table width="500" height="36" border="0" cellpadding="0" cellspacing="0">
          <tr>
            <td><input type="text" name="myFilter" size=50 id="myFilter">
              <input type="submit" name="submit" value=" Search " id="submit"></td>
          </tr>
        </table>
      </div>
      </form>
?>

search.php

<?php
session_start();
include( "connection.php" );
include( "class.inputfilter_clean.php" );

if(isset($_POST['submit']))
{
   $myFilter = new inputfilter( );
   $search = $myFilter->process( $search );
   $search = str_replace( "\"", " ", $search );
   $search = str_replace( "?", " ", $search );
   $search = str_replace( ">", " ", $search );
   $sql_search_id  = strip_tags( $search );

   
   $sql=("SELECT id, imgpath, item_name, description, in_return FROM abcxyz WHERE item_name like '%".$sql_search_id."%' OR description like '%".        $sql_search_id."%' group by item_name ");
   $result = mysql_query($sql);
    echo "<table border='0' CELLPADDING=5 STYLE='font-size:16px'>";
 while ($row = mysql_fetch_array($result))
 {
       echo "<tr><td align='center'>";
   echo '<a href="viewitem.php?sendto='.$row['id'].'"><img src="' . $row['imgpath'] . '" width="125" border="0" alt=""></a>';
   echo "</td><td align='center'>";
   echo "".substr($row['item_name'],0,50),"";
   echo "</td><td align='center'>";
   echo substr($row['description'],0,50).'<a href="viewitem.php?sendto='.$row['id'].'" class="view_item"> ...more</a>';
   echo "</td><td align='center'>";
   echo substr($row['in_return'],0,50).'<a href="viewitem.php?sendto='.$row['id'].'" class="view_item"> ...</a>';
       echo "</td><td align='center'>";
   echo "</td></tr>";
       echo '<tr><td height="19" colspan="4">';
       echo '<hr width="550">';
       echo "</td></tr>";
   }
echo "</table>";
  }
  else
  {
      echo "No search results.";
  }


}



?>

dreamwest · June 3, 2009

You have no value

form

Now get the value:

if(isset($_POST['submit']))
{
$search = $_POST['myFilter'];

   $myFilter = new inputfilter( );
   $search = $myFilter->process( $search );
   $search = str_replace( "\"", " ", $search );
   $search = str_replace( "?", " ", $search );
   $search = str_replace( ">", " ", $search );
   $sql_search_id  = strip_tags( $search );

justAnoob · June 3, 2009

It keeps saying that there are no search results.. Actually I typed part of a picture name in and it displayed that picture,, after that I am not able to get any kind of results.

dreamwest · June 3, 2009

Take a step back and echo the form

search.php

$search = $_POST['myFilter'];
echo $search;

justAnoob · June 3, 2009

I just echoed $search and again,,, the first time i try it,, it echoes out what I type in,, after that,, it says nothing.

justAnoob · June 3, 2009

ok,,, if I type something in like "fkldsjsdgtaphdfh",,,,, it will echo out that mumble jumble... but if i type in something like "good", it will not echo that out.

It seems like it is choosing what it wants to echo.

Something with the filter file is blocking certain things...

dreamwest · June 3, 2009

The filter strips tags ....and stuff but not normal strings

Itll remove

<script = hello?>

but not a normal string - which most users will use

hello im searching

justAnoob · June 3, 2009

ok,, I am back to having the search work for a couple times and then it after the 2nd or 3rd search it keeps telling me there are no results when I know that it is in the database under the 2 columns that I'm searching.

What is going on now?

<?php
session_start();
include( "connection.php" );
include( "class.inputfilter_clean.php" );

if(isset($_POST['submit']))
{
   $search = $_POST['myFilter'];
    
   $myFilter = new inputfilter( );
   $search = $myFilter->process( $search );
   $search = str_replace( "\"", " ", $search );
   $search = str_replace( "?", " ", $search );
   $search = str_replace( ">", " ", $search );
   $sql_search_id  = strip_tags( $search );

   $sql=("SELECT item_name FROM abcxyz WHERE item_name like '%".$sql_search_id."%' OR description like '%".sql_search_id."%' group by item_name ");
      
   $result = mysql_query($sql);
   echo "<table border='0' CELLPADDING=5 STYLE='font-size:16px'>";
   while ($row = mysql_fetch_array($result))
   {
       echo "<tr><td align='center'>";
   echo '<a href="viewitem.php?sendto='.$row['id'].'"><img src="' . $row['imgpath'] . '" width="125" border="0" alt=""></a>';
   echo "</td><td align='center'>";
   echo "".substr($row['item_name'],0,50),"";
   echo "</td><td align='center'>";
   echo substr($row['description'],0,50).'<a href="viewitem.php?sendto='.$row['id'].'" class="view_item"> ...more</a>';
   echo "</td><td align='center'>";
   echo substr($row['in_return'],0,50).'<a href="viewitem.php?sendto='.$row['id'].'" class="view_item"> ...</a>';
       echo "</td><td align='center'>";
   echo "</td></tr>";
       echo '<tr><td height="19" colspan="4">';
       echo '<hr width="550">';
       echo "</td></tr>";
}
echo "</table>";
}
else
{
   echo "No search results.";
}


?>

dreamwest · June 3, 2009

If you want everthing to show delete this.

group by item_name

The GROUP BY statement is used to group the result-set by one or more columns.

justAnoob · June 3, 2009

I don't get this at all. ,,, I made the changes you suggested.. But if I type in the letter "a",, 3 of 4 pictures show up... But if I type in something that is in one of the columns that I'm searching, I get nothing.

<?php
session_start();
include( "connection.php" );
include( "class.inputfilter_clean.php" );

if(isset($_POST['submit']))
{
   $search = $_POST['myFilter'];
    
   $myFilter = new inputfilter( );
   $search = $myFilter->process( $search );
   $search = str_replace( "\"", " ", $search );
   $search = str_replace( "?", " ", $search );
   $search = str_replace( ">", " ", $search );
   $sql_search_id  = strip_tags( $search );

   $sql=("SELECT * FROM abcxyz WHERE item_name like '%".$sql_search_id."%' OR description like '%".sql_search_id."%' ");
      
   $result = mysql_query($sql);
   echo "<table border='0' CELLPADDING=5 STYLE='font-size:16px'>";
   while ($row = mysql_fetch_array($result))
   {
       echo "<tr><td align='center'>";
   echo '<a href="viewitem.php?sendto='.$row['id'].'"><img src="' . $row['imgpath'] . '" width="125" border="0" alt=""></a>';
   echo "</td><td align='center'>";
   echo "".substr($row['item_name'],0,50),"";
   echo "</td><td align='center'>";
   echo substr($row['description'],0,50).'<a href="viewitem.php?sendto='.$row['id'].'" class="view_item"> ...more</a>';
   echo "</td><td align='center'>";
   echo substr($row['in_return'],0,50).'<a href="viewitem.php?sendto='.$row['id'].'" class="view_item"> ...</a>';
       echo "</td><td align='center'>";
   echo "</td></tr>";
       echo '<tr><td height="19" colspan="4">';
       echo '<hr width="550">';
       echo "</td></tr>";
}
echo "</table>";
}
else
{
   echo "No search results.";
}


?>

dreamwest · June 3, 2009

Try this it will output what the form input is

<?php
session_start();
include( "connection.php" );
include( "class.inputfilter_clean.php" );

if(isset($_POST['submit']))
{
   $search = $_POST['myFilter'];

echo $search; //take this away after testing
    
   $myFilter = new inputfilter( );
   $search = $myFilter->process( $search );
   $search = str_replace( "\"", " ", $search );
   $search = str_replace( "?", " ", $search );
   $search = str_replace( ">", " ", $search );
   $sql_search_id  = strip_tags( $search );

   $sql=("SELECT * FROM abcxyz WHERE item_name like '%".$sql_search_id."%' OR description like '%".sql_search_id."%' ");
      
   $result = mysql_query($sql);
$total =  mysql_num_rows( $result );
       
       if ( $total <= 0 )
{
    echo "<b>Nothing matches<b>";
}else{
   echo "<table border='0' CELLPADDING=5 STYLE='font-size:16px'>";
   while ($row = mysql_fetch_array($result))
   {
       echo "<tr><td align='center'>";
      echo '<a href="viewitem.php?sendto='.$row['id'].'"><img src="' . $row['imgpath'] . '" width="125" border="0" alt=""></a>';
      echo "</td><td align='center'>";
      echo "".substr($row['item_name'],0,50),"";
      echo "</td><td align='center'>";
      echo substr($row['description'],0,50).'<a href="viewitem.php?sendto='.$row['id'].'" class="view_item"> ...more</a>';
      echo "</td><td align='center'>";
      echo substr($row['in_return'],0,50).'<a href="viewitem.php?sendto='.$row['id'].'" class="view_item"> ...</a>';
       echo "</td><td align='center'>";
      echo "</td></tr>";
       echo '<tr><td height="19" colspan="4">';
       echo '<hr width="550">';
       echo "</td></tr>";
   }
   echo "</table>";
}
else
{
   echo "No search results.";
}
}

?>

justAnoob · June 4, 2009

again,, i can search the word "a" and 3 of the 4 pictures show up,, but when i enter any of the words that are in the name or description, nothing appears,, not even the echo $search var

justAnoob · June 4, 2009

I think I will just a temporarily down sign on my search box. LoL.

Sign In

php mysql search question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information