[SOLVED] PHP/MySQL Search Engine - two tables, running out of memory

[EternalSorrow]

I'm trying to create a two-table search engine which will search through a simple stories table and a heavier chapters (here called fanfiction) table.  The trouble I'm having is implementing another input text box (here called keywords) in addition to the current two which will be connect solely to the fanfiction table.

 

I can't seem to figure out how to connect the multiple fields from the stories table to the single field (here called $chapter) from the fanfiction table.  Whenever I enter a word in, the search will return all rows for the stories table and each story is titled with the first story, but all other information will be correct.

 

I've also had to place the GROUP BY stories.story in the SELECT code to limit the searches, because the memory was timing out.  That means the search was returning the same chapters (and thus stories) multiple times if multiple instances of the word(s) were found.

 

So the problems are many fold: the keyword that is typed in, which is connected to the fanfiction table, is completely ignored when used with any other part of the search engine.  I also can't figure out how to limit the results to distinct stories when there are multiple instances of the word in its chapters without using the GROUP BY method.

 

Here's the code for the search engine:

<form action="#results" method="POST">
<table align="center" style="text-align: right;">
<tr><td>Title: <input type="text" name="story">

<tr><td>Summary: <input type="text" name="summary">

<tr><td>Keyword(s): <input type="text" name="chapter">

<tr><td>Status: <select name="status">
<option value="all">Search All</option>
<?php
$qStatus = "SELECT DISTINCT status FROM stories ORDER BY status ";
$rsStatus = mysql_query($qStatus) or die ('Cannot execute query');

while ($row = mysql_fetch_array($rsStatus))
{
extract($row);
echo '<option value="'.$status.'">'.$status.'</option>';

}
?>
</select>

<tr><td>Rating: <select name="rating">
<option value="all">Search All</option>
<?php
$qRating = "SELECT DISTINCT rating FROM stories ORDER BY rating ";
$rsRating = mysql_query($qRating) or die ('Cannot execute query');

while ($row = mysql_fetch_array($rsRating))
{
extract($row);
echo '<option value="'.$rating.'">'.$rating.'</option>';

}
?>
</select>

<tr><td>Series: <select name="series">
<option value="all">Search All</option>
<?php
$qSeries = "SELECT DISTINCT series FROM stories ORDER BY series ";
$rsSeries = mysql_query($qSeries) or die ('Cannot execute query');

while ($row = mysql_fetch_array($rsSeries))
{
extract($row);
echo '<option value="'.$series.'">'.$series.'</option>';

}
?>
</select>

<tr><td>Sort Order: <select name="order">
<option value="asc">Ascending</option>
<option value="desc">Descending</option>
</select>

<tr><td style="text-align: right;">
<input type="reset" name="reset" value="Reset">
<br><input type="submit" name="search" value="Search">
</table>
</form>

<div class="line"></div>

<p>Here are the <a name="results">results</A> of your search:

<p><?php

if(isset($_POST[search])) {

$story = strtolower(strip_tags(mysql_escape_string($_POST['story'])));
$summary = strtolower(strip_tags(mysql_escape_string($_POST['summary'])));
$chapter = strtolower(strip_tags(mysql_escape_string($_POST['chapter'])));
$status = strip_tags(mysql_escape_string($_POST['status']));
$rating = strip_tags(mysql_escape_string($_POST['rating']));
$series = strip_tags(mysql_escape_string($_POST['series']));
$order = strip_tags(mysql_escape_string($_POST['order']));

$termsArray = array();

if(!empty($story))
{
$termsArray[] = "stories.story LIKE '%$story%'";
}

if(!empty($summary))
{
$termsArray[] = "stories.summary LIKE '%$summary%'";
}

if(!empty($chapter))
{
$termsArray[] = "fanfiction.chapter LIKE '%$chapter%'";
}

if (count($termsArray) > 0){
   $terms = implode(" AND ", $termsArray);
   $terms = " WHERE ".$terms;
   unset($termsArray);
}

$join = (empty($story) && empty($summary) && empty($chapter)) ? "WHERE" : "AND";

$sql_rating = ($rating == all) ? "" : "$join stories.rating='$rating'";

if ($sql_rating != ""){
   $join = "AND";
}

$sql_series = ($series == all) ? "" : "$join stories.series='$series'";

if ($sql_series != ""){
   $join = "AND";
}

$sql_status = ($status == all) ? "" : "$join stories.status='$status'";

$qSearch = "SELECT stories.*, fanfiction.*
FROM stories, fanfiction
$terms $sql_rating $sql_series $sql_status
GROUP BY stories.story
ORDER by stories.story $order ";

$rsSearch = mysql_query($qSearch) or die(mysql_error());

if (mysql_num_rows($rsSearch) == 0)
{
echo '<p>Sorry, there was no results returned for your search. Please try again.</p>';
}
else
{
echo '<center><p><strong>'.mysql_num_rows($rsSearch).'</strong> story(s) found.</p></center>';

$i = 1;

echo '<ul class="fanfiction">';

while ($row = mysql_fetch_array($rsSearch))
{
extract($row);

$contents_here = '<li><div class="font"><a href="fanfiction.php?story='.$story.'" >'.$story.'</A></div>
'.$summary.'
<div class="fan">Series: <a href="stories.php?series='.$series.'">'.$series.'</A> | Universe: '.$universe.' | Rating: '.$rating.' | Status: '.$status.' | Chapters: '.$chapter_number.' | Pairing: '.$pairing.' | Genre: '.$genre.'</div></li>';

if ($i==1)
    {
    echo '<div class="even">'.$contents_here.'</div>';
    }
   
    else if ($i==0)
    {
    echo '<div class="odd">'.$contents_here.'</div>';
    }

    $i++; $i=$i%2;

}
echo '</ul>';

}

}
?>

[anfo]

at the end of the sql query use the keyword LIMIT and the number of results you want for example;

 

SELECT ghosts FROM stories LIMIT 30

 

sorry can't help you with the rest only fairly new to php/mysql myself

[EternalSorrow]

Finally got it solved.  I'd foolishly forgotten to place a LEFT JOIN in the SELECT statement to connect the two tables.