How do I create search with option of AND / OR

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
peachiness
Forum Commoner
Posts: 41
Joined: Mon Oct 11, 2010 1:33 pm

How do I create search with option of AND / OR

Post by peachiness »

So my biggest concern is when I'm writing a search feature, what is the requirement to allow user to search for AUTHOR. AUTHOR and YEAR. or AUTHOR or YEAR?

If this is too complicated to explain, would you refer me to a tutorial on creating complex searches?

or, should I build a search within a searched result? (if so, how?)

This is what I have right now...how do I fix it so it can display results for search like 'AUTHOR 2009' instead of just for 'AUTHOR' or '2009'? (which is how it's working right now

Code: Select all

$query = "SELECT *
FROM publications
WHERE author1 LIKE '$trimmed'
OR year LIKE '$trimmed'";
Last edited by peachiness on Mon Feb 28, 2011 2:32 pm, edited 1 time in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How do I create search with option of AND / OR

Post by califdon »

You have to decide what you want to allow the user to do. Do you want to collect all 3 results on every search, or do you want to allow the user to choose which search method to use, as Google and other search engines do? The first thing you need to do is be aware of what kind of results you'll get from each of those 3 (or 4, if you do all of them) queries. If you search by Author OR Year, be aware that you may get a large number of records, since it will return all records that match either criterion. Do you really want to do that?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: How do I create search with option of AND / OR

Post by AbraCadaver »

At its simplest, you could have a checkbox that says "Results must contain all criteria". If it is checked then the code should use all ANDs, if not it can use all ORs. It will get complicated to allow multiple ANDs and ORs. To allow multiples you could have a "require" checkbox next to each criteria and group all of the checked ones into a (this AND that AND them) clause and the others into (thing OR thang OR stuff) clause.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
peachiness
Forum Commoner
Posts: 41
Joined: Mon Oct 11, 2010 1:33 pm

Re: How do I create search with option of AND / OR

Post by peachiness »

califdon: Thank you for the advice. I will add a choice for the user when they search.

AbraCadaver: I appreciate your tip. That is exactly what I will do!

What do you think of the current idea?

Code: Select all

$searchPieces = explode(" ",$search);
$year = NULL;
$searchTerm = "SELECT id,author1,keyword1,full reference,link FROM publications WHERE author1 LIKE '$search' AND keyword1 LIKE '$search'";
$searchTerm2 = "SELECT id,author1,keyword1,full reference,link FROM publications WHERE author1 LIKE '$search' OR keyword1 LIKE '$search'";


foreach($searchPieces as $val){
	$val = trim($val);
	if(strlen($val) == 4 && is_numeric($val)){
		$searchTerm = $searchTerm . " AND year >= '$val'";
		$searchTerm2 = $searchTerm . " AND year >= '$val'";
		break;
		}
}

$indexList = NULL;

$query2 = mysql_db_query("a2288820_data",$searchTerm);

while($row = mysql_fetch_row($query)){
	$authorList = explode(",",$row[5]); //This is the array of authors
	$keywordList = explode(",",$row[25]); //This is the array of keywords
	$indexList[] = $row[0]; //This adds the index to an array to check later on for results that "aren't as good."
	$fullReference = $row[3]; //This is the full reference string, not split into an array
	$link = $row[4]; //This is the pdf link
	/*
	//Use this to iterate through the data if you need to
	foreach($authorList as $val){
		if(trim(strtolower($val)) == trim((strtolower($search)))) {
		//result found
		
		}	
	}*/
}

$query2 = mysql_db_query("a2288820_data","SELECT id,authors,keywords FROM publications WHERE authors LIKE '$search' OR keywords LIKE '$search'");
while($row = mysql_fetch_row($query2)){
	if(array_search($row[0],$indexList) === FALSE){ //We haven't returned the results of this already
		$authorList = explode(",",$row[5]);
		$keywordList = explode(",",$row[25]);
		$fullReference = $row[3]; //This is the full reference string, not split into an array
		$link = $row[4]; //This is the pdf link
		
		if(strlen($row[0]) > 0){ 
		//Authors exists
		
		}
		else{
		//Keywords exists
		}
	
	}
}

/*
$months = {"january","february","march","april","may","june","july","august","september","october","november","december"};
$searchPieces = explode(" ",$search);
$monthNum = -1;
foreach($searchPieces as $val){
	if(($index = array_search(trim(strtolower($val)),$months)) !== FALSE){
	$monthNum = $index + 1;
	}
}*/


$query = mysql_db_query("a2288820_data","SELECT * FROM PUBLICATIONS");
while($row = mysql_fetch_row($query)){
$id = $row[0];

$authors = NULL;
for($i = 5; $i < 25; $i++){
	if(strlen($row[i]) > 0){
		$authors = $row[i] . ",";
	}
}

$authors[strlen($authors) - 1] = "";

$authors = $row[5] . "," . $row[6] // do this in a for loop
$keywords = $row[25] . "," . $row[26] // keywords

echo $authors;
echo $keywords;
//mysql_db_query("a2288820_data","UPDATE PUBLICATIONS SET authors = '$authors' AND keywords = '$keywords' WHERE id = '$id'");
}

?>
Post Reply