Help needed in analysing a table struct and coding

psmshankar

He all,

I am building a small discussion board in my application. I have a table called 'Message' wherein i am storing the discussions...
Well, my case is :
a post can hav many replies and for each reply there can be a comment or reply.
Lets say
A starts the thread.
B replies to A.
C comment on B.
D also comment on B.
E replies to A.
F replies to A.
G replies to F.
G may also replies to A

so its like tree structure.... so i need to store these data and display accordingly like tree structure(may be not tree structure but indented correctly for good view)

My structure of 'Message' table is:

PostID tinyint(11) unsigned auto_increment
Message varchar(255)
PostedBy varchar(15)
ParentID tinyint(11) unsigned
BranchTutorID tinyint(11) unsigned
DatePosted datetime
Status char(1)
Unit tinyint(3) unsigned

currently what i am storing is..consdering the above example...
the first record in message table will be (i am writing only few column values which is imp..considering all users are from same unit)
PostID PostedBy ParentID Unit
First Record 1 A 0 1
Second Record 2 B 1 1
Third Record 3 C 2 1
Fourth Record 4 D 2 1
in the above records, ParentID '0' means its new thread...ParentID=1 means this post is a reply/comment for the PostID 1 and so on...

i want to display the whole discussion messages for a particular unit...
i am struck in that.. 🙁
currently i am using ParentID to denote for which post the reply is...
I am using PHP 4.1/MySQL 3.23

I don't know whether the table design is correct or not...
Could you please suggest me how to accomplish this...
Please help me..
🙁

Thanks in advance...
Shankar

barand

Sounds like all you need is a query to select those with parent = 0 and call a recursive function which prints message then prints its child messages.

So each time a child message prints it prints its child messages.

Keep track of the recursion level so you can indent children.

Do not forget to exit when no children!

Weedpacket

This article might give you some further ideas. If you want to get hardcore, there's Slash, and it's PHPification at http://phpslash.sourceforge.net/

psmshankar

weedpacket,
read that article...so now my table struct is ok..
now i am struck up with how to display...

barand,
yup u r right ....struck up to display correctly with indent...

psmshankar

i have written PHP script to display it....but the thing is i cannot able to indent the posts....

following is the HTML version of my data in Message table(copied as HTML and pasted here ..using MySQLFront)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> 

<html> 
<head> 
  <title>localhost: test.message</title> 
  <meta name="GENERATOR" content="MySQL-Front 2.2"> 
  <style type="text/css"><!-- 
    .header {background-color: #C0C0C0;} 
    th,td {vertical-align: top; background-color: #FFFFFF;} 
  //--></style> 
</head> 

<body> 

<h3>localhost: test.message (9 Records)</h3> 

<table border=1> 
  <tr class="header"> 
    <th>PostID</th> 
    <th>Message</th> 
    <th>PostedBy</th> 
    <th>ParentID</th> 
    <th>BranchTutorID</th> 
    <th>DatePosted</th> 
    <th>Status</th> 
    <th>Unit</th> 
  </tr> 
  <tr> 
    <td>1 </td> 
    <td>Test Post By Tutor </td> 
    <td>1001022663 </td> 
    <td>0 </td> 
    <td>1 </td> 
    <td>2002-11-03 03:19:27 </td> 
    <td>A </td> 
    <td>1 </td> 
  </tr> 
  <tr> 
    <td>2 </td> 
    <td>Post By Student A </td> 
    <td>A </td> 
    <td>1 </td> 
    <td>1 </td> 
    <td>2002-11-03 03:42:53 </td> 
    <td>A </td> 
    <td>1 </td> 
  </tr> 
  <tr> 
    <td>3 </td> 
    <td>Post By Student B to A </td> 
    <td>B </td> 
    <td>2 </td> 
    <td>1 </td> 
    <td>2002-11-03 03:48:11 </td> 
    <td>A </td> 
    <td>1 </td> 
  </tr> 
  <tr> 
    <td>4 </td> 
    <td>Post By Student C to A </td> 
    <td>C </td> 
    <td>2 </td> 
    <td>1 </td> 
    <td>2002-11-03 03:51:31 </td> 
    <td>A </td> 
    <td>1 </td> 
  </tr> 
  <tr> 
    <td>5 </td> 
    <td>Post By Student D to Tutor </td> 
    <td>D </td> 
    <td>1 </td> 
    <td>1 </td> 
    <td>2002-11-03 04:05:32 </td> 
    <td>A </td> 
    <td>1 </td> 
  </tr> 
  <tr> 
    <td>6 </td> 
    <td>Post By Student E to Tutor </td> 
    <td>E </td> 
    <td>1 </td> 
    <td>1 </td> 
    <td>2002-11-03 04:34:53 </td> 
    <td>A </td> 
    <td>1 </td> 
  </tr> 
  <tr> 
    <td>7 </td> 
    <td>Post By Student F to A </td> 
    <td>F </td> 
    <td>2 </td> 
    <td>1 </td> 
    <td>2002-11-03 05:11:26 </td> 
    <td>A </td> 
    <td>1 </td> 
  </tr> 
  <tr> 
    <td>8 </td> 
    <td>Post By Student G to B </td> 
    <td>G </td> 
    <td>3 </td> 
    <td>1 </td> 
    <td>2002-11-06 11:58:47 </td> 
    <td>A </td> 
    <td>1 </td> 
  </tr> 
  <tr> 
    <td>9 </td> 
    <td>Post By Student H to C </td> 
    <td>H </td> 
    <td>4 </td> 
    <td>1 </td> 
    <td>2002-11-06 04:16:48 </td> 
    <td>A </td> 
    <td>1 </td> 
  </tr> 
</table> 

<br><br> 
<i>generated 06/11/2002 11:09:57 PM by <a href="http://www.mysqlfront.de/">MySQL-Front 2.2</a></i> 

</body></html>

here is my entire code for this

require('connection.php'); 

$CurPostID=0;$CurParentID=0; 
$PrevPostID=0;$PrevParentID=0;    

$TopPos = 3; 
$incCounter = 0; 

$sSQL = "SELECT * FROM Message WHERE Unit=1 AND BranchTutorID=1 "; 
$sSQL .= " ORDER BY PostID,ParentID,DatePosted "; 
$ParentResult = mysql_query($sSQL); 
$No_Of_Parent_Record = mysql_num_rows($ParentResult); 

if($No_Of_Parent_Record >0) 
{ 
   $arr_display = array($No_Of_Parent_Record); 

   while($rsParentResult = mysql_fetch_array($ParentResult)) 
   { 
      $iPostID = $rsParentResult["PostID"]; 
      $CurPostID = $iPostID; 

  $sMessage = $rsParentResult["Message"]; 
  $sPostedBy = $rsParentResult["PostedBy"]; 

  $iParentID = $rsParentResult["ParentID"]; 
  $CurParentID = $iParentID; 

  $iBranchTutorID = $rsParentResult["BranchTutorID"]; 
  $sDatePosted = $rsParentResult["DatePosted"]; 
  $sStatus = $rsParentResult["Status"]; 
  $iUnit = $rsParentResult["Unit"]; 

  if($CurParentID==0) 
  { 
     $sValue = ""; 
     $sValue = "<table border=0><tr><td>Posted By: $sPostedBy</td></tr>"; 
     $sValue .= "<tr><td>Date: $sDatePosted</td></tr> <tr><td>$sMessage</td></tr></table><hr>"; 

     echo $sValue; 
     $arr_display[$incCounter] = $CurPostID; 
     $incCounter++; 
  } 
  else 
  { 
     if(in_array($CurPostID,$arr_display)) 
     { 
        $incCounter = $incCounter; 
     } 
     else 
     { 
        $sValue = ""; 
        $sValue = "<table border=0><tr><td>Posted By: $sPostedBy</td></tr>"; 
        $sValue .= "<tr><td>Date: $sDatePosted</td></tr> <tr><td>$sMessage</td></tr></table><hr>"; 

        echo $sValue; 

        $arr_display[$incCounter] = $CurPostID; 
        $incCounter++; 

     } 

     DisplayChild($CurParentID,$CurPostID); 
  }    
   } 
} 



function DisplayChild($iParentID,$iPostID) 
{ 
   Global $TopPos; 
   Global $incCounter; 
   Global $arr_display;  

   $sSQL = ""; 
   $sSQL = "SELECT * FROM Message WHERE Unit=1 AND BranchTutorID=1 AND ParentID=$iPostID "; 
   $sSQL .= " ORDER BY PostID,ParentID,DatePosted"; 
   $ChildResult = mysql_query($sSQL); 
   $No_Of_Child_Record = mysql_num_rows($ChildResult); 

   if ($No_Of_Child_Record >0) 
   { 
      while($rsChildResult = mysql_fetch_array($ChildResult)) 
      { 
         $iChildPostID = $rsChildResult["PostID"]; 

     $sChildMessage = $rsChildResult["Message"]; 
     $sChildPostedBy = $rsChildResult["PostedBy"]; 

     $iChildParentID = $rsChildResult["ParentID"]; 

     $iChildBranchTutorID = $rsChildResult["BranchTutorID"]; 
     $sChildDatePosted = $rsChildResult["DatePosted"]; 
     $sChildStatus = $rsChildResult["Status"]; 
     $iChildUnit = $rsChildResult["Unit"]; 

     if(in_array($iChildPostID,$arr_display)) 
     { 
        $incCounter = $incCounter; 
     } 
     else 
     { 
        $TopPos = $TopPos + 25; 
        $sValue = ""; 
        //$sValue = "<div style='position:absolute;left:10%;top:$TopPos%'><table border=0><tr><td>Posted By: $sChildPostedBy</td></tr>"; 
        $sValue = "<table border=0><tr><td>Posted By: $sChildPostedBy</td></tr>"; 
        $sValue .= "<tr><td>Date: $sChildDatePosted</td></tr> <tr><td>$sChildMessage</td></tr></table><hr>"; 

        echo $sValue; 

        $arr_display[$incCounter] = $iChildPostID; 
        $incCounter++; 

        DisplayChild($iChildParentID,$iChildPostID); 
     } 
  } 

  //DisplayChild($iChildParentID,$iChildPostID); 
   }    

}

i used recursive funstion to get the childs....
this will display the posts one by one with parent-child relation but no indentation...
getting the result correctly but donno how to indent the posts(display)based on parent-child relation.. i tried by using div tag..but it is not coming correctly... 😕

barand

Try this. I couldn't remember you field names when I built my test table so they will need changing.

Its rough and ready but might inspire you🙂

function printMessage($mid,$level) {
	$q1 = mysql_query("select message, user, msgtime from mybb where msgid=$mid");
	list($message,$user,$msgtime)=mysql_fetch_row($q1);
	echo "<table cellspacing=\"0\">\n";
	/*set up columns*/
	if ($level > 0) 
		echo "<col width=\"100\" span=\"$level\">";
	echo "<col width=\"100\"><col width=\"400\">";
	echo "\n<tr>";
	/*indent according to level*/
	for ($i=0;$i<$level;$i++) { 
		echo "<td>&nbsp;</td>";
	}
	/*print user, time and message*/
	echo "\n<td bgcolor=\"#CCCCCC\" ><B>$user</B><br>$msgtime</td>\n";
	echo "<td bgcolor=\"#CCFFCC\">$message</td></tr></table>\n";
	mysql_free_result($q1);

/*now get any replies to the message*/
$q2 = mysql_query("select msgid from mybb where parentid=$mid");
while ($row = mysql_fetch_array($q2)) {
	/* and print reply increasing level count */
	printMessage($row["msgid"],$level+1);
}
mysql_free_result($q2);
}

Call the top level message with "printMessage($ID, 0)" where 0 is the initial level.

hth