Page 1 of 1

discussion board with mysql - query problem

Posted: Sun Jan 16, 2005 5:49 pm
by jacquesx
I'm building a discussion forum but is having trouble retrieving data.

table structure is:

Code: Select all

uid auto inc key
parent int 
name
title
date
message
time
ALL new topics have a parent = 0. If a parent > 0 thrn it is a reply.

to retrieve the main topics, i use this function:

Code: Select all

function gettopics(){
include("config.php");
$query="select * from test where parent='0'  order by date desc ";

 echo  '<ul type="disc">';
if ($r =mysql_query($query))&#123; 


 
 while ($row =mysql_fetch_array($r))&#123; 
$name=$row&#1111;'name'];
$uid=$row&#1111;'uid'];
$title=$row&#1111;'title'];
$message=$row&#1111;'message'];
$date=$row&#1111;'date'];

echo  "<li>";
		   echo "<a href="viewarticle.php?parent=$uid"> $title </a>";
           echo "-- by ($name) $date<br/>";
echo  "</ul>";		   
&#125;
&#125;
&#125;


To retrieve the replies:

$parent=$_GET&#1111;'parent'];
include("config.php");
$query="select uid,name,title,message,parent,date from test where parent=$parent order by date,parent desc ";
//$query="SELECT * FROM test as t,  WHERE t.uid = t.parent ";
 //echo  '<ul type="disc">';
if ($r =mysql_query($query))&#123;
$num=(mysql_num_rows($r));

 //Retrieve records
 while ($row =mysql_fetch_array($r))&#123;
$name=$row&#1111;'name'];
$uid=$row&#1111;'uid'];
$title=$row&#1111;'title'];
$message=$row&#1111;'message'];
$date=$row&#1111;'date'];
//add bullet points
  echo  '<ul type="disc">';
echo  "<li>";
echo "<a href="viewarticle.php?parent=$uid"> $title </a>";
echo "-- by ($name) $date<br/>";
echo  "</li></ul>";
&#125;

&#125;
else&#123;
print "No replies to this thread";
&#125;

?>

What i want is a function or code that can retrieve the main topics with it's replies, as in:

Code: Select all

topic1
  reply(to topic1)
  reply(to topic1)
topic2
  reply(to topic2)
  reply(to topic2)
How do i combine the two functions to retrieve the main topics and replies together?
thanks


feyd | please use formatting!

Posted: Sun Jan 16, 2005 7:12 pm
by feyd
general logic of the query

Code: Select all

SELECT * FROM `posts_table` a
INNER JOIN `posts_table` b ON a.id = b.parent
ORDER BY a.id, b.id

forum

Posted: Tue Jan 18, 2005 7:13 pm
by jacquesx
Yes but t still does not bring out the

main topic and replies together.

Thanks

Posted: Tue Jan 18, 2005 7:41 pm
by feyd
each record returned would be the parent post and one of it's replies.

If you pulled the data via associative, then it'd only be the reply.

Posted: Tue Jan 18, 2005 8:12 pm
by timvw
you are using adjency-list to implement a tree in sql, but there are other (imho better) solutions like fe nested sets... search the web for more info ;)

the problem with adjency list is that you can have a lot of recursion... and thus a lot of queries too... usually this is not a good idea...

Posted: Wed Jan 19, 2005 1:01 pm
by papieros
Why don't you call function which finds replies from gettopics() with 'parent' as parameter?:

function gettopics(){
...
while( $row=mysql_.... ){
...
get_replies( uid );
...
}

}//end gettopics()

Posted: Wed Jan 19, 2005 1:54 pm
by JAM
papieros wrote:Why don't you call function which finds replies from gettopics() with 'parent' as parameter?: <cut>
I'm asuming that you are suggesting that the get_replies(id) in this case would result in a second call to the database... If not, disregard this post. :wink:

This will of course work, but using JOIN's are preferred. Making multible calls to the database takes up more resources, onboard and actual data transfer, and this is often something neglected.