EternalSorrow

Thanks

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

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.

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?

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>'; } } ?>

Thanks for the great explanation! That definitely cleared up some of my questions and confusions!

bump...anyone?

bump again...going on the fourth time...

bumpety

Greetings. I am new to the board, and was curious if someone could answer my php coding problem. I am attempting a large database which is will be accessed by visitors to my web page according to three page. The first page will show a list of categories available to view, with a link to the next page. The next page will show a list of years within that category, also with a link to those rows specified by both the category and year. An example would be: click category link -> click year link -> arrive at row which applies to same category and year. I am able to create the proper list of categories which, when clicked, will lead the visitor to the proper list of years related to that category. However, using my current script I am unable to figure out how to specify the row to show only those which apply to the category and list. I've scoured the internet, asked on other forums, and finally come on hands and needs for your assistance. Any ideas? category.php page: <?php mysql_connect(localhost,user,pw); @mysql_select_db(db) or die( "Unable to select database"); $query = "SELECT DISTINCT category, category_id FROM `archives` WHERE `group` like '%inuyasha%' ORDER BY `category` ASC "; $result = mysql_query($query) or die(mysql_error()); while($row=mysql_fetch_array($result)) { extract($row); echo "<li><a href=\"inuyashayear.php?category_id=$category_id\">$category</A></li>"; } ?> year.php page, which includes the code to display the rows (I sincerely apologize if the length is too long): <?php mysql_connect(localhost,user,pw); @mysql_select_db(db) or die( "Unable to select database"); if (is_numeric($_GET["category_id"])) { $category_id = $_GET["category_id"]; } $a = strip_tags(trim(htmlentities($_GET["a"], ENT_QUOTES))); if (empty($a)) { $a = 'index'; } switch ($a) { case 'index': $select_year = mysql_query("SELECT DISTINCT `year`, category_id FROM `archives` WHERE `category_id` = '$category_id' ORDER BY `year` desc") or die (mysql_error()); while($row2=mysql_fetch_array($select_year)) { extract ($row2); $select_year_count = mysql_query("SELECT * FROM `archives` where `year`='$year' ORDER BY `year` ASC") or die (mysql_error()); echo "<li><a href=\"?a=show&year=$year\">$year</a></li>"; } break; case 'show': $year2 = strip_tags(trim(htmlentities($_GET["year"], ENT_QUOTES))); // Example Start Query $query = "select DISTINCT year, title, author, rating from `archives` WHERE `year`='$year2' AND category_id ='$category_id' order by year desc "; $result = mysql_query($query) or die(mysql_error()); while($row=mysql_fetch_array($result)) { extract($row); echo "<li>$title - $author - $rating</li>";; } } ?>