<?php
$author_id = $_GET['author'];
$subject = $_GET['subject'];
$pubtype = $_GET['type'];
$year = $_GET['year'];
$college = $_GET['college'];
$department = $_GET['department'];
$keyword = $_GET['keyword'];
$keyword = mysql_real_escape_string($keyword);
$subject = mysql_real_escape_string($subject);
$year = mysql_real_escape_string($year);
//If any of the fields were left blank, we will assign % to the variable to use as wildcards in the SQL queries
if ($subject == "")
$subject = "%";
if ($year == "")
$year = "%";
if ($keyword == "" || $keyword == "*")
$keyword = "%";
if ($author_id == "blank")
{
$author_id = "%";
$firstname = "%";
$lastname = "%";
}
else
{
//get author specified in search form
$author_row = mysql_query("SELECT * FROM authors WHERE author_id='$author_id'") or die (mysql_error());
while ($row = mysql_fetch_array($author_row))
{
$firstname = $row['firstname'];
$lastname = $row['lastname'];
$middle_initial = $row['middle_initial'];
}
}
if ($college == "blank")
{
$college = "%";
$c_id = "%";
}
else
{
//get college specified in search form
$college_query = mysql_query("SELECT * FROM college WHERE collegename = '$college'") or die (mysql_error());
$college_row = mysql_fetch_array($college_query);
$c_id = $college_row['college_id'];
}
if ($department == "blank")
{
$department = "%";
$d_id = "%";
}
else
{
//get department specified in search form
$department_query = mysql_query("SELECT * FROM department WHERE departmentname = '$department'") or die (mysql_error());
$department_row = mysql_fetch_array($department_query);
$d_id = $department_row['department_id'];
}
switch ($pubtype)
{
//if the user left the publication-type blank in the search form, then we will search all publication types
case "blank":
echo '<table align="left" width="100%"><tr><td style="background-color:#CCCCFF;"><b>Books:</b></td></tr><tr><td>';
$result = book_search($firstname, $lastname, $year, $c_id, $d_id, $keyword); //this function is defined at the bottom of document
display_result($result, 1); //this function is defined at the bottom of document
echo "<br/><br/></td></tr>";
echo '<tr><td style="background-color:#CCCCFF;"><b>Book Chapters:</b></td></tr><tr><td>';
$result = book_chapter_search($firstname, $lastname, $year, $c_id, $d_id, $keyword);
display_result($result, 2);
echo "<br/><br/></td></tr>";
echo '<tr><td style="background-color:#CCCCFF;"><b>Conference Proceedings:</b></td></tr><tr><td>';
$result = conference_search($firstname, $lastname, $year, $c_id, $d_id, $keyword);
display_result($result, 4);
echo "<br/><br/></td></tr>";
echo '<tr><td style="background-color:#CCCCFF;"><b>Journal Articles:</b></td></tr><tr><td>';
$result = journal_search($firstname, $lastname, $year, $c_id, $d_id, $keyword);
display_result($result, 3);
echo "<br/><br/></td></tr>";
echo '<tr><td style="background-color:#CCCCFF;"><b>Other Publications:</b></td></tr><tr><td>';
$result = other_search($firstname, $lastname, $year, $c_id, $d_id, $keyword);
display_result($result, 5);
echo "</td></tr></table>";
break;
//search for publication-type = book
case "Book":
echo '<table width="100%"><tr><td style="background-color:#CCCCFF;"><b>Books:</b></td></tr><tr><td>';
$result = book_search($firstname, $lastname, $year, $c_id, $d_id, $keyword);
display_result($result, 1);
echo "</td></tr></table>";
break;
//search for publication-type = book chapter
case "Book Chapter":
echo '<table width="100%"><tr><td style="background-color:#CCCCFF;"><b>Book Chapters:</b></td></tr><tr><td>';
$result = book_chapter_search($firstname, $lastname, $year, $c_id, $d_id, $keyword);
display_result($result, 2);
echo "</td></tr></table>";
break;
//search for publication-type = journal article
case "Journal Article":
echo '<table width="100%"><tr><td style="background-color:#CCCCFF;"><b>Journal Articles:</b></td></tr><tr><td>';
$result = journal_search($firstname, $lastname, $year, $c_id, $d_id, $keyword);
display_result($result, 3);
echo "</td></tr></table>";
break;
case "Conference":
echo '<table width="100%"><tr><td style="background-color:#CCCCFF;"><b>Conference Proceedings:</b></td></tr><tr><td>';
$result = conference_search($firstname, $lastname, $year, $c_id, $d_id, $keyword);
display_result($result, 4);
echo "</td></tr></table>";
break;
case "Other";
echo '<table width="100%"><tr><td style="background-color:#CCCCFF;"><b>Other Publications:</b></td></tr><tr><td>';
$result = other_search($firstname, $lastname, $year, $c_id, $d_id, $keyword);
display_result($result, 5);
echo "</td></tr></table>";
break;
}
?>
<?php
//this function searches for journal articles
function journal_search($firstname, $lastname, $year, $c_id, $d_id, $keyword)
{
$result_local = mysql_query("
(SELECT * FROM
(SELECT DISTINCT publication_id, firstname, lastname, middle_initial, faculty, author_id, subject, type, title, journal_articles_id, article_title, journal_title, volume_number, issue_number, pages, doi, year, url FROM
(SELECT * FROM
(SELECT * FROM
(SELECT * FROM
(SELECT temp2.firstname, temp2.lastname, temp2.middle_initial, temp2.faculty, temp2.author_id, temp2.publication_id FROM
(SELECT temp2.firstname, temp2.lastname, temp2.middle_initial, temp2.faculty, temp2.author_id, temp2.publication_id FROM
(SELECT authors.author_id, authors.lastname, authors.firstname, authors.middle_initial, authors.faculty, authors.publication_id FROM
(SELECT * FROM faculty WHERE college_id LIKE '$c_id' AND department_id LIKE '$d_id' )
AS temp1 JOIN authors WHERE temp1.firstname = authors.firstname AND temp1.lastname = authors.lastname)
AS temp2 WHERE temp2.firstname LIKE '$firstname' AND temp2.lastname LIKE '$lastname')
AS temp1, authors AS temp2 WHERE temp2.publication_id = temp1.publication_id)
AS temp JOIN publications USING (publication_id) WHERE temp.publication_id = publications.publication_id)
AS temp JOIN journal_articles USING (publication_id) WHERE temp.publication_id = journal_articles.publication_id)
AS temp WHERE temp.year LIKE '$year')
AS temp4)
AS temp5 WHERE year LIKE '%$keyword%' OR journal_title LIKE '%$keyword%' OR article_title LIKE '%$keyword%' OR firstname LIKE '%$keyword%' OR lastname LIKE '%$keyword%')
") or die (mysql_error());
return $result_local;
}
/* mysql_query("CREATE TABLE backup(
publication_id INT(11),
firstname VARCHAR(20),
lastname VARCHAR(20),
middle_initial VARCHAR(5),
faculty TINYINT(1),
author_id INT(20),
subject VARCHAR(20),
type VARCHAR(20),
title VARCHAR(20),
books_id INT(11),
book_title VARCHAR(50),
location VARCHAR(50),
publisher VARCHAR(50),
year INT(4),
url VARCHAR(255)
)") or die (mysql_error());*/
//this function searches for books
function book_search($firstname, $lastname, $year, $c_id, $d_id, $keyword)
{
//I did SELECT DISTINCT as a work-around - without it, some records would be duplicated due to this query, depending on conditions
$result_local = mysql_query("
(SELECT * FROM
(SELECT DISTINCT publication_id, firstname, lastname, middle_initial, faculty, author_id, subject, type, title, books_id, book_title, location, publisher, year, url FROM
(SELECT * FROM
(SELECT * FROM
(SELECT * FROM
(SELECT temp2.firstname, temp2.lastname, temp2.middle_initial, temp2.faculty, temp2.author_id, temp2.publication_id FROM
(SELECT temp2.firstname, temp2.lastname, temp2.middle_initial, temp2.faculty, temp2.author_id, temp2.publication_id FROM
(SELECT authors.author_id, authors.lastname, authors.firstname, authors.middle_initial, authors.faculty, authors.publication_id FROM
(SELECT * FROM faculty WHERE college_id LIKE '$c_id' AND department_id LIKE '$d_id' )
AS temp1 JOIN authors WHERE temp1.firstname = authors.firstname AND temp1.lastname = authors.lastname)
AS temp2 WHERE temp2.firstname LIKE '$firstname' AND temp2.lastname LIKE '$lastname')
AS temp1, authors AS temp2 WHERE temp2.publication_id = temp1.publication_id)
AS temp JOIN publications USING (publication_id) WHERE temp.publication_id = publications.publication_id)
AS temp JOIN books USING (publication_id) WHERE temp.publication_id = books.publication_id)
AS temp WHERE temp.year LIKE '$year')
AS temp4)
AS temp5 WHERE book_title LIKE '%$keyword%' OR location LIKE '%$keyword%' OR year LIKE '%$keyword%' OR publisher LIKE '%$keyword%' OR firstname LIKE '%$keyword%' OR lastname LIKE '%$keyword%')
") or die (mysql_error());
return $result_local;
}
//this function searches for book chapters
function book_chapter_search($firstname, $lastname, $year, $c_id, $d_id, $keyword)
{
$result_local = mysql_query("
(SELECT * FROM
(SELECT DISTINCT publication_id, firstname, lastname, middle_initial, faculty, author_id, subject, type, title, book_chapters_id, book_title, chapter_title, pages, location, publisher, year, url FROM
(SELECT * FROM
(SELECT * FROM
(SELECT * FROM
(SELECT temp2.firstname, temp2.lastname, temp2.middle_initial, temp2.faculty, temp2.author_id, temp2.publication_id FROM
(SELECT temp2.firstname, temp2.lastname, temp2.middle_initial, temp2.faculty, temp2.author_id, temp2.publication_id FROM
(SELECT authors.author_id, authors.lastname, authors.firstname, authors.middle_initial, authors.faculty, authors.publication_id FROM
(SELECT * FROM faculty WHERE college_id LIKE '$c_id' AND department_id LIKE '$d_id' )
AS temp1 JOIN authors WHERE temp1.firstname = authors.firstname AND temp1.lastname = authors.lastname)
AS temp2 WHERE temp2.firstname LIKE '$firstname' AND temp2.lastname LIKE '$lastname')
AS temp1, authors AS temp2 WHERE temp2.publication_id = temp1.publication_id)
AS temp JOIN publications USING (publication_id) WHERE temp.publication_id = publications.publication_id)
AS temp JOIN book_chapters USING (publication_id) WHERE temp.publication_id = book_chapters.publication_id)
AS temp WHERE temp.year LIKE '$year')
AS temp4)
AS temp5 WHERE chapter_title LIKE '%$keyword%' OR book_title LIKE '%$keyword%' OR location LIKE '%$keyword%' OR publisher LIKE '%$keyword%' OR year LIKE '%$keyword%' OR firstname LIKE '%$keyword%' OR lastname LIKE '%$keyword%')
") or die (mysql_error());
return $result_local;
}
function conference_search($firstname, $lastname, $year, $c_id, $d_id, $keyword)
{
$result_local = mysql_query("
(SELECT * FROM
(SELECT DISTINCT publication_id, firstname, lastname, middle_initial, faculty, author_id, subject, type, title, conference_id, presentation_title, conference, proceedings, pages, date, location, url FROM
(SELECT * FROM
(SELECT * FROM
(SELECT * FROM
(SELECT temp2.firstname, temp2.lastname, temp2.middle_initial, temp2.faculty, temp2.author_id, temp2.publication_id FROM
(SELECT temp2.firstname, temp2.lastname, temp2.middle_initial, temp2.faculty, temp2.author_id, temp2.publication_id FROM
(SELECT authors.author_id, authors.lastname, authors.firstname, authors.middle_initial, authors.faculty, authors.publication_id FROM
(SELECT * FROM faculty WHERE college_id LIKE '$c_id' AND department_id LIKE '$d_id' )
AS temp1 JOIN authors WHERE temp1.firstname = authors.firstname AND temp1.lastname = authors.lastname)
AS temp2 WHERE temp2.firstname LIKE '$firstname' AND temp2.lastname LIKE '$lastname')
AS temp1, authors AS temp2 WHERE temp2.publication_id = temp1.publication_id)
AS temp JOIN publications USING (publication_id) WHERE temp.publication_id = publications.publication_id)
AS temp JOIN conferences USING (publication_id) WHERE temp.publication_id = conferences.publication_id)
AS temp WHERE temp.date LIKE '$year')
AS temp4)
AS temp5 WHERE date LIKE '%$keyword%' OR presentation_title LIKE '%$keyword%' OR conference LIKE '%$keyword%' OR proceedings LIKE '%keyword%' OR firstname LIKE '%$keyword%' OR lastname LIKE '%$keyword%')
") or die (mysql_error());
return $result_local;
}
function other_search($firstname, $lastname, $year, $c_id, $d_id, $keyword)
{
$result_local = mysql_query("
(SELECT * FROM
(SELECT DISTINCT publication_id, firstname, lastname, middle_initial, faculty, author_id, subject, type, title, other_id, other_title, publisher, year, location, url FROM
(SELECT * FROM
(SELECT * FROM
(SELECT * FROM
(SELECT temp2.firstname, temp2.lastname, temp2.middle_initial, temp2.faculty, temp2.author_id, temp2.publication_id FROM
(SELECT temp2.firstname, temp2.lastname, temp2.middle_initial, temp2.faculty, temp2.author_id, temp2.publication_id FROM
(SELECT authors.author_id, authors.lastname, authors.firstname, authors.middle_initial, authors.faculty, authors.publication_id FROM
(SELECT * FROM faculty WHERE college_id LIKE '$c_id' AND department_id LIKE '$d_id' )
AS temp1 JOIN authors WHERE temp1.firstname = authors.firstname AND temp1.lastname = authors.lastname)
AS temp2 WHERE temp2.firstname LIKE '$firstname' AND temp2.lastname LIKE '$lastname')
AS temp1, authors AS temp2 WHERE temp2.publication_id = temp1.publication_id)
AS temp JOIN publications USING (publication_id) WHERE temp.publication_id = publications.publication_id)
AS temp JOIN other USING (publication_id) WHERE temp.publication_id = other.publication_id)
AS temp WHERE temp.year LIKE '$year')
AS temp4)
AS temp5 WHERE year LIKE '%$keyword%' OR other_title LIKE '%$keyword%' OR location LIKE '%$keyword%' OR publisher LIKE '%keyword%' OR firstname LIKE '%$keyword%' OR lastname LIKE '%$keyword%')
") or die (mysql_error());
return $result_local;
}
//this function prints the results of the search queries
function display_result($result, $type)
{
$p_id_array = array(); //this array will hold publication_ID's
$editors_firstname_array = array(); //this array will hold the first name of editors, and will correspond to...
$editors_lastname_array = array(); //...the last name of editors, held in this array
while ($row = mysql_fetch_array($result))
{
$p_id_array[] = $row['publication_id']; //insert all publication_ID's into $p_id_array
}
$p_id_array = array_unique($p_id_array); //remove any duplicate entries from $p_id_array
//Re-point to the beginning of the $result array
if (mysql_num_rows($result) >= 1)
mysql_data_seek($result, 0);
if (mysql_num_rows($result) == 0) //If there are no rows in this array, then the search queries returned no results
echo "<br/>No results.";
$result_faculty = mysql_query("SELECT * FROM faculty"); //Create an array of the faculty members. This will be used to compare against...
//...authors to see if they are faculty members.
echo "<p>";
foreach ($p_id_array as $array_element) //this loop will occur for every unique publication_id
{
while ($row = mysql_fetch_array($result))
{
mysql_data_seek($result_faculty, 0); //point to the beginning of array of faculty members
if ($row['publication_id'] == $array_element)
{
//If this author is a faculty member, print the name in bold.
while ($row_faculty = mysql_fetch_array($result_faculty))
{
if ($row_faculty['firstname'] == $row['firstname'] &&
$row_faculty['lastname'] == $row['lastname'] &&
$row_faculty['middle_initial'] == $row['middle_initial'])
{
echo "<b>" . $row['firstname'] . " " . $row['middle_initial'] . " " .$row['lastname'] . "</b>, ";
$bold = true;
//mysql_data_seek($result_faculty, 0);
break;
}
else
$bold = false;
}
//If the author is NOT a faculty member, do not print the name in bold
if ($bold == false)
echo $row['firstname'] . " " . $row['middle_initial'] . " " .$row['lastname'] . ", ";
if ($type == 1) //book
{
$information_array = array($row['year'], $row['title'], $row['location'], $row['publisher'], $row['url']);
}
if ($type == 2) //book chapter
{
$information_array = array($row['year'], $row['title'], $row['book_title'], $row['pages'], $row['location'], $row['publisher'], $row['url']);
}
if ($type == 3) //journal article
{
$information_array = array($row['year'], $row['title'], $row['journal_title'], $row['volume_number'], $row['issue_number'], $row['pages'], $row['url']);
}
if ($type == 4)
{
$information_array = array($row['date'], $row['title'], $row['presentation_title'], $row['conference'], $row['proceedings'], $row['pages'], $row['location'], $row['url']);
}
if ($type == 5)
{
$information_array = array($row['year'], $row['title'], $row['other_title'], $row['publisher'], $row['location'], $row['url']);
}
}
}
$editors_result = mysql_query("SELECT * FROM editors WHERE publication_id = '$array_element'");
while ($editor_row = mysql_fetch_array($editors_result))
{
$editors_firstname_array[] = $editor_row['firstname'];
$editors_lastname_array[] = $editor_row['lastname'];
}
if ($type == 1) //book
{
echo $information_array[0]. ". <i>". $information_array[1] . '</i>. '. $information_array[2] . ": " . $information_array[3]. ".<br/>";
}
if ($type == 2) //book_chapter
{
echo $information_array[0]. '. "'. $information_array[1]. '." In <i>' . $information_array[2] . '</i>';
if (sizeof($editors_firstname_array) > 0)
echo ' edited by ';
for ($i = 0; $i <= sizeof($editors_firstname_array); $i++)
{
if ($editors_firstname_array[$i] != "")
echo $editors_firstname_array[$i] . " " . $editors_lastname_array[$i];
if ($i < (sizeof($editors_firstname_array) - 1))
echo " and ";
}
$editors_firstname_array = array();
$editors_lastname_array = array();
echo ". " . $information_array[4]. ": ". $information_array[5];
echo "<a href=\"http://" . $information_array[6] . "\">" . $information_array[6] . "</a><br/>";
}
if ($type == 3) //journal article
{
echo $information_array[0]. ". " .$information_array[1] . ". " . "<i>" . $information_array[2]. "</i> ";
if ($information_array[3] != 0)
echo $information_array[3]. ", ";
if ($informatioin_array[4] != 0)
echo " (" . $information_array[4] . "): ";
if ($information_array[5] != "")
echo $information_array[5];
if ($information_array[6] != "")
echo ", " . "<a href=\"http://" . $information_array[6] . "\">" . $information_array[6] . "</a>";
echo "<br/>";
}
if ($type == 4)
{
echo $information_array[2];
if ($information_array[4] != "")
echo ", <i>" . $information_array[4]. "</i>";
if ($information_array[3] != "")
echo ", " . $information_array[3];
if ($information_array[6] != "")
echo ", " . $information_array[6];
if ($information_array[5] != "")
echo ", pp. " . $information_array[5];
if ($information_array[7] != "")
echo ", " . "<a href=\"http://" . $information_array[7] . "\">" . $information_array[7] . "</a>";
echo "<br/>";
}
if ($type == 5)
{
echo $information_array[2];
if ($information_array[3] != "")
echo ", " . $information_array[3];
if ($information_array[4] != "")
echo ", " . $information_array[4];
if ($information_array[0] != "")
echo ", " . $information_array[0];
if ($information_array[5] != "")
echo ", " . "<a href=\"http://" . $information_array[5] . "\">" . $information_array[5] . "</a>";
echo "<br/>";
}
//Re-point to the beginning of the $result array
mysql_data_seek($result, 0);
echo "<br/>";
}
echo "</p>";
}
?>When user searches for something , the above code gives the search results. I want to provide hyperlink to every search result. When the user clicks the search result, user will be navigated to another webpage.
