forum searching and results

[graham23s]

Hi Guys,

 

i have coded a search function into my forum still quite basic in my sql i have 2 main tables

 

forum_posts

forum_topics

 

i'm having trouble seacrhing both tableas at the 1 time heres my full 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;
     
     }
     
     # search results table #############################################################
     echo '<table width="90%" border="1" bordercolor="#000000" cellpadding="2" cellspacing="0" />
           <tr>
           <td colspan="6" bgcolor="#004E98" align="center"><font color="#ffffff" /><b>BETA SEARCH (Still In Construction)</b></font></td>
           </tr>
           <tr>
           <th bgcolor="#004E98" align="center" width="5%"/><font color="#ffffff">ID</th><th bgcolor="#004E98" align="center" width="10%"/><font color="#ffffff">Forum</th>
           <th bgcolor="#004E98" align="center" width="55%"/><font color="#ffffff">Topic</th><th bgcolor="#004E98" align="center" width="10%"/><font color="#ffffff">Author</th>
           <th bgcolor="#004E98" align="center" width="5%"/><font color="#ffffff">Replies</th><th bgcolor="#004E98" align="center" width="5%"/><font color="#ffffff">Views</th>
           </tr>
           <tr>';
     
     while($row = mysql_fetch_array($search_result)) {
     
     ## get all the data associated with the search...///////////////////////////////////
     $result_post_id = $row['id'];
     $results = $row['post_body'];
     $results_id = $row['user_id'];
     $topic_post_id = $row['topic_post_id'];
     
     $query3 = "SELECT * FROM `forum_topics` WHERE `topic_id`='$topic_post_id'";
     $result3 = mysql_query($query3);
     $row2 = mysql_fetch_array($result3);
     ## get replies and views ###########################################################
     
     $replies = $row2['replies'];
     $views = $row2['views'];
     
     ## 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'];
     
     ## get the forum name ##############################################################
     $query2 = "SELECT * FROM `forum_sections` WHERE `id`='$topic_post_id'";
     $result2 = mysql_query($query2);
     $section_name = mysql_fetch_array($result2);
     $sect_name = $section_name['subject'];
     
     ## echo out the results ############################################################
     echo '<td>'.$result_post_id.'</td><td>'.$sect_name.'</td><td align="center"><a href="read_forum.php?id='.$result_post_id.'">'.$results.'</a></td><td><a href="user_details.php?id='.$user_id.'"/>'.$username.'</a></td><td>'.$replies.'</td><td>'.$views.'</td>';
     
                     
     }
     
     echo '</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 ######################################################################     
        
     
     }
     ####################################################################################
     // get forum main posts...//////////////////////////////////////////////////////////
     $query = "SELECT * FROM `forum_sections`";
     $result = mysql_query($query) or die (mysql_error());
     
     echo '<br /><center><h4>Forums</h4></center>';
     echo '<table width="600" border="1" bordercolor="#000000" cellpadding="2" cellspacing="0" />
           <tr>
           <th bgcolor="#004E98" /><font color="#ffffff"> </font></th><th bgcolor="#004E98" /><font color="#ffffff">Forum</font></th><th bgcolor="#004E98" /><font color="#ffffff">Topics</font></th><th bgcolor="#004E98" /><font color="#ffffff">Posts</font></th><th bgcolor="#004E98" /><font color="#ffffff">Last Poster</font></th>
           </tr>
           <tr>';
     while ($row = mysql_fetch_array($result)) { 
     
     $id = $row['id'];
     $subject  = $row['subject'];  
     $description  = $row['description'];
     
     $query0 = "SELECT * FROM `forum_posts` WHERE `topic_post_id`='$id'";
     $result0 = mysql_query($query0);
     $number_of_posts = mysql_num_rows($result0);
     
     $query2 = "SELECT * FROM `forum_topics` WHERE `topic_id`='$id'";
     $result2 = mysql_query($query2) or die (mysql_error());     
     $number_of_topics = mysql_num_rows($result2);
     
     ## Last poster code...//////////////////////////////////////////////////////////////
     
     $query3 = "SELECT * FROM `forum_sections` WHERE`id`='$id'";
     $result3 = mysql_query($query3);
     $rows = mysql_fetch_array($result3); 
     
     $last_poster = $rows['last_poster'];      
     // get the id to make the name clickable.../////////////////////////////////////////
     $query4 = "SELECT * FROM `membership` WHERE `username`='$last_poster'";
     $result4 = mysql_query($query4);
     $rows_id = mysql_fetch_array($result4);
     
     $last_poster_id = $rows_id['id'];
     
     $query_topics = "SELECT * FROM `forum_topics`";
     $result_topics = mysql_query($query_topics);
     
     $topics_made = mysql_num_rows($result_topics);
     
     $query_posts = "SELECT * FROM `forum_posts`";
     $result_posts = mysql_query($query_posts);
     
     $posts_made = mysql_num_rows($result_posts);  
               
     ## Last poster code...//////////////////////////////////////////////////////////////
          
           echo '<td bgcolor="#004E98"><img src="images/forum_icon.gif"/></td><td><a href="view_forum.php?id='.$id.'" />'.$subject.'</a><br /><font size="1" />'.$description.'</font></td><td>'.$number_of_topics.'</td><td>'.$number_of_posts.'</td><td><a href="user_details.php?id='.$last_poster_id.'" />'.$last_poster.'</a></td></tr>';

     }      
           echo '<td align="left"colspan="5" bgcolor="#004E98"><b><font color="#ffffff" size="1">Our Users Have Posted (<font color="yellow"><b>'.$topics_made.'</b></font>) Topic(s) And Made (<font color="yellow"><b>'.$posts_made.'</font>) Post(s).</td>
                 </table><br />';
                 
     ####################################################################################
     echo '<center><b>Options: </b><a href="forums.php?action=search">Search Forum</a><br /><br />';
?>
<?php
     // include the footer...////////////////////////////////////////////////////////////
     include("includes/footer.php");
?>

 

and the search query:

 

     ## 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());

 

also i have noticed the search doesnt display the same word more than once if i search for graham23s it will display it, if i post graham23s again and search again it shows no results.

 

any help would be appreciated

 

Graham