Searching for this OR that

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

Searching for this OR that

Post by peachiness »

When I try to search by author, it works. When I search by year, it works. But once I put both in the search, I get nothing.

How do I fix that?

Code: Select all

<?php


	$indexCheck = "yes";
	require('config.php');
	session_start();
	if(@$_SESSION['log'])
	@$userCheck = "admin";
	$sort1 = @$_GET['sort'];
	$search = @$_GET['search'];
	$sortBy = @$_GET['by'];

//advanced search output by AUTHOR

	if($sort1 == "Author")
	{
		$authors = array();
		$i = 0;


		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author1") or die(mysql_error());
		while((@@$row = mysql_fetch_array($result)))
		{
			if(@$row[5] != ""){
			if(!in_array(@$row[5],$authors)){
			$authors[$i] = @@$row[5];
			$i++;
			}
				}
		}
		
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author2");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[6] != ""){
			if(!in_array(@$row[6],$authors)){
			$authors[$i] = @@$row[6];
			$i++;
			}
				}
		}
		
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author3");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[7] != ""){
			if(!in_array(@$row[7],$authors)){
			$authors[$i] = @@$row[7];
			$i++;
			}
				}
		}
		
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author4");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}
		
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author5");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[9] != ""){
			if(!in_array(@$row[9],$authors)){
			$authors[$i] = @@$row[9];
			$i++;
			}
				}
		}

		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author6");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}

		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author7");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}

		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author8");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}

		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author9");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}

		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author10");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[10] != ""){
			if(!in_array(@$row[10],$authors)){
			$authors[$i] = @@$row[10];
			$i++;
			}
				}
		}

		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author11");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}

		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author12");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}

		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author13");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}

		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author14");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}

		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author15");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY author16");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[8] != ""){
			if(!in_array(@$row[8],$authors)){
			$authors[$i] = @@$row[8];
			$i++;
			}
				}
		}
				echo "<select onChange=\"javascript: if (this.value != 'NULL') { searchBase(this.value,'$sort1')}\">";
				echo "<option value=\"NULL\">Choose an author to search by </option>";
		for($b=0;$b<sizeOf($authors);$b++)
			{
				echo "<option value=\"$authors[$b]\">$authors[$b]</option>";
			}
				echo "</select>";
	}
//advanced search output by KEYWORD
	if($sort1 == "Keyword")
	{
		$keyword = array();
		$i = 0;
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY keyword1");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[24] != ""){
			if(!in_array(@$row[24],$keyword)){
			$keyword[$i] = @@$row[24];
			$i++;
			}
			}
		}
		
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY keyword2");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[25] != ""){
			if(!in_array(@$row[25],$keyword)){
			$keyword[$i] = @@$row[25];
			$i++;
			}
			}
		}
		
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY keyword3");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[26] != ""){
			if(!in_array(@$row[26],$keyword)){
			$keyword[$i] = @@$row[26];
			$i++;
			}
			}
		}
		
			$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY keyword3");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[27] != ""){
			if(!in_array(@$row[27],$keyword)){
			$keyword[$i] = @@$row[27];
			$i++;
			}
			}
		}
			$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY keyword3");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[28] != ""){
			if(!in_array(@$row[28],$keyword)){
			$keyword[$i] = @@$row[28];
			$i++;
			}
			}
		}
			$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY keyword3");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[29] != ""){
			if(!in_array(@$row[29],$keyword)){
			$keyword[$i] = @@$row[29];
			$i++;
			}
			}
		}
			$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY keyword3");
		while((@$row = mysql_fetch_array($result)))
		{
			if(@$row[30] != ""){
			if(!in_array(@$row[30],$keyword)){
			$keyword[$i] = @@$row[30];
			$i++;
			}
			}
		}
		
		
				echo "<select onChange=\"javascript: if (this.value != 'NULL') { searchBase(this.value,'$sort1')}\">";
				echo "<option value=\"NULL\">Choose a keyword to search by </option>";
		for($b=0;$b<sizeOf($keyword);$b++)
			{
				echo "<option value=\"$keyword[$b]\">$keyword[$b]</option>";
			}
				echo "</select>";
	}
//advanced search output by DATE 
	if($sort1 == "Date")
	{
		$d2 = array();
		$ia = 0;
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY year");
				echo "<select onChange=\"javascript: if (this.value != 'NULL') { searchBase(this.value,'$sort1')}\">";
				echo "<option value=\"NULL\">Choose a year to search by </option>";
		while((@$row = mysql_fetch_array($result)))
		{
			if((@$row[2] != "")){
			if(!in_array(@$row[2],$d2)){
			$temp = @@$row[2];
			$d2[$ia] = $temp;
			$ia++;
			}
			}
		}
		rsort($d2);		
		for($ia=0;$ia<sizeOf($d2);$ia++)
				echo "<option value=\"$d2[$ia]\">".$d2[$ia]."</option>";
				echo "</select>";
	}
	//advanced search output by TYPE
	if($sort1 == "Type")
	{
		$d2 = array();
		$ia = 0;
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY type");
				echo "<select onChange=\"javascript: if (this.value != 'NULL') { searchBase(this.value,'$sort1')}\">";
				echo "<option value=\"NULL\">Choose a type to search by </option>";
		while((@$row = mysql_fetch_array($result)))
		{
			if((@$row[1] != "")){
			if(!in_array(@$row[1],$d2)){
			$temp = @@$row[1];
			$d2[$ia] = $temp;
			$ia++;
			}
			}
		}
		
		for($ia=0;$ia<sizeOf($d2);$ia++)
		echo "<option value=\"$d2[$ia]\">".$d2[$ia]."</option>";
				echo "</select>";
	}
//advanced search output by FULL REF THAT IS NOT WORKING		
	if($sort1 == "Standard Reference")
	{
		$d2 = array();
		$ia = 0;
		$result = mysql_db_query("a2288820_data","SELECT * FROM publications ORDER BY stadRef");
				echo "<select onChange=\"javascript: if (this.value != 'NULL') { searchBase(this.value,'$sort1')}\">";
				echo "<option value=\"NULL\">Choose a Type to search by </option>";
		while((@$row = mysql_fetch_array($result)))
		{
			if((@$row[12] != "")){
			if(!in_array(@$row[12],$d2)){
			$temp = @@$row[12];
			$d2[$ia] = $temp;
			$ia++;
			}
			}
		}
		
		for($ia=0;$ia<sizeOf($d2);$ia++)
		echo "<option value=\"$d2[$ia]\">".$d2[$ia]."</option>";
				echo "</select>";
	}
	

	function echoSearchResultsTableHeader() {
		echo "<tr id=\"topRow\">";
		echo "<th>ID</th>";
echo "<th>Primary author</th>";
echo "<th>Secondary author</th>";
		echo "<th width=65%>Standard Reference</th>";
		echo "<th>Link</th>";
		echo "</tr>";
	}

//construct query
	function echoSearchResultsTableRows($search,$sortField,&$startIndex) {

		$trimmed=trim($search);

		$result = mysql_query("select * from publications where $sortField like \"%$trimmed%\" order by $sortField");
		echoMysqlSearchResultsTableRows($result,$startIndex);
	}


	function echoMysqlSearchResultsTableRows($result,&$startIndex) {
		$index = $startIndex;
		while(@$row = mysql_fetch_array($result))
		{
			echo "<tr>\n";
		if(@$userCheck == "admin"){
		echo "<td id=\"noLinkTd\"><a href=\"?page=edit&cID=".$row[0]."\">[Edit] ".$z."</a><br />";
		echo " <a href=\"javascript://\" onClick=\"deleteRow(".$row[0].",".$z.")\">[Remove]</a>";
		echo "</td>\n";
		}
		else
		echo "<td><b>".$row['id']."</b></td>\n";
		echo "<td align=center>".$row[5]."&nbsp; </td>\n";
		echo "<td align=center>".$row[6]."&nbsp; </td>\n";
		echo "<td>".$row[35]."&nbsp; </td>\n";
		echo "<td><a href=\"".$row[36]."\">".$row[36]."</a>&nbsp; </td>\n";
		echo "</tr>\n";
			$index++;
		}
		$startIndex = $index;
	}

			if(($search != null))
	{
		echo "<div id=\"sfor\">";
		echo "Searching for... ";
echo "<b>".$search."</b>";
echo "<div id=\"back\">";
			echo "<a href='http://agecon2.tamu.edu/people/faculty/rister-edward/dev/?page=view';>Return to full database</a>";
	


echo "</div>";
echo "</div>";
		
		echo "<div class=\"database\">";
		echo "<table class=\"sortable2\" cellspacing=\"0\" id=\"dataTable\">";

		echoSearchResultsTableHeader();

		$index = 0;
		
		if($sortBy == "Date")
		{
			echoSearchResultsTableRows($search,"year",$index);
		}
				
		if($sortBy == "Standard Reference")
		{
			echoSearchResultsTableRows($search,"stadRef",$index);
		}
		
		if($sortBy == "Type")
		{
			echoSearchResultsTableRows($search,"type",$index);
		}
		
		if($sortBy == "Keyword")
		{
			echoSearchResultsTableRows($search,"keyword1",$index);
			echoSearchResultsTableRows($search,"keyword2",$index);
			echoSearchResultsTableRows($search,"keyword3",$index);
			echoSearchResultsTableRows($search,"keyword4",$index);
		}
		
		if($sortBy == "Author")
		{
			echoSearchResultsTableRows($search,"author1",$index);
			echoSearchResultsTableRows($search,"author2",$index);
			echoSearchResultsTableRows($search,"author3",$index);
		}
		
		if($sortBy == "all")
		{
			echoSearchResultsTableRows($search,"author1",$index);
			echoSearchResultsTableRows($search,"author2",$index);
			echoSearchResultsTableRows($search,"author3",$index);
			echoSearchResultsTableRows($search,"keyword1",$index);
			echoSearchResultsTableRows($search,"keyword2",$index);
			echoSearchResultsTableRows($search,"keyword3",$index);
			echoSearchResultsTableRows($search,"keyword4",$index);
			echoSearchResultsTableRows($search,"month",$index);
			echoSearchResultsTableRows($search,"year",$index);
			echoSearchResultsTableRows($search,"date",$index);
		}

	
		echo "</table>";
		echo "</div>";



	}
	
	mysql_close();

 ?>


User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Searching for this OR that

Post by Darhazer »

normalize your database first. Having 16 author columns is not good, and performing 16 table scans is madness
peachiness
Forum Commoner
Posts: 41
Joined: Mon Oct 11, 2010 1:33 pm

Re: Searching for this OR that

Post by peachiness »

Darhazer wrote:normalize your database first. Having 16 author columns is not good, and performing 16 table scans is madness
How do I go about normalizing a publication that has up to 20 authors and keywords?

My current structure is all these fields in one table

[text] CREATE TABLE `a2288820_data`.`pdf`(
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`type` VARCHAR( 100 ) NOT NULL ,
`year` VARCHAR( 4 ) NOT NULL ,
`month` VARCHAR( 16 ) NOT NULL ,
`date` VARCHAR( 2 ) NOT NULL ,
`author1` VARCHAR( 100 ) NOT NULL ,
`author2` VARCHAR( 100 ) NOT NULL ,
`author3` VARCHAR( 100 ) NOT NULL ,
`author4` VARCHAR( 100 ) NOT NULL ,
`author5` VARCHAR( 100 ) NOT NULL ,
`author6` VARCHAR( 100 ) NOT NULL ,
`author7` VARCHAR( 100 ) NOT NULL ,
`author8` VARCHAR( 100 ) NOT NULL ,
`author9` VARCHAR( 100 ) NOT NULL ,
`author10` VARCHAR( 100 ) NOT NULL ,
`author11` VARCHAR( 100 ) NOT NULL ,
`author12` VARCHAR( 100 ) NOT NULL ,
`author13` VARCHAR( 100 ) NOT NULL ,
`author14` VARCHAR( 100 ) NOT NULL ,
`author15` VARCHAR( 100 ) NOT NULL ,
`author16` VARCHAR( 100 ) NOT NULL ,
`author17` VARCHAR( 100 ) NOT NULL ,
`author18` VARCHAR( 100 ) NOT NULL ,
`author19` VARCHAR( 100 ) NOT NULL ,
`author20` VARCHAR( 100 ) NOT NULL ,
`keyword1` VARCHAR( 100 ) NOT NULL ,
`keyword2` VARCHAR( 100 ) NOT NULL ,
`keyword3` VARCHAR( 100 ) NOT NULL ,
`keyword4` VARCHAR( 100 ) NOT NULL ,
`keyword5` VARCHAR( 100 ) NOT NULL ,
`keyword6` VARCHAR( 100 ) NOT NULL ,
`keyword7` VARCHAR( 100 ) NOT NULL ,
`keyword8` VARCHAR( 100 ) NOT NULL ,
`keyword9` VARCHAR( 100 ) NOT NULL ,
`keyword10` VARCHAR( 100 ) NOT NULL ,
`full reference` text NOT NULL ,
`link` VARCHAR( 22 ) NOT NULL[/text]

I am thinking of

AUTHOR TABLE
authorID
First
Last

KEYWORD TABLE
keywordID
keywords

ASSOCIATION TABLE
ID
authorID
keywordID
Year
Month
Datnum
Link


However, won't that only allow me 1author and 1 keyword per row?
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Searching for this OR that

Post by Darhazer »

table publications:
`id`
`type`
`year`
`month`
`date`
`full reference`
`link`

table authors
id
first_name
last_name

table keywords
id
keyword

table publication_x_author
publication_id
author_id
Note - this is M:M relation - there is one record for each author of the publication. Same for keywords
table publication_x_keyword
publication_id
keyword_id
peachiness
Forum Commoner
Posts: 41
Joined: Mon Oct 11, 2010 1:33 pm

Re: Searching for this OR that

Post by peachiness »

do you think this structure will work with the following query?

Code: Select all

<form action='search.php' method='GET'>
<input type='text' size='50' name='search'><input type='submit' name='submit' value='search'>
</form>

<?php

	$indexCheck = "yes";
	require('config.php');
	session_start();
	if(@$_SESSION['log'])
	@$userCheck = "admin";
	$sort1 = @$_GET['sort'];
	$search = @$_GET['search'];
	$sortBy = @$_GET['by'];


$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'");
}

?>
Darhazer wrote:table publications:
`id`
`type`
`year`
`month`
`date`
`full reference`
`link`

table authors
id
first_name
last_name

table keywords
id
keyword

table publication_x_author
publication_id
author_id
Note - this is M:M relation - there is one record for each author of the publication. Same for keywords
table publication_x_keyword
publication_id
keyword_id
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Searching for this OR that

Post by Darhazer »

You have to join the new tables:

Code: Select all

SELECT publications.id,author,keyword,full_reference,link
FROM publications
INNER JOIN publication_x_author ON publication.id = pubication_x_author.publication_id
INNER JOIN publication_x_keyword ON publication.id = pubication_x_author.publication_id
INNER JOIN authors on authors.id = publication_x_author.author_id
INNER JOIN keywords on keywords.id = publication_x_keyrod.keyword_id
WHERE author LIKE '$search'
OR keyword LIKE '$search'";
Unfortunatelly for large publications table it would be swall, but it can be optimized using UNION between 2 queries - one searching
for author and the other one searching for keyword
peachiness
Forum Commoner
Posts: 41
Joined: Mon Oct 11, 2010 1:33 pm

Re: Searching for this OR that

Post by peachiness »

Thanks!

I'm trying to test this ...but i keep getting a blank page

Code: Select all


<?php
	$indexCheck = "yes";
	require('config.php');
	session_start();
	if(@$_SESSION['log'])
	@$userCheck = "admin";
	$sort1 = @$_GET['sort'];
	$search = @$_GET['search'];
	$sortBy = @$_GET['by'];

  //-query  the database table

  $sql="SELECT publications.id,author,keyword,full_reference,link
FROM publications
INNER JOIN publication_x_author ON publication.id = pubication_x_author.publication_id
INNER JOIN publication_x_keyword ON publication.id = pubication_x_author.publication_id
INNER JOIN authors on authors.id = publication_x_author.author_id
INNER JOIN keywords on keywords.id = publication_x_keyword.keyword_id
WHERE author LIKE '$search'
OR keyword LIKE '$search'";

//construct query
	function echoSearchResultsTableRows($search,$sortField,&$startIndex) {

		$trimmed=trim($search);

		$result = mysql_query("select * from publications where $sortField like \"%$trimmed%\" order by $sortField");
		echoMysqlSearchResultsTableRows($result,$startIndex);
	}


	function echoMysqlSearchResultsTableRows($result,&$startIndex) {
		$index = $startIndex;
		while(@$row = mysql_fetch_array($result))
		{
			echo "<tr>\n";
		if(@$userCheck == "admin"){
		echo "<td id=\"noLinkTd\"><a href=\"?page=edit&cID=".$row[0]."\">[Edit] ".$z."</a><br />";
		echo " <a href=\"javascript://\" onClick=\"deleteRow(".$row[0].",".$z.")\">[Remove]</a>";
		echo "</td>\n";
		}
		else
		echo "<td><b>".$row['id']."</b></td>\n";
		echo "<td align=center>".$row[5]."&nbsp; </td>\n";
		echo "<td align=center>".$row[6]."&nbsp; </td>\n";
		echo "<td>".$row[35]."&nbsp; </td>\n";
		echo "<td><a href=\"".$row[36]."\">".$row[36]."</a>&nbsp; </td>\n";
		echo "</tr>\n";
			$index++;
		}
		$startIndex = $index;
	}

			if(($search != null))
	{
		echo "<div id=\"sfor\">";
		echo "Searching for... ";
echo "<b>".$search."</b>";
echo "<div id=\"back\">";
			echo "<a href='http://';>Return to full database</a>";
	


echo "</div>";
echo "</div>";
?>


what do you think the problem is?
Darhazer wrote:You have to join the new tables:

Code: Select all

SELECT publications.id,author,keyword,full_reference,link
FROM publications
INNER JOIN publication_x_author ON publication.id = pubication_x_author.publication_id
INNER JOIN publication_x_keyword ON publication.id = pubication_x_author.publication_id
INNER JOIN authors on authors.id = publication_x_author.author_id
INNER JOIN keywords on keywords.id = publication_x_keyrod.keyword_id
WHERE author LIKE '$search'
OR keyword LIKE '$search'";
Unfortunatelly for large publications table it would be swall, but it can be optimized using UNION between 2 queries - one searching
for author and the other one searching for keyword
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Searching for this OR that

Post by Darhazer »

Actually you are still using your old query and $sql is never used.
And when you perform queries, check the result of the mysql_query function - if it is false, the query have failed
(you can check the error using mysql_error() function)
peachiness
Forum Commoner
Posts: 41
Joined: Mon Oct 11, 2010 1:33 pm

Re: Searching for this OR that

Post by peachiness »

I am trying this query but my search always returns zero results. What do you think could be the problem?

Code: Select all


<?php

  // Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }




//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost", "", ""); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db ("a2288820_data") or die("Unable to select database"); //select which database we're using

// Build SQL Query  


$query = "SELECT publications.id,author,keyword,full_reference,link
FROM publications
INNER JOIN publication_x_author ON publication.id = pubication_x_author.publication_id
INNER JOIN publication_x_keyword ON publication.id = pubication_x_author.publication_id
INNER JOIN authors on authors.id = publication_x_author.author_id
INNER JOIN keywords on keywords.id = publication_x_keyrod.keyword_id
WHERE author LIKE '$trimmed'
OR keyword LIKE '$trimmed'";
// EDIT HERE and specify your table and field names for the SQL query

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";

// google
 echo "<p><a href=\"http://www.google.com/search?q=" 
  . $trimmed . "\" target=\"_blank\" title=\"Look up 
  " . $trimmed . " on Google\">Click here</a> to try the 
  search on google</p>";
  }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";

// begin to show results set
echo "Results";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {

  $title = $row["author1"];

  echo "$count.)&nbsp;$title" ;
  $count++ ;
  }

$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&nbsp&nbsp;";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
  
?>


THIS works

Code: Select all

<?php

  // Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }




//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost", "", ""); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db ("a2288820_data") or die("Unable to select database"); //select which database we're using

// Build SQL Query  


$query = "SELECT * FROM publications WHERE year LIKE  '$trimmed' OR author1 LIKE '$trimmed'";
// EDIT HERE and specify your table and field names for the SQL query

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";

// google
 echo "<p><a href=\"http://www.google.com/search?q=" 
  . $trimmed . "\" target=\"_blank\" title=\"Look up 
  " . $trimmed . " on Google\">Click here</a> to try the 
  search on google</p>";
  }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";

// begin to show results set
echo "Results";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {

  $title = $row["author1"];

  echo "$count.)&nbsp;$title" ;
  $count++ ;
  }

$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&nbsp&nbsp;";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
  
?>


Darhazer wrote:Actually you are still using your old query and $sql is never used.
And when you perform queries, check the result of the mysql_query function - if it is false, the query have failed
(you can check the error using mysql_error() function)
Post Reply