I am new here and having a big headache. I have inherited a web site at my job. It is a site for a history magazine and I need to be able to have a search and advanced search option that allows users to search the article table by keywords. The database is already set up with an article table with a keyword row. I have no idea how to go about this. This is the script I have for calling articles up by category how can I modify it to search using that darn search box? the connection string is in my bin/html/common-header.php but I can provide it if needed.
Thanks in advance for any help!

<?
$path = "";
$pageID = "categories";


$CategoryID = $_REQUEST;


$self = $_SERVER;


// how many rows to show per page
$rowsPerPage = 20;


// if $_GET defined, use it as page number
if(isset($_GET))
{
$pageNum = $_GET;
} else {
// by default we show first page
$pageNum = 1;
}


// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
?>


<HTML>
<HEAD>
<TITLE>Current History</TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<LINK REL=StyleSheet HREF="current-history-style.css" TYPE="text/css">


</HEAD>
<BODY BGCOLOR=#FFFFFF LEFTMARGIN=0 TOPMARGIN=0 MARGINWIDTH=0 MARGINHEIGHT=0>


<? require("bin/html/common-header.php"); ?>


<?
$categoryHeader_result = mysql(currenthistorydb, "select *  from Categories where (CategoryID = $CategoryID)");
$num = mysql_numrows($categoryHeader_result);
$i = 0;
$Title = mysql_result($categoryHeader_result, $i, "CategoryName");


$article_result = mysql(currenthistorydb, "select Articles.ArticleID, Articles.Title, Articles.Author, Articles.Month, Articles.Year, Articles.Abstract, CategoryArticles.ArticleID,  CategoryArticles.CategoryID  from Articles,  CategoryArticles where ((Articles.display = 1) and (CategoryArticles.ArticleID = Articles.ArticleID) and ( CategoryArticles.CategoryID = $CategoryID)) order by Year desc, Issue desc, page asc  LIMIT $offset, $rowsPerPage");


$query   = "SELECT COUNT(Articles.ArticleID) AS numrows FROM Articles, CategoryArticles where  ((Articles.display = 1) and (CategoryArticles.ArticleID = Articles.ArticleID) and ( CategoryArticles.CategoryID = $CategoryID))";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row;
// echo "<br>numrow" . $numrows;
// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
// echo "<br>maxpage" . $maxPage;



if ($pageNum > 1)
{
$page  = $pageNum - 1;
$prev  = " <a href=\"$self?ID=$CategoryID&page=$page\">[Prev]</a> ";


$first = " <a href=\"$self?ID=$CategoryID&page=1\">[First Page]</a> ";
}
else
{
$prev  = '&nbsp;'; // we're on page one, don't print previous link
$first = '&nbsp;'; // nor the first page link
}


if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?ID=$CategoryID&page=$page\">[Next]</a> ";


$last = " <a href=\"$self?ID=$CategoryID&page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = '&nbsp;'; // we're on the last page, don't print next link
$last = '&nbsp;'; // nor the last page link
}



$num = mysql_numrows($article_result);
$k = 0;



// print the page navigation link


echo "<table border=0 cellpadding=0 cellspacing=0 width=\"100%\"><tr><td><h1 align=left style=\"margin:0px;\">$Title</h1><td>";
echo "<td align=right nowrap valign=bottom>Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages<br> " . $first . $prev .  $next . $last . "</td></tr></table>";
echo "<hr>";



while ($k < $num) {
echo "<p><b>" . mysql_result($article_result, $k, "Month") . " " . mysql_result($article_result, $k, "Year") . "</b><br>
<a href=\"Article.php?ID=" .  mysql_result($article_result, $k, "ArticleID") . "\" class=\"title\">" . mysql_result($article_result, $k, "Title") . "</a><br>";
if ( mysql_result($article_result, $i, "Author")  <> "") {
echo "<a href=\"Article.php?ID=" .  mysql_result($article_result, $i, "ArticleID") . "\" class=\"author\">by " . mysql_result($article_result, $i, "Author") . "</a><br>";
}
echo mysql_result($article_result, $k, "Abstract") ."</p>";
$k++;
}



// print the page navigation link
echo "<div align=right>Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages<br> " . $first . $prev .  $next . $last . "</div>";


?>


<? require("bin/html/common-footer.php"); ?>


</BODY>
</HTML>

Edited 3 Years Ago by happygeek: fixed formatting

To search the Articles table for the user query:
Create the search form giving the search field a name. You can set the form method to GET or POST. I'd use GET.

<form name="sitesearch" action="search.php" method="get">
<input type="text" name="userquery">
</form>

Then in the searh.php file assign the value from the input field to a variable:

$userquery = $_GET['userquery'];

Then search the Keyword row for that userquery:

"select * from Articles where Articles.Keyword like '%$userquery%'"

Then display the results the same way the Articles page does.

this is the new script:

$name = $_GET['keywords'];
$name_arr = explode(' ', $name);
$search_result = '';

foreach($name_arr as $key => $name)
{
      $query="SELECT * FROM Articls WHERE";
      $query.=" Keywords LIKE '%$name%'";
      $query.=" OR Title LIKE '%$name%'";
      $query.=" OR Author LIKE '%$name%' ORDER BY title";
      $result_arr[$key]=mysql_db_query("currenthistorydb", $query);
      $num_rows_arr[$key] = mysql_num_rows($result_arr[$key]);
      $search_result.= "Found $num_rows_arr[$key] results for the term $name.<br />";
}

mysql_close();

echo($search_result);

echo '<b><center><font size="4" color="#FF0000">Search Result</font></center></b><br><br>';

foreach($result_arr as $key => $result)
{
      $i=0;
      while ($i < $num_rows_arr[$key])
      {

            $row = mysql_fetch_row($result);
            $search_term = $name_arr[$key];
            $authors = $row[1];
            $title = $row[2];
            $source = $row[3];

            echo "<b>Search Term:</b> $search_term<br><b>Author:</b> $authors<br><b>Title:</b> $title<br><b>Source:</b> $source<br><br><hr><br>";

            $i++;
      }
}

and i am getting this:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /www/currenthistory.com/htdocs/preview/search-results.php on line 32
Found results for the term China.

This article has been dead for over six months. Start a new discussion instead.