0 Replies - 3085 Views - Last Post: 08 December 2012 - 01:00 PM

#1 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 931
  • View blog
  • Posts: 3,216
  • Joined: 19-January 10

Search database

Posted 08 December 2012 - 01:00 PM

Description: This snippet requires mysqli with the mysqlnd driver.

To search you must provide a mysqli object first, table name second, array with column names third, and then the search value. Optional arguments would be the excluded values, and search for an exact match.

To search for the word "the" in the column "content" from a table called "stories", you would have to do this:
$result = search($mysqli, "stories", array("content"), "the");

To do the same but exclude stories that contain the word "dog", you can do this:
$result = search($mysqli, "stories", array("content"), "the", "dog");

To search for stories that only contain the word "the", you can do this:
$result = search($mysqli, "stories", array("content"), "the", null, true);


For each of these examples, $result should be an empty array on no results, an array containing all found rows, or false if there was an error.This snippet searches inside of a table for a given value. The columns to be searched must be specified. Includes searching for exact matches and the ability to exclude results that contain a provided string
/*
	 * search(string $table, string[] $columns, string $searchValue, string $exclude=null, boolean $exactMatch=false)
	 * Searches $table for $searchValue inside of $columns. If $exclude is not null, $columsn containing $exclude will not be included.
	 * If $exactMatch is true, direct comparison will be used. If false, %LIKE% will be used.
	 * Returns an array of rows, empty array on no results
	 * Returns false on error
	*/
	public function search($mysqli, $table, $columns, $searchValue, $exclude=null, $exactMatch=false){
		$bind = array();
		$result = array();
		$table = $mysqli->real_escape_string($table);
		$sql = "SELECT * FROM $table WHERE";
		foreach($columns as $key => $col){
			if($key == (count($columns)-1)){
				$sql .= ' ' . $col . ' LIKE ? AND ' . $col . ' NOT LIKE ?';
			} else {
				$sql .= ' ' . $col . ' LIKE ? AND ' . $col . ' NOT LIKE ? OR';
			}
			$bind[0] = (isset($bind[0])) ? $bind[0] . "ss" : 'ss';
			$bind[] = "%" . $searchValue . "%";
			if($exclude == null){
				$bind[] = "";
			} else {
				$bind[] = "%" . $exclude . "%";
			}
		}
		
		foreach($bind as $key => $value){
			$bind[$key] = &$bind[$key]; // Makes them references for bind_param
		}
		
		if($stmt = $mysqli->prepare($sql)){
			call_user_func_array(array($stmt, "bind_param"), $bind);
			$stmt->execute();
			
			$results = $stmt->get_result();
			while($row = $results->fetch_array(MYSQLI_ASSOC)){
				$result[] = $row;
			}
			return $result;
		}
		return false;
	}


Is This A Good Question/Topic? 0
  • +

Page 1 of 1