Not so simple query (or am i wrong ??)
Moderator: General Moderators
Not so simple query (or am i wrong ??)
Here is the table:
+----------+----------+
| mess_id | reply |
+----------+----------+
| 1 | 0 |
+---------------------+
| 2 | 1 |
+---------------------+
| 3 | 1 |
+---------------------+
| 4 | 2 |
+---------------------+
| 5 | 0 |
+---------------------+
....
I need a query that gets all the replies for, let's say, message 1 and the
replies to that replies and so on.
So for message 1 should rerurn : rows 2,3,4
2 should return : row 4
3 should return : nothing
More precise : Get all replies for message X and all the replies to that replies.
I'd apreciate any help.
+----------+----------+
| mess_id | reply |
+----------+----------+
| 1 | 0 |
+---------------------+
| 2 | 1 |
+---------------------+
| 3 | 1 |
+---------------------+
| 4 | 2 |
+---------------------+
| 5 | 0 |
+---------------------+
....
I need a query that gets all the replies for, let's say, message 1 and the
replies to that replies and so on.
So for message 1 should rerurn : rows 2,3,4
2 should return : row 4
3 should return : nothing
More precise : Get all replies for message X and all the replies to that replies.
I'd apreciate any help.
- redhair
- Forum Contributor
- Posts: 300
- Joined: Fri May 30, 2003 4:36 pm
- Location: 53.23N-6.57E
- Contact:
this was tested:
btw...in your example, you say:
working example: http://redhair.nl.nu/projects/test.php
Code: Select all
<?php
function get_messages($reply_id)
{
include "db_connect.php";
$table="yourtable";
print "mess_id:" . $reply_id . "-has these replies:<ul>";
$sql = "SELECT reply FROM $table WHERE reply="$reply_id" order by reply DESC";
$result = @mysql_query($sql,$connection) or die("Couldn't execute query1.");
$nb=mysql_numrows($result);
for($i=0;$i<$nb;$i++){
$reply=mysql_result($result,$i,"reply");
?>
<li><? print $reply; ?></li>
<?
}
print "</ul>\n";
}
$sql = "SELECT mess_id FROM $table order by mess_id";
$result = @mysql_query($sql,$connection) or die("Couldn't execute query2.");
$nb=mysql_numrows($result);
for($i=0;$i<$nb;$i++){
$reply_id=mysql_result($result,$i,"mess_id");
$list_replies .= get_messages($reply_id);
echo $list_replies;
}
?>But i think 4 should not show as reply of message 1, only 2 and 3 seems to be replies to 1. (strictly according to your example..am i right?)So for message 1 should rerurn : rows 2,3,4
working example: http://redhair.nl.nu/projects/test.php
Your code is good. But it doesn't do what i wanted (but it's close).
I think i didn't explained verry well what i wanted so i'll try again :
I don't want to get only the exact replies to a message. I want to get besides them
the replies to the replies of the original message too.
(like a binary tree with the starting point being the main message)
So for message 1 you have the direct replies 2,3
but 4 being a reply to message 2 and message 2 is a reply to message 1
should get included as a reply to message 1.
1
|--2
|-----4
|--3
this is an exemple of the tree that i must form for message 1.
Sorry if I didn't explain it verry well.
I think i didn't explained verry well what i wanted so i'll try again :
I don't want to get only the exact replies to a message. I want to get besides them
the replies to the replies of the original message too.
(like a binary tree with the starting point being the main message)
So for message 1 you have the direct replies 2,3
but 4 being a reply to message 2 and message 2 is a reply to message 1
should get included as a reply to message 1.
1
|--2
|-----4
|--3
this is an exemple of the tree that i must form for message 1.
Sorry if I didn't explain it verry well.
recursive i think is what you need...
Maybe you need this.....
PS: skill usage 1%

Code: Select all
function show_your_skill($parrent=0){
$query = "SELECT * FROM `forum` WHERE parent_id='".$parrent."'";
if(!$result = mysql_query($query)){
echo mysql_error();
echo $query;
}
echo "<UL>";
while($row = mysql_fetch_array($result)){
echo "<LI>".$row["title"];
show_your_skill($row["id"]);
}
echo "</UL>";
}PS: skill usage 1%
That's the way but ...
the thing is I need a number of queries that doesn't grow up when the level of replies grows.
This way if you have 100 replies to the message and evry reply being a reply to annother one you'll get to run over 100 queries, not verry effective.
But this is the closest I've got too, to the answer to this problem.
The biger problem is that I need a way to do it in a fixed number of queries.
the thing is I need a number of queries that doesn't grow up when the level of replies grows.
This way if you have 100 replies to the message and evry reply being a reply to annother one you'll get to run over 100 queries, not verry effective.
But this is the closest I've got too, to the answer to this problem.
The biger problem is that I need a way to do it in a fixed number of queries.
Fabian Pascal's Pratical Issues in Database Manangement has a chapter on this type of query. with some possible solutions. I don't have the book with me or I could provide more details. I believe he had a solution, given a specific database design, that didn't need recursive queries OR Oracle's CONNECT BY.