I'm writing a search form for a simple forum, but I cannot manage to find a solution to select only the fields that interest me. The forum table, named "Messages" (MySql) uses this structure:
MessageID - FirstMsg - Content
201 - 0 - Jack and Rose
202 - 201 - Rose
203 - 201 - Jack, Rose, David
204 - 0 - Bill
205 - 204 - Rose
206 - 0 - Rose and David
207 - 206 - David
The main problem is that the php forum page that I'm using recognize the messages with FirstMsg 0 as "parent" and the ones with FirstMsg different from 0 as replies to the one with 0 and MessageID equal to their FirstMsg.
es. row with 201 as MessageID 201 is parent of 202 and 203 that will be viewed as replies to post 201.
With a MySql query and php I easily get the content:
$MySql = "SELECT * FROM Messages WHERE Content LIKE '%$Search%'" ORDER BY Date DESC LIMIT 1, 30";
$Result = mysql_query($MySql);
while($rs = mysql_fetch_array($Result)) {
}
For example, searching Rose I get, echoing the content in the while loop:
201 - 0 - Jack and Rose
202 - 201 - Rose
203 - 201 - Jack, Rose, David
205 - 204 - Rose
206 - 0 - Rose and David
When i need to get, with a query, only the "parent" elements, even of "Rose" is nor present in the parent message but only in one of the replies, like:
201 - 0 - Jack and Rose
204 - 0 - Bill
206 - 0 - Rose and David
It is possible to do this changing only the MySql query? I need that because the query itself is used, among the same page, to navigate between the “pages” generated by the php when their count increases after the max messages per page number. Thanks in advance for any help.
If you only want to get the parent messages:
SELECT DISTINCT p.*
FROM Messages m JOIN Messages p
ON m.FirstMsg = p.MessageID
OR (m.FirstMsg = 0 AND m.MessageID = p.MessageID)
WHERE m.Content LIKE '%$Search%'
ORDER BY m.Date DESC LIMIT 1, 30
If you want to sort by the parent message, change m.Date to p.Date