SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Search parameters with php

    Hi guys,

    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 Code:
    <section id="search">
        
        <?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="jokes_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><input type="submit" value="Search" class="searchButton"></li>
        </ul>
    </form>
    </section>
    <section id="results">
        <?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%'";
            }
    ?>    


        <ol id="results-list">
            
            <?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($joketext0$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!";
    ?>
    Last edited by guido2004; Nov 8, 2011 at 13:58. Reason: adding php tags

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    First of all, please put the apropriate code tags around the code you post. In this case the [php] and [/php] tags. I added them for you. It makes it so much easier to read the code.

    Second, in a script there's no need to connect to mysql and select a database twice (unless you've closed the connection at some point, or you want to connect to another database).

    Of course, these two tips don't solve your problem. But looking at your code, I don't see why you shouldn't get more than one theme if the joke has more of them. Can you give an example of what is it you see in your browser, and what you would like to see instead?

  3. #3
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guido and thanks for editing the PHP tags,

    I'm attaching an image of what I am getting in the browser when I search with a keyword. The results are true, but repeated. I would like there to be one result and under Theme(s): Agriculture, Civil Society.

    themes.png

    Thank you!

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Ok I understand the problem.
    I don't have the time right now to work with your code, but please take a look at this post: http://www.sitepoint.com/forums/show...how-to-code-it

    It's a similar problem. Try to implement it in your situation.

  5. #5
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Guido!

    It does look like a very similar problem, unfortunately I still don't really understand the solution...I don't know exactly how to implement it into my code. I'll try to figure it out, but if you have some time later on to guide me through it, I would greatly appreciate it!

  6. #6
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't implement code...

    Quote Originally Posted by guido2004 View Post
    Ok I understand the problem.
    I don't have the time right now to work with your code, but please take a look at this post: http://www.sitepoint.com/forums/show...how-to-code-it

    It's a similar problem. Try to implement it in your situation.
    Hi again, Guido.

    I'm still unable to implement from the example you suggested (if anything I think I "broke" something)...do you think you could help me again if/when you have some time?

    Thank you,


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •