SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: searching 2 tables at once
-
Jun 16, 2007, 03:26 #1
- 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 ######################################################################
?>
any help would be great
cheers
Graham
-
Jun 16, 2007, 03:49 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
are the tables related? if so, use a join query and search them both at once
-
Jun 16, 2007, 04:10 #3
- 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%')";
Graham
-
Jun 16, 2007, 04:56 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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 '%$keywords%' OR forum_posts.post_body LIKE '%$keywords%'
-
Jun 16, 2007, 10:44 #5
- Join Date
- Dec 2006
- Posts
- 430
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
great, thanks mate
Graham
Bookmarks