Search results with PHP and MySQL

Question

I recently posted another thread related to a search engine I was working on based on "Build your own DB driven website etc" by Kevin Yank. The search engine is working, however there are a few bugs I would like to fix and don't know how to. I'll focuson just one of them in this thread so that it doesn't get too confusing.

In the database, there is one table for the jokes (called "joke") and another table for the theme (called "theme"). These two tables are related by another table called "joketheme". Each joke should be able to have more than 1 theme and I would like the results to list all the themes without repeating the entries. So far, I haven't been able to achieve that. In fact, I'm not sure how to even designate 2 themes for 1 joke within MySQL.

    table 1: joke
    id~~~joketext~~~other data
    1~~~joke1~~~
    2~~~joke2~~~
    3~~~joke3~~~

    table 2: theme
    id~~~name
    1~~Knock knock
    2~~Lawyers

    table 3: joketheme
    jokeid~~~themeid
    1~~~~~~1
    1~~~~~~2
    2~~~~~~2
    3~~~~~~1

Do you know what I need to change in the code (or in the MySQL) to list more than one theme in each result (if that entry has more than one theme?)?

Here is my code in the search results page. Thank you in advance for any help!:

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

?>

Search 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>

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



    $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

Sounds like you need to do some research into SQL queries and how they work. If you want a list of themes for an individual joke, you can do a simple query like this:

  SELECT DISTINCT(theme.name) FROM theme
     INNER JOIN joketheme ON joketheme.theme_id = theme.id
     INNER JOIN joke ON joke.id = joketheme.joke_id
     WHERE joke.id = {insert joke id}

What we're doing here is called a join. We're joining the three tables together, matching results based on ids that are common across all tables. These are called keys. Basically, we're trying to select all the themes from the theme table that match up with a specific joke's theme.

The DISTINCT part of the query ensures that we only get unique results.

I suggest you check out some SQL tutorials to find out about some different ways of getting different sets of data using queries. It's really useful to know these kinds of things and could save you a ton of that code up there.

Also, as far as readability goes, I would suggest putting underscores between table names for join tables (e.g. joke_theme), or adopting some other similar convention. Makes it easier to read and tell which one is a join table and which is a regular table.

Good luck :)