Not so simple query (or am i wrong ??)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
uncoDMX
Forum Newbie
Posts: 7
Joined: Thu Jun 05, 2003 5:49 am
Location: Romania
Contact:

Not so simple query (or am i wrong ??)

Post 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.
User avatar
redhair
Forum Contributor
Posts: 300
Joined: Fri May 30, 2003 4:36 pm
Location: 53.23N-6.57E
Contact:

Post 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
uncoDMX
Forum Newbie
Posts: 7
Joined: Thu Jun 05, 2003 5:49 am
Location: Romania
Contact:

Post 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.
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post 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% 8) 8) 8)
uncoDMX
Forum Newbie
Posts: 7
Joined: Thu Jun 05, 2003 5:49 am
Location: Romania
Contact:

Post 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.
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

Find a recursive database :) mysql 5?
uncoDMX
Forum Newbie
Posts: 7
Joined: Thu Jun 05, 2003 5:49 am
Location: Romania
Contact:

Post 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.
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post 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?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
uncoDMX
Forum Newbie
Posts: 7
Joined: Thu Jun 05, 2003 5:49 am
Location: Romania
Contact:

Post 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.
Post Reply