Searching for keywords... WOW this is hard.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Searching for keywords... WOW this is hard.

Post by Luke »

OK... I have almost got my directory working... I have a database class, a directory class, an error logging class, and now I need a search class. (this could be incorperated into the directory interaction class, I'm not sure yet)

My problem is that I can't find a way for the searchwords to be found due to plurality, case-sensitivity.. etc. Anybody have any suggestions. Here's what I've got so far:

Directory Class and Search Class

Code: Select all

class dirHandle{
	// This class basically just creates sql queries to pass to the database function.
	// You can pass the directory table name as the first property, and the category table name as the second
	
	var $dirtable;	// Name of table businesses are to be stored in
	var $cattable;	// Name of table categories are to be stored in
	
	function dirHandle($dir="directory", $cat="categories"){
		$this->dirtable = $dir;
		$this->cattable = $cat;
	}
	function add($properties){
		// Method just returns an insert query to pass to the database handle class
		// Accepts an array of each property expected by the database table
		
		$sql = "INSERT INTO " . $this->dirtable . " VALUES (";
		foreach($properties as $val){
			$sql .= "'" . mysql_real_escape_string($val) . "',";
		}
		
		//removes the comma at the end created by the loop
		$sql = substr($sql, 0, -1);
		
		$sql .= ")";
		return $sql;
	}
	function delete($id){
		// Method returns a delete query to pass to the database handle class
		// Accepts the id of the row to be deleted
		
		$sql = "DELETE FROM " . $this->dirtable . " WHERE " . $this->dirtable . "_id = " . $id;
		return $sql;
	}
	function delete_all(){
		// Deletes all rows
		
		$sql = "TRUNCATE TABLE " . $this->dirtable;
		return $sql;
	}
	function modify($id, $properties){
		// Method returns an update query to pass to the database handle class
		// Accepts the id of row to modify, and an array
		// Array must be an associative array of column names as keys and values as values
		
		$sql = "UPDATE `" . $this->dirtable . "` SET ";
		foreach($properties as $key => $val){
			$sql .= "`" . $this->dirtable . "_" . $key . "` = '" . mysql_real_escape_string($val) . "',";
		}

		//removes the comma at the end created by the loop		
		$sql = substr($sql, 0, -1);
		
		$sql .= " WHERE `" . $this->dirtable . "_id` =" . $id . " LIMIT 1";
		return $sql;
	}
	function make_bread_crumbs($totalrows, $current, $increment="10"){
		$pages = $totalrows / $increment;
		$remainder = $totalrows % $increment;
		if($remainder != 0) $pages++;
		$crumbs = "";
		if($current >= 2){
			$prev = $current - 1;
			$crumbs .= "<a href=\"" . $_SERVER['PHP_SELF'] . "?page=" . $prev . "\"> Prev </a>";
		}
		for($i = 1; $i <= $pages; $i++){
			if($current == $i){
				$crumbs .= " " . $i . " ";
			}
			else{
				$crumbs .= " <a href=\"" . $_SERVER['PHP_SELF'] . "?page=" . $i . "\">$i</a> ";
			}
		}
		if($current <= ($pages-1)){
			$next = $current + 1;
			$crumbs .= "<a href=\"" . $_SERVER['PHP_SELF'] . "?page=" . $next . "\"> Next </a>";
		}
		if($pages > 1){
			return $crumbs;
		}
		return false;
	}
	function highlight($row, $keywords){
		foreach($row as $key => $val){
			$highlight = "<b>$keywords</b>";
			$row[$key] = str_replace($keywords, $highlight, $val);
		}
		return $row;
	}
	function display($properties, $format="standard"){
		// Method accepts an array of business properties as indexed in the database.
		// It also accepts a second argument as a format type.
		// Returns html formatted info
		
		// Since the column names are prefixed with the directory table name, the following removes that prefix.
		$search = $this->dirtable . "_";
		foreach ($properties as $key => $value){
			$newkey = str_replace($search, '', $key);
		    $properties[$newkey] = $value;
    		unset($properties[$key]);
		} 
		if($format == "standard"){
			$address = "n/a";
			if(!empty($properties['address'])){
				$address = $properties['address'] . "<br />" . $properties['city'] . ", " . $properties['state'] . " " . $properties['zip'];
			}
			$email = $properties['email'];
			if(strlen($email) > 18){
				$email = substr($email, 0, 18) . "...";
			}
			$phone = formatPhone($properties['phone']);
			
			$html  = "<div class=\"dir" . settype($properties['st_hosted'], "string") . "\">\n";
			$html .= "   <h1 class=\"dir\">" . $properties['name'] . "</h1>\n";
			$html .= "   <p class=\"dir\">" . $properties['description'] . "</p>\n";
			$html .= "   <table class=\"info\" width=\"100%\"> \n";
			$html .= "    <tr>\n";
			$html .= "     <td width=\"25%\"><b>Address</b></td>\n";
			$html .= "     <td width=\"25%\"><b>Phone</b></td>\n";
			$html .= "     <td width=\"25%\"><b>Email</b></td>\n";
			$html .= "     <td width=\"25%\"><b>Website</b></td>\n";
			$html .= "    </tr>\n";
			$html .= "    <tr>\n";
			$html .= "     <td width=\"25%\">" . $address . "</td>\n";
			$html .= "     <td width=\"25%\">" . $phone . "</td>\n";
			$html .= "     <td width=\"25%\"><a href=\"mailto:" . $properties['email'] . "\">" . $email . "</a></td>\n";
			$html .= "     <td width=\"25%\"><a href=\"" . $properties['website'] . "\" target=\"_blank\">" . $properties['website'] . "</a></td>\n";
			$html .= "    </tr>\n";
			$html .= "   </table>\n";
			$html .= "</div>\n";
			return $html;
		}
	}
}
class search{
	var $table;
	function search($table){
		$this->table = $table;
	}
	function find($keyword, $searchcolumn){
		$keyword = strtolower($keyword);
		$sql = "SELECT * FROM " . $this->table . " WHERE `" . $this->table . "_" . $searchcolumn . "` LIKE '% " . $keyword . " %' OR `" . $this->table . "_" . $searchcolumn . "` LIKE '% " . $keyword . " %'";
		return $sql;
	}
	function find_multi($keywords, $searchcolumn="*"){
		
	}
}
Index.php

Code: Select all

<link href="style.css" rel="stylesheet" type="text/css" />
<form name="search" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
 <input type="text" name="keywords" value="" />
 <input type="hidden" name="action" value="search" />
 <input type="submit" value="Search" />
</form>
<?php
include_once("common_functions.php");
include_once("common_classes.php");
include_once("directory_class.php");

$error = new error;

$array = array(
			'name' 			=> "Luke's Company",
			'description' 	=> "This is a description",
			'address' 		=> "1234 Generic Road",
			'city' 			=> "Paradise",
			'state' 		=> "CA",
			'zip' 			=> 95969,
			'phone' 		=> "530-872-1677",
			'email' 		=> "luke.visinoni@johneaton.com",
			'website'		=> "http://www.poo.com",
			'st_hosted' 	=> 0,
			'categories' 	=> "16");

$dirhandle = new dirHandle;
$dbh = new dataBase;
$table = 'directory';
$inc = 10;
if($dbh->connect()){
	$keywords = $_POST['keywords'];
	$page = 1;
	if(!empty($_GET['page'])){
		$page = $_GET['page'];
	}
	$start = $page * $inc - $inc;
	$sql = "SELECT * FROM " . $table . " LIMIT " . $start . ", " . $inc;
	if($_POST['action'] == "search"){
		$search = new search($table);
		if($sql = $search->find($keywords, 'description')){
			if($results = $dbh->select($sql)){
				$amount = mysql_num_rows($results);
				$sql = $sql . " LIMIT " . $start . ", " . $inc;

			}
			else{
				$error->Log($dbh->last_error);
			}
		}
		else{
			$error->Log($dbh->last_error);
		}
	}
	if(!isset($amount)){
		$amount = $dbh->get_row_amount($table);
	}
	echo $dirhandle->make_bread_crumbs($amount, $page, $inc);
	if($results = $dbh->select($sql)){
		while($row = $dbh->get_row($results, 'MYSQL_ASSOC')){
			$row = $dirhandle->highlight($row, $keywords);
			echo $dirhandle->display($row);
		}
	}
	else{
		$error->Log($dbh->last_error);
	}
	echo $dirhandle->make_bread_crumbs($amount, $page, $inc);
}
$dbh->print_last_error();
?>
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Removing the last ',' via substr() is a hackish trick at best. Can you somehow implement it using a conditional appending of the comma? (for instance, have a first flag that gets set to false after the first iteration, or use a for loop)

Anyway...

http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html
http://dev.mysql.com/doc/refman/5.0/en/ ... uning.html
User avatar
trukfixer
Forum Contributor
Posts: 174
Joined: Fri May 21, 2004 3:14 pm
Location: Miami, Florida, USA

Post by trukfixer »

Code: Select all

//removes the comma at the end created by the loop
        $sql = substr($sql, 0, -1);

Code: Select all

$sql = rtrim($sql,",");
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Thanks guys... :) Anything else regarding searching would be appreciated.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Post Reply