discussion board with mysql - query problem

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
jacquesx
Forum Newbie
Posts: 2
Joined: Sun Jan 16, 2005 5:39 pm

discussion board with mysql - query problem

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
jacquesx
Forum Newbie
Posts: 2
Joined: Sun Jan 16, 2005 5:39 pm

forum

Post by jacquesx »

Yes but t still does not bring out the

main topic and replies together.

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
papieros
Forum Newbie
Posts: 8
Joined: Wed Jan 19, 2005 12:15 pm

Post 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()
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

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