PHP Search EITHER keywords OR other categories
<p>I have a search feature using PHP that offers either the option to search by keyword OR by other categories. To illustrate:</p>

<pre><code>      Search by keyword: __________
      Search by the following (drop down menus):
         1. Author
         2. Category
         3. Theme
         4. Region
</code></pre>

<p>One of many bugs I have is that if a user types something into the keyword search field and then ALSO chooses a category from a drop down menu, the search query is unsuccessful (0 results). What can I do to make it run, no matter what the user types in or chooses from the drop down menus?</p>

<p>The code for the results page is as follows. Thank you in advance for any help you can offer!:</p>

<p>Connection:</p>

<pre><code>    &lt;?php

$dbcnx = @mysql_connect('localhost', 'root', 'password'); 

if (!$dbcnx) {
    exit('&lt;p&gt;Unable to connect to the ' . 'database server at this time.&lt;/p&gt;');
}

if (!@mysql_select_db('ijdb')) { 
    exit('&lt;p&gt;Unable to locate the joke ' . 'database at this time.&lt;/p&gt;');
}

$authors = @mysql_query('SELECT id, name FROM author'); 
if (!$authors) {
    exit('&lt;p&gt;Unable to obtain author list from the database.&lt;/p&gt;');
}

$cats = @mysql_query('SELECT id, name FROM category'); 
if (!$cats) {
    exit( '&lt;p&gt;Unable to obtain category list from the database.&lt;/p&gt;');
} 

$themes = @mysql_query('SELECT id, name FROM theme'); 
if (!$themes) {
    exit( '&lt;p&gt;Unable to obtain category list from the database.&lt;/p&gt;');
}

$geofoci = @mysql_query('SELECT id, name FROM geofocus'); 
if (!$geofoci) {
    exit( '&lt;p&gt;Unable to obtain category list from the database.&lt;/p&gt;');
}

?&gt;
</code></pre>

<p>The actual form:</p>

<pre><code>    &lt;form class="searchField" name="input" action="fundfetch_search.php" method="post"&gt;
    &lt;ul&gt;
&lt;li&gt;
    &lt;label&gt;Search by keyword:&lt;/label&gt;
    &lt;input type="text" name="searchtext" class="styleSearchbox" placeholder="By keyword" value="&lt;?php echo $_POST['searchtext']; ?&gt;"&gt;
&lt;/li&gt;
&lt;li&gt;
        &lt;label&gt;OR by the following: &lt;/label&gt;
        &lt;label&gt;&lt;select name="aid" size="1" class="styleDropdown"&gt;
        &lt;option selected value=""&gt;Any Author&lt;/option&gt; 
        &lt;?php
            while ($author = mysql_fetch_array($authors)) { 
                $aid = $author['id']; 
                $aname = htmlspecialchars($author['name']); 
                echo "&lt;option value='$aid'&gt;$aname&lt;/option&gt;\n";
        } 
        ?&gt; 
        &lt;/select&gt;&lt;/label&gt;           
    &lt;/li&gt;
    &lt;li&gt;
        &lt;label&gt;&lt;select name="cid" size="1" class="styleDropdown"&gt;
            &lt;option selected value=""&gt;Any Category&lt;/option&gt; 
        &lt;?php
        while ($cat = mysql_fetch_array($cats)) { 
            $cid = $cat['id']; 
            $cname = htmlspecialchars($cat['name']); 
            echo "&lt;option value='$cid'&gt;$cname&lt;/option&gt;\n";
        } 
        ?&gt;
        &lt;/select&gt;&lt;/label&gt;
    &lt;/li&gt;
    &lt;li&gt;
        &lt;label&gt;&lt;select name="tid" size="1" class="styleDropdown"&gt;
            &lt;option selected value=""&gt;Any Theme&lt;/option&gt; 
        &lt;?php
        while ($theme = mysql_fetch_array($themes)) { 
            $tid = $theme['id']; 
            $tname = htmlspecialchars($theme['name']); 
            echo "&lt;option value='$tid'&gt;$tname&lt;/option&gt;\n";
        } 
        ?&gt;
        &lt;/select&gt;&lt;/label&gt;
    &lt;/li&gt;
    &lt;li&gt;
        &lt;label&gt;&lt;select name="gfid" size="1" class="styleDropdown"&gt;
            &lt;option selected value=""&gt;Any Region&lt;/option&gt;
        &lt;?php
        while ($geofocus = mysql_fetch_array($geofoci)) { 
            $gfid = $geofocus['id']; 
            $gfname = htmlspecialchars($geofocus['name']); 
            echo "&lt;option value='$gfid'&gt;$gfname&lt;/option&gt;\n";
        } 
        ?&gt;
        &lt;/select&gt;&lt;/label&gt;
    &lt;/li&gt;
    &lt;li style="visibility:hidden"&gt;&lt;a href="../FUNDER.COM website/searchfilteroption"&gt;Closing&lt;/a&gt;&lt;/li&gt;
 &lt;li&gt;&lt;input type="submit" value="Search" class="searchButton"&gt;&lt;/li&gt;
&lt;/ul&gt;
    &lt;/form&gt;
</code></pre>

<p>Database query:</p>

<pre><code>        &lt;?php

$dbcnx = @mysql_connect('localhost', 'root', 'password'); 

if (!$dbcnx) {
    exit('&lt;p&gt;Unable to connect to the ' . 'database server at this time.&lt;/p&gt;');
}

if (!@mysql_select_db('ijdb')) { 
    exit('&lt;p&gt;Unable to locate the joke ' . 'database at this time.&lt;/p&gt;');
    }

// The basic SELECT statement 

$select = 'SELECT DISTINCT joke.id, joke.joketext, joke.jokedate, 
            author.id AS author_id, author.name AS author_name, 
            jokecategory.jokeid AS cat_jokeid, jokecategory.categoryid AS joke_catid, category.id AS cat_id, category.name as cat_name, 
            joketheme.jokeid AS theme_jokeid, joketheme.themeid AS joke_themeid, theme.id AS theme_id, theme.name AS theme_name,
            jokegeofocus.jokeid AS geofocus_jokeid, jokegeofocus.geofocusid AS joke_geofocusid, geofocus.id AS geofocus_id, geofocus.name AS geofocus_name';
$from   = ' FROM joke, author, jokecategory, category, joketheme, theme, jokegeofocus, geofocus'; 
$where = ' WHERE joke.authorid = author.id AND joke.id = jokecategory.jokeid AND jokecategory.categoryid = category.id AND joke.id = joketheme.jokeid AND joketheme.themeid = theme.id AND joke.id = jokegeofocus.jokeid AND jokegeofocus.geofocusid = geofocus.id';
$in = ' ORDER BY jokedate DESC';

$aid = $_POST['aid']; 
if ($aid != '') { // An author is selected
    $where .= " AND authorid='$aid'";
}

$cid = $_POST['cid']; 
if ($cid != '') { // A category is selected
    $from .= ''; // usually written as ' ,tablename'
    $where .= " AND joke.id=jokecategory.jokeid AND categoryid='$cid'";
}

$tid = $_POST['tid'];
if ($tid != '') { // A theme is selected
    $from .= '';
    $where .= " AND joke.id=joketheme.jokeid AND themeid='$tid'";
}

$gfid = $_POST['gfid'];
if ($gfid != '') { // A region is selected
    $from .= '';
    $where .= " AND joke.id=jokegeofocus.jokeid AND geofocusid='$gfid'";
}

$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
    $where .= " AND keywords LIKE '%$searchtext%'";
    }
    ?&gt;
</code></pre>

<p>Results:</p>

<pre><code>    &lt;?php   

    $jokes = @mysql_query($select . $from . $where . $in); 
    if (!$jokes) {
        echo '&lt;/table&gt;'; exit('&lt;p&gt;Error retrieving jokes from database!&lt;br /&gt;'.
        'Error: ' . mysql_error() . '&lt;/p&gt;');
    }

    $numrows = mysql_num_rows($jokes);
    if ($numrows&gt;0){
    while ($joke = mysql_fetch_array($jokes)) { 
        $id = $joke['id'];
        $joketext = htmlspecialchars($joke['joketext']);
        $jokedate = htmlspecialchars($joke['jokedate']);
        $aname = htmlspecialchars($joke['author_name']);
        $category = htmlspecialchars($joke['cat_name']);
        $theme = htmlspecialchars($joke['theme_name']);
        $geofocus = htmlspecialchars($joke['geofocus_name']);
        $position = 200;
        $post = substr($joketext, 0, $position);
        echo "&lt;li id=\"jump\"&gt;
                &lt;article class=\"entry\"&gt;
                    &lt;header&gt;
                        &lt;h3 class=\"entry-title\"&gt;&lt;a href=''&gt;$aname&lt;/a&gt;&lt;/h3&gt;
                    &lt;/header&gt;
                    &lt;div class=\"entry-content\"&gt;
                        &lt;p&gt;$post...&lt;/p&gt;
                    &lt;/div&gt;
                    &lt;div class =\"entry-attributes\"&gt;
                        &lt;p&gt;&gt; Category: $category&lt;/p&gt;
                        &lt;p&gt; &gt; Theme(s): $theme&lt;/p&gt;
                        &lt;p&gt; &gt; Region(s) of focus: $geofocus&lt;/p&gt;
                        &lt;/div&gt;
                    &lt;footer class=\"entry-info\"&gt;
                        &lt;abbr class=\"published\"&gt;$jokedate&lt;/abbr&gt;
                    &lt;/footer&gt;
                &lt;/article&gt;
            &lt;/li&gt;";
    }
}
    else
        echo "Sorry, no results were found. Please change your search parameters and try again!";
     ?&gt;         
</code></pre>
