searching 2 tables at once

[graham23s]

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

[r937]

are the tables related? if so, use a join query and search them both at once

[graham23s]

yep they are related through the "topic_id" integer, (is that what you mean)would i just put:

$search_query = "SELECT * FROM `forum_topics.forum_posts` WHERE (`post_body.post_body` LIKE '%$keywords%')"; 

thanks mate

Graham

[r937]

like this --

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 '%$keywords%'
    OR forum_posts.post_body LIKE '%$keywords%'

[graham23s]

great, thanks mate

Graham