Select From 2 Table With ID AND ParentID

Question

I have 2 table : 1.message 2.replaytomessage

tables have 2 same filed : 1.title 2.matn

in message store topic and in replaytomessage store replay to topic replaytomessage link to message with parentmid filed

i want write search in both table for example i search 'test' , select query find in title & matn in message and replaytomessage

i write this query :

$result = mysql_query("SELECT * FROM message inner Join replaytomessage On  message.mid = replaytomessage.parentmid WHERE message.matn LIKE '%$qfind%' OR message.title LIKE '%$qfind%' ORDER BY message.mid DESC  LIMIT $start, $per_page") or die(mysql_error());

but in result show replaytomessage data or no correct title...

this is my While :
while($row = mysql_fetch_array( $result )) {
    echo "<tr>";
    echo "<td width = '300'>";
    $title = mysql_real_escape_string($row['title']);
    $mid = intval($row['mid']);  
    $member = intval($row['member_id']);
    echo "<a href = 'message.php?mid=$mid'>$title</a>";
    echo "</td>  ";

}

Answer 1

I have a ticket system with php/mysql , i want search in topics and answer top topic tables. topic store in message table and answer to topic store in replaytomessage

for example when i searching 'test' word , i want show result from both table(message,replaytomessage) , maybe someone send topic Included 'test' word or someone answer to topic Included 'test' word.

I have 2 table : 1.message 2.replaytomessage tables have 2 same filed : 1.title 2.matn in message store topic and in replaytomessage store replay to topic data , replaytomessage link to message with parentmid filed

i dont know i must user UNION OR Join!

Answer 2

I haven't entirely understood what you are trying to achieve, but my suggestion would be to write the query in a database (MySQL?) client until you get the results you are expecting, and then translate into code (PHP?).

From what I think I understand, you need to give aliases to your columns to retrieve the correct data:

SELECT message.title as messagetitle, replaytomessage.title as replaytitle,
message.mid as messagemid, replaytomessage.mid as replaymid,
message.member_id as messagememberid, replaytomessage.member_id as replaymemberid
FROM message ...

then you can use the aliases to get the data from the table that you want