SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    searching 2 tables at once

    Hi Guys,

    on the forum im developing a user can search for specific keywords, but i have 2 tables i really need searching they are:

    forum_posts
    forum_topics

    this is part of the forum code:

    PHP Code:
    <?php     
         
    ####################################################################################
         
    if ($_GET['action'] == "search") {
         
         
    ## isset code ######################################################################
         
    if(isset($_POST['submit'])) {
         
         echo 
    '<br /><center><h4>Search Results</h4></center>';
         
         
    // define variable...///////////////////////////////////////////////////////////////
         
    $keywords CleanPosts($_POST['keywords'],1);
         
         
    // empty press...///////////////////////////////////////////////////////////////////
         
    if(empty($keywords)) {
         
            echo 
    '<font color="red" /><b>Error:</font> Sorry, You Never Typed In A String To Search For!<br /><br />';
            include(
    "includes/footer.php");
            exit;
         
         }
         
         
    ## now do the search.../////////////////////////////////////////////////////////////
         
    $search_query "SELECT * FROM `forum_posts` WHERE (`post_body` LIKE '%$keywords%')";
         
    $search_result mysql_query($search_query) or die (mysql_error());
         
         
    // no results...////////////////////////////////////////////////////////////////////
         
    if(mysql_num_rows($search_result) != 1) {
         
         echo 
    'Sorry, We Found No Search Results For (<font color="red">'.$keywords.'</font>)<br /><br />';
         include(
    "includes/footer.php");
         exit;
         
         }
         
         while(
    $row mysql_fetch_array($search_result)) {
         
         
    ## get all the data associated with the search...///////////////////////////////////
         
    $results $row['post_body'];
         
    $results_id $row['user_id'];
         
         
    ## get the usersname ###############################################################
         
    $query1 "SELECT * FROM `membership` WHERE `id`='$results_id'";
         
    $result1 mysql_query($query1) or die (mysql_error());
         
    $rows mysql_fetch_array($result1) or die (mysql_error());
         
         
    $user_id $rows['id'];
         
    $username $rows['username'];
         
         
    # search results table #############################################################
         
    echo '<table width="400" border="1" cellpadding="0" cellspacing="0" />
               <tr>
               <td bgcolor="#004E98" align="center">BETA SEARCH</td>
               </tr>
               <tr>
               <td align="center" />'
    .$results.'</td>
               </tr>
               <tr>
               <td bgcolor="#004E98" align="center"><b><font color="#ffffff" />Was Posted By: '
    .$username.'</font></b></td>
               </table><br />'
    ;
              
         }
              
         include(
    "includes/footer.php");
         exit;
              
         } else {
              
            echo 
    '<br /><center><h4>Search Forums</h4></center>';
            echo 
    '<table width="300" border="1" cellpadding="0" cellspacing="0" />
                  <form action="" method="POST" />
                  <th align="center" bgcolor="#004E98" /><font color="#ffffff">Type In Keywords To Search Forum</th>
                  </tr>
                  <tr>
                  <td align="center"><input type="text" name="keywords" size="40" /></td>
                  </tr>
                  <tr>
                  <td align="right" bgcolor="#004E98" /><input type="submit" name="submit" value="Search Forum" /></td>
                  </table></form><br />'
    ;
                  
            include(
    "includes/footer.php");
            exit;
            
         }
         
    ## isset code ######################################################################  
    ?>
    i'm not to sure how to go about searching both forum_posts and forum_topicss at the same time.

    any help would be great

    cheers

    Graham

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    are the tables related? if so, use a join query and search them both at once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep they are related through the "topic_id" integer, (is that what you mean)would i just put:

    PHP Code:
    $search_query "SELECT * FROM `forum_topics.forum_posts` WHERE (`post_body.post_body` LIKE '%$keywords%')"
    thanks mate

    Graham

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    like this --
    Code:
    SELECT forum_topics.topic_id
         , forum_posts.post_body
         , forum_posts.user_id
      FROM forum_topics
    INNER
      JOIN forum_posts
        ON forum_posts.topic_id = forum_topics.topic_id
     WHERE forum_topics.topic_title LIKE '&#37;$keywords%'
        OR forum_posts.post_body LIKE '%$keywords%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    great, thanks mate

    Graham


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
  •