使用PHP和MySQL搜索结果

我最近发布了另一个与搜索引擎相关的帖子，我正在基于Kevin Yank的"建立自己的DB驱动网站等"。搜索引擎正在工作，但是有一些错误，我想修复，不知道如何。在这篇文章中，我只关注其中的一个，这样就不会让人太困惑了。

在数据库中，有一个表用于笑话(称为"joke")，另一个表用于主题(称为"theme")。这两个表由另一个名为"joketheme"的表连接。每个笑话应该能够有一个以上的主题，我希望结果列出所有的主题，而不重复的条目。到目前为止，我还没能做到这一点。事实上，我不确定如何在MySQL中为一个笑话指定2个主题。

    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

你知道我需要改变的代码(或在MySQL)在每个结果中列出多个主题(如果该条目有多个主题?)?

这是我的代码在搜索结果页面。提前感谢您的任何帮助!:

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

搜索表单:

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

查询:

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

结果

    <?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!";
     ?>

  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}