Need help with a mysql search

[shanetastic]

I found an article on how to implement a mysql search (http://www.iamcal.com/publish/articles/php/search/) and it seemed like a good way to go, but I can't get it up and running.

 

Here is my code:

	<?php
$db_host = 'localhost';
$db_user = 'username';
$db_pwd = 'password';

$database = 'dbname';
$table = 'Principals';

if (!mysql_connect($db_host, $db_user, $db_pwd))
	die("Can't connect to database");

if (!mysql_select_db($database))
	die("Can't select database");




$searchterms = 'dorr oliver';


function search_split_terms($terms){

	$terms = preg_replace("/\"(.*?)\"/e", "search_transform_term('\$1')", $terms);
	$terms = preg_split("/\s+|,/", $terms);

	$out = array();

	foreach($terms as $term){

		$term = preg_replace("/\{WHITESPACE-([0-9]+)\}/e", "chr(\$1)", $term);
		$term = preg_replace("/\{COMMA\}/", ",", $term);

		$out[] = $term;
	}

	return $out;
}

function search_transform_term($term){
	$term = preg_replace("/(\s)/e", "'{WHITESPACE-'.ord('\$1').'}'", $term);
	$term = preg_replace("/,/", "{COMMA}", $term);
	return $term;
}

function search_escape_rlike($string){
	return preg_replace("/([.\[\]*^\$])/", '\\\$1', $string);
}

function search_db_escape_terms($terms){
	$out = array();
	foreach($terms as $term){
		$out[] = '[[:<:]]'.AddSlashes(search_escape_rlike($term)).'[[:>:]]';
	}
	return $out;
}

function search_perform($terms){

	$terms = search_split_terms($terms);
	$terms_db = search_db_escape_terms($terms);
	$terms_rx = search_rx_escape_terms($terms);

	$parts = array();
	foreach($terms_db as $term_db){
		$parts[] = "Principals RLIKE '$term_db'";
	}
	$parts = implode(' OR ', $parts);

	$sql = "SELECT * FROM Principal WHERE $parts";

	$rows = array();

	$result = mysql_query($sql);
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

		$row[score] = 0;

		foreach($terms_rx as $term_rx){
			$row[score] += preg_match_all("/$term_rx/i", $row[Principals], $null);
		}

		$rows[] = $row;
	}

	uasort($rows, 'search_sort_results');

	return $rows;
}

function search_rx_escape_terms($terms){
	$out = array();
	foreach($terms as $term){
		$out[] = '\b'.preg_quote($term, '/').'\b';
	}
	return $out;
}

function search_sort_results($a, $b){

	$ax = $a[score];
	$bx = $b[score];

	if ($ax == $bx){ return 0; }
	return ($ax > $bx) ? -1 : 1;
}

function search_html_escape_terms($terms){
	$out = array();

	foreach($terms as $term){
		if (preg_match("/\s|,/", $term)){
			$out[] = '"'.HtmlSpecialChars($term).'"';
		}else{
			$out[] = HtmlSpecialChars($term);
		}
	}

	return $out;	
}

function search_pretty_terms($terms_html){

	if (count($terms_html) == 1){
		return array_pop($terms_html);
	}

	$last = array_pop($terms_html);

	return implode(', ', $terms_html)." and $last";
}


#
# do the search here...
#

$results = search_perform($searchterms);
$term_list = search_pretty_terms(search_html_escape_terms(search_split_terms($searchterms)));

 

The table name is 'Principals' and the field name I am trying to search is 'Principal'.  I am getting an error on the line:

 

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

 

The error is:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

Can anyone shed some light on this for me?

[jcbones]

Sure, but first you will have to do one other thing.

 

Top of Script

<?php
error_reporting(-1);
ini_set('display_errors',1);

 

Search in the Script and:

Replace:

$result = mysql_query($sql);

With:

$result = mysql_query($sql) or trigger_error(mysql_error());

 

Post back with the error.

[shanetastic]

Thanks!  The additional debugging information helped me find my mistake.  I had the table name and field name switched 

[xyph]

And you've learned a huge lesson in debugging.

 

Verify your functions return what you expect them to, and report if they don't.