Search results with PHP and MySQL
<p>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.</p>

<p>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. </p>

<pre><code>    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
</code></pre>

<p>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?)?</p>

<p>Here is my code in the search results page. Thank you in advance for any help!:</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>Search 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>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;');
    }



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