Page 1 of 1
Not so simple query (or am i wrong ??)
Posted: Thu Jun 05, 2003 5:49 am
by uncoDMX
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.
Posted: Thu Jun 05, 2003 7:10 am
by redhair
this was tested:
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;
}
?>
btw...in your example, you say:
So for message 1 should rerurn : rows 2,3,4
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?)
working example:
http://redhair.nl.nu/projects/test.php
Posted: Thu Jun 05, 2003 8:10 am
by uncoDMX
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.
Posted: Thu Jun 05, 2003 8:15 am
by []InTeR[]
recursive i think is what you need...
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>";
}
Maybe you need this.....
PS: skill usage 1%

Posted: Thu Jun 05, 2003 8:32 am
by uncoDMX
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.
Posted: Thu Jun 05, 2003 8:54 am
by []InTeR[]
Find a recursive database

mysql 5?
Posted: Thu Jun 05, 2003 9:04 am
by uncoDMX
I would wait for mysql 5 (actualy I am) but untill it's released I have to make this work on mysql 4.
Maybe there will be suport for recursive queries.
Posted: Thu Jun 05, 2003 9:12 am
by hedge
I think you can do that with a 'connect by' query, that's Oracle syntax but I believe part of the SQL standard. Does your DB support connect by?
Posted: Thu Jun 05, 2003 9:21 am
by nielsene
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.
Posted: Thu Jun 05, 2003 9:28 am
by uncoDMX
I'm using MySQL 4.0 and from what i've read 'connect by' will be supported one day. (maybe in 5.0)
Till then i'll look for that book.