[SOLVED] Search Engine - opposing input boxes

[EternalSorrow]

Currently I'm trying to fix several problems with my complicated search engine, namely when someone tries to search the two input boxes simultaneously or the two option boxes simultaneously, errors occur for both attempts.

 

Here is the error when using the input boxes simultaneously:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE summary LIKE '%more%' ORDER by stories.story asc'

 

And here is the error when using the option boxes simultaneously:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE stories.series_id='1' AND series.series_id='1' ORDER by stories.story asc'

 

Here is a live preview of the search engine for a visual reference.

 

I've tried pulling the pieces apart and working on them separately, but I only make the problem worse.  Any ideas for what may be the problem(s) with my code?

 

The search engine code:

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

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

<tr><td>Status: <input type="text" name="status">

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

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

}
?>
</select>

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

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

}
?>
</select>

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

<tr><td colspan="2" style="text-align: center;">
<input type="submit" name="search" value="Search">
<input type="reset" name="reset" value="Reset">
</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'])));
$status = strtolower(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']));

if(!empty($story))
{
$terms .= " WHERE story LIKE '%$story%' ";
}

if(!empty($summary))
{
$terms .= " WHERE summary LIKE '%$summary%'";
}

if(!empty($status))
{
$terms .= " WHERE status LIKE '%$status%'";
}

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

$sql_rating = ($rating == all) ? "" : "$join stories.rating_id='$rating' AND rating.rating_id='$rating'";
$sql_series = ($series == all) ? "" : "$join stories.series_id='$series' AND series.series_id='$series'";

$qSearch = "SELECT *
FROM stories
LEFT JOIN rating ON stories.rating_id = rating.rating_id
LEFT JOIN series ON series.series_id = stories.series_id
$terms $sql_rating $sql_series $sql_status 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><a href="fanfiction.php?story='.$story.'" >'.$story.'</A>
<br>'.$summary.'
<div class="fan">'.$status.' ('.$chapter.') · '.$pairing.' · '.$universe.' · '.$genre.' · '.$rating.'</div>';

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></div>';

}

}
?>

[bobbinsbro]

looks to me like when concatenating text into $terms, your forgetting to put "AND" between the WHERE's. so what you get when searching both boxes simultaneously is something like:

 

$terms = " WHERE somethingOrAnother WHERE yaddayadda"

 

what you should have is:

 

$terms = " WHERE somethingOrAnother AND WHERE yaddayadda"

[bobbinsbro]

second error:

 

looks to me like what you have in the sql is something like:

 

....WHERE stories.rating_id='$rating' AND rating.rating_id='$rating' WHERE stories.series_id='$series' AND series.series_id='$series'...

 

what i think you want is to end up with

 

....WHERE stories.rating_id='$rating' AND rating.rating_id='$rating' AND WHERE stories.series_id='$series' AND series.series_id='$series'...

 

you should probably add an if statement after assigning $sql_series to check if $sql_series != "" and if true, then $sql_rating = $sql_rating.' AND ';

 

i think...

 

[EternalSorrow]

Your first post worked perfectly on the problem, but I'm a little slow on the second post.

 

I understand that the problem is a lack of an AND statement which would connect the two options, but I'm unsure (read: too PHP illiterate) to properly implement your fix (tried and failed).  What would be the exact look of the if statement to check the 'true' status of the sql_series and implement the needed AND for the search?

[bobbinsbro]

there may be a more efficient way of doing this (i'm a bit new to php myself) but here's what i would do:

if ($sql_series != ""){ //i think that just doing      if($sql_series)         should work too.
   $sql_series = $sql_series." AND "; //note the spaces around the AND.
}

[EternalSorrow]

Using both if ($sql_series) and if ($sql_series != "") creates an error message for the series option, regardless of whether it's in conjunction with the other option box or even the input boxes.

 

Also, to add to the problem the option boxes can no longer be used alone, meaning the searcher must input information into any of the input boxes in order for the options to work.  Previously they could be used as a stand-alone option without the input boxes.

[bobbinsbro]

ok. i think i see what i did wrong. try substituting this:

if(!empty($story))
{
$terms .= " WHERE story LIKE '%$story%'";
}

if(!empty($summary))
{
$terms .= " WHERE summary LIKE '%$summary%'";
}

if(!empty($status))
{
$terms .= " WHERE status LIKE '%$status%'";
}

 

with this:

 

$termsArray = array();

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

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

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

if (count($termsArray) > 0){
   $terms = implode(" AND ", $termsArray);
   $terms = " WHERE ".$terms;
   unset($termsArray); //clear memory, cause we're finished using this.
}

 

let me know what happens.

i'll look at the second error again and post what i find.

[bobbinsbro]

for the second error, how about trying this:

 

instead of:

$sql_rating = ($rating == all) ? "" : "$join stories.rating_id='$rating' AND rating.rating_id='$rating'";
$sql_series = ($series == all) ? "" : "$join stories.series_id='$series' AND series.series_id='$series'";

 

use:

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

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

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

[EternalSorrow]

You're a genius.  I've been stuck with this lame search engine for ages and now everything works perfectly.  I can't thank you enough for your quick and inciteful help (I've learned a lot from your code, and plan to study it further).

 

Again, thank you so much1

[bobbinsbro]

glad i could help.

 

nice site btw. i really like the look.

[EternalSorrow]

Thanks