PHP Search EITHER keywords OR other categories

Question

I have a search feature using PHP that offers either the option to search by keyword OR by other categories. To illustrate:

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

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?

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

Connection:

    <?php

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

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

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

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

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

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

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

?>

The actual form:

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

Database query:

        <?php

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

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

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

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

Results:

    <?php   

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

    $numrows = mysql_num_rows($jokes);
    if ($numrows>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 "<li id=\"jump\">
                <article class=\"entry\">
                    <header>
                        <h3 class=\"entry-title\"><a href=''>$aname</a></h3>
                    </header>
                    <div class=\"entry-content\">
                        <p>$post...</p>
                    </div>
                    <div class =\"entry-attributes\">
                        <p>> Category: $category</p>
                        <p> > Theme(s): $theme</p>
                        <p> > Region(s) of focus: $geofocus</p>
                        </div>
                    <footer class=\"entry-info\">
                        <abbr class=\"published\">$jokedate</abbr>
                    </footer>
                </article>
            </li>";
    }
}
    else
        echo "Sorry, no results were found. Please change your search parameters and try again!";
     ?>         

Answer 1

I don't even know where to start ...

First at all, never thrust your users, please sanitize your database input.

Second, code is writen for people to understand, you slipped a lot of duplicates in your query, I think beacause is hard to read the query, example AND joke.id = jokecategory.jokeid appears 2 times.

More recomandations:

• use inner join with on it makes the query more readable
• don't encapsulate INT in quotes, it will break your index and make your query slow.
• if you expect an INT check it with is_numeric()
• use isset() before $val != '', so it won't throw a warning if the index in the array doesn't exists

See code below:

$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
            inner join author on (joke.authorid = author.id)
            inner join jokecategory on (joke.id = jokecategory.jokeid)
            inner join category on (jokecategory.categoryid = category.id)
            inner join joketheme on (joke.id = joketheme.jokeid)
            inner join theme on (joketheme.themeid = theme.id)
            inner join jokegeofocus on (joke.id = jokegeofocus.jokeid)
            inner join geofocus on (jokegeofocus.geofocusid = geofocus.id)'; 
$first_where = ' where ';
$where = '';
$in = ' ORDER BY jokedate DESC';

if (is_numeric($_POST['aid']))
{   // An author is selected
    $where.= $first_where.' authorid='.$_POST['aid'];
    $first_where = ' and ';
}

if (is_numeric($_POST['cid']))
{   // A category is selected
    $where.= $first_where.' categoryid='.$_POST['cid'];
    $first_where = ' and ';
}

if (is_numeric($_POST['tid']))
{   // A theme is selected
    $where.= $first_where.' themeid='.$_POST['tid'];
    $first_where = ' and ';
}

if (is_numeric($_POST['gfid']))
{   // A region is selected
    $where.= $first_where.' geofocusid='.$_POST['gfid'];
    $first_where = ' and ';
}

if (isset($_POST['searchtext']) and $_POST['searchtext'] != '')
{   // Some search text was specified
    $where.= $first_where.' keywords LIKE "%'.(mysql_real_escape_string($_POST['searchtext'], $dbcnx)).'%"';
}

if($where == '')
{   // prevents returning the whole database, if form is empty
    $where = ' limit 20';
}

Please test my code, and leave a comment (add it to your answer, you don't have reputations for real comments) if you still have problems (I didn't have time to test it).

Answer 2

I would suggest showing the mixed results where the keyword exists in that category and then maybe on the side listing other categories along with a count where that keyword was found. Think about large sites like amazon. If I select "movies" from the category list and type in "disney", I am shown the most popular results from movies that match disney. Then on the left, I am given more options to narrow or widen the search through sub/other categories.