Help, MYSQL Search Function

[barqersbite]

Okay, I am creating a simple forum for my Grade 12 Summative Project, and am having a hard time searching the forum. I have two tables which i would like to search, setup like so:

table1
---------
post_id - AI primary
poster_username
post_subject
post_text
post_time - stored as time() would spit out

table2
---------
reply_id
reply_postid
reply_username
reply_time
reply_text

So, searching the posts subject/text works, I simply do this:

$result = mysql_query("SELECT * FROM posts WHERE post_subject LIKE '%$search%' OR post_text LIKE '%$search%'");

Then Display it like so:

date_default_timezone_set('America/Montreal');							
									
while($r=mysql_fetch_array($result))							
{								   
//the format is $variable = $r["nameofmysqlcolumn"];							  
//modify these to match your mysql table columns								
$date = $r['post_time'];
								
$date = date("F j, Y, g:i a", $date); 
								
if ($r['post_reported'] != '0')									
     echo "<tr><td height=40><img src=images/reported.png />{$r['poster_username']}</td><td width=400px><a href=postview.php?post={$r['post_id']}>{$r['post_subject']}</a></td><td>".$date."</td></tr>";

else
      echo "<tr><td height=40>{$r['poster_username']}</td><td width=400px><a href=postview.php?post={$r['post_id']}>{$r['post_subject']}</a></td><td>".$date."</td></tr>";
}

However, I want to search the reply table (table2) for reply_text containing the search keyword. Then not display the reply, but display the actual post corresponding to the reply_postid. So I did a search like so:

$result2 = mysql_query("SELECT reply_postid FROM reply WHERE reply_text LIKE '%$search%'");

But then I'm not sure how to combine result 1 and 2 together so that result will also gather the posts which correspond to reply_postid. Also, I don't want the same post shown several times, so I don't know how to remove duplicates in the $result2 variable.

Thanks guys!

[diafol]

table1
---------
post_id - AI primary
poster_username
post_subject
post_text
post_time - stored as time() would spit out

table2
---------
reply_id
reply_postid
reply_username
reply_time
reply_text

Try this sql:

$r=mysql_query("SELECT DISTINCT t1.* FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.post_id = t2.reply_postid WHERE t1.post_subject = '%$search%' OR t1.post_text = '%$search%' OR t2.reply_text = '%search%'");
while($d=mysql_fetch_array($r)){

//spit out the data you need in the format required
//e.g. echo $d['post_subject'] etc. No need for 't1.' prefix in the variables

}

Perhaps you know all this, but just in case:
The INNER JOIN simply links two tables together. This will be familiar to you if you've used Access - Query View.
The DISTINCT ensures only unique records are returned. It could be that a post would be returned a number of times due to the search term being found in many of the replies - DISTINCT stops this.
AS is the 'alias' - just shortens the table prefix.

[barqersbite]

I tried what you suggested, and it works sometimes and other times it doesn't.

So this is what I modified it to:

$d=mysql_query("SELECT DISTINCT t1.* FROM posts AS t1 INNER JOIN reply AS t2 ON t1.post_id = t2.reply_postid WHERE t1.post_subject LIKE '%$search%' OR t1.post_text LIKE '%$search%' OR t2.reply_text LIKE '%search%'");

And it does search, but it doesn't seem to pickup the reply_text. Like if I make a reply of "Reply Section", and then search section, it returns no results. However, if I make a topic (which goes into table 1 - posts) and it has "section" in the posts_text, then it works, but if I put "Section" in the posts_subject, then it won't return any results. So it works for the post_text, but not the post_subject or reply_text. I'll keep checking it out!

Also, it only seems to work with topics that have replies in them.

[barqersbite]

Okay, I got it to search replies, I was missing the $ for the search in the reply_text LIKE statement, but it still won't search topics which do not have replies. That's the only issue now!

[diafol]

No, it won't - due to the INNER JOIN - change it to LEFT JOIN.

$d=mysql_query("SELECT DISTINCT t1.* FROM posts AS t1 LEFT JOIN reply AS t2 ON t1.post_id = t2.reply_postid WHERE t1.post_subject LIKE '%$search%' OR t1.post_text LIKE '%$search%' OR t2.reply_text LIKE '%$search%'");

[barqersbite]

No, it won't - due to the INNER JOIN - change it to LEFT JOIN.

$d=mysql_query("SELECT DISTINCT t1.* FROM posts AS t1 LEFT JOIN reply AS t2 ON t1.post_id = t2.reply_postid WHERE t1.post_subject LIKE '%$search%' OR t1.post_text LIKE '%$search%' OR t2.reply_text LIKE '%$search%'");

Awesome! That works perfectly now, thank you sir Ardav.