PHP Comments System (In dire need of help!)

[Conphoid]

Hey, I just need a little help. I'm relatively experienced with PHP but this is baffling me...

 

I have a comments system for a new website I'm working on. The comments system so far works fine.

 

This is what it looks like. As you can see there are 2 comments. Comment 1 and Comment 2.

 

 

I want to add replies to these comments, so each comment may have a number of replies to it. As demonstrated here.

 

 

As you can see the replies to each comment should be directly under it. If a comment has no replies there will simply be a blank space.

 

This is the current code I have so far for the working comments system (not including the replies part). I have labeled most of it so I hope it's understandable.

 

<?php

$id2 = $_GET['id']; // This is the ID users profile which the comments are posted on.

// mySQL Database Connection Here

$query = "SELECT * FROM com WHERE reviewer = '$nomh' || client = '$id2' ORDER BY id DESC"; // This is selecting the appropriate comments to display on the profile page.
// This part might be irrelevant in the problem as it all works fine
// For record, $nomh and reviewer are the user who is commenting, client and $id2 is the ID of the user's profile who is receiving the comments on their page.


$result = mysql_query($query);
$num = mysql_num_rows ($result);
$tot = mysql_num_rows ($result);
mysql_close();

if ($tot > 0 ) {  // Determining how many comments should be shown on the page based on how many comments exist in the database.
$i=0;

while ($i < $tot ) {

$name = mysql_result($result,$i,"reviewer"); // Name of the user posting the comment.
$desc = mysql_result($result,$i,"review"); // The content of the post which was made.
$client = mysql_result($result,$i,"client"); // The ID of the profile page which is receiving the comment.
$avatar = mysql_result($result,$i,"avatar"); // The avatar of the user posting the comment.
$date = mysql_result($result,$i,"date"); // The date the comment was posted.
$nomb = mysql_result($result,$i,"nom"); // The name of the user receiving the comment, of whom the profile page belongs to.
$dat = mysql_result($result,$i,"dat"); // The ID of the user posting the comment.
$id = mysql_result($result,$i,"id"); // The ID of the comment. Which is auto-incrementing.

// Just taking parts of the comments from the database in order to post them on the page (above).

echo "<table class=\"smalltitle\" style=\"width: 100%\"><tr>";
echo "<td style=\"width: 21px\" valign=\"top\" id=\"uu\"><a class=\"woof\" \><img alt=\"Thumbnail Pic\" height=\"50\" src=\"$avatar\" width=\"50\" /><span><table><tr><td><img src=\"$avatar\" width=\"30px\" height=\"30px\" /></td><td valign=\"top\">$name<br><font size=\"1px\">View Profile</font></td></tr></table></span></a></td>";

echo "<td valign=\"top\" class=\"maytire\" id=\"uu\"><span class=\"auto-style24\"><strong><a class=\"auto-style24\" href=\"http://";

echo "chrawl.com/flask.php?id=";

echo "$dat";

echo " \">$name</a> > <a class=\"auto-style24\" href=\"http://chrawl.com/flask.php?id=$client \">$nomb</a> <font size=\"1\">   $date EST</font></size><br /><span class=\"auto-style20\">";

echo $desc;

echo "</span><br /></td></tr></table><br>";

// The table and inputing the information which makes up the comment (above).

++$i; } } else {  // What happens if there are no comments for this user.

echo "<table class=\"boxtrot\" align=\"center\" style=\"width: 100%; align: center; height: 1px\"><tr><td valign=\"top\" style=\"padding:5px\">";
echo "<strong>There are no comments!</strong>"; 
echo "</td></tr></table>";

} ?>

 

I am having a lot of trouble adding replies to this. The replies are on another table, not the one containing the comments in the above code. The replies are on a table called "respond".

 

The information from the table I need to put in this code are "nameo - The name of the user who replied", "commento - The comment of the reply which was made", "oc - A number which is the same as the number of the comment ID in the above code. The plan here was that the replies are placed next to the comment where 'oc' in  Respond table is the same as 'id' in the Comments table".

 

I made a code which connected to the other table for the replies, on table "Respond". And the plan was to get the values and put them so they corresponded to the comments there were supposed to follow... But so far with that the replies don't show up, or if they do they show up underneath the wrong comments.

 

$result = mysql_query($query);
$i=0;
$id = mysql_result($result,$i,"id"); // The ID of the comment from the comments table

$line = "SELECT * FROM respond WHERE oc = '$id'"; // Connecting to the replies database to get the replies
$concave = mysql_query($line);

 

I don't know what to do. I know this might be quite complex, or at least the way I explained it might be bad.

 

I extremely appreciate any help with this, you don't even know how much it means to me. I will help answer any questions or anything you need, and any suggestions are much welcomed.

 

Thanks a lot in advance.

[WebStyles]

your idea seems fine, a table for comments, and a table for answers, linked by commentID.

 

all you need is to have stuff in the right order:

 

while(READING EACH COMMENTS){

1. print first comment

2. check for answers. if they exist print them all.

}

[Conphoid]

your idea seems fine, a table for comments, and a table for answers, linked by commentID.

 

all you need is to have stuff in the right order:

 

while(READING EACH COMMENTS){

1. print first comment

2. check for answers. if they exist print them all.

}

 

I just can't get the replies and the comments to link up. Might be I am just so frustrated by this stage that it is clouding my judgement on how to put this thing together but nothing I seem to try works. I really need breastfed through what to do here because I am completely lost.

 

But I understand the structure of what you're saying. I just can't do it.

[WebStyles]

I would do something like this (table structure)

 

COMMENTS:

id,comment,author,date,time

 

ANSWERS:

id,commentID,answer,author,date,time

 

making sure that the id from table COMMENTS has the same values as commentID from table ANSWERS

[xyph]

Two ways

 

One

QUERY-----------
SELECT `comment`, `id`, `parent`
FROM `comments`
ORDER BY IF( `parent` = 0, `id`, `parent`), `id`

TABLE/DATA DUMP-----------
CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) NOT NULL,
  `comment` text NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

INSERT INTO `comments` (`id`, `parent`, `comment`) VALUES
(1, 0, 'hello'),
(2, 0, 'foo'),
(3, 0, 'loner'),
(4, 1, 'world'),
(5, 2, 'bar'),
(6, 2, 'baz');

RETURNS-----------
+---------+----+--------+
| comment | id | parent |
+---------+----+--------+
| hello   |  1 |      0 |
| world   |  4 |      1 |
| foo     |  2 |      0 |
| bar     |  5 |      2 |
| baz     |  6 |      2 |
| loner   |  3 |      0 |
+---------+----+--------+

 

TWO (more redundant)

QUERY-----------
SELECT `c`.`comment`, `c`.`id`, `r`.`comment` as `reply`, `r`.`id` as `replyid`
FROM `comments` `c`
LEFT JOIN `replies` `r`
ON `c`.`id` = `r`.`cid`

TABLE/DATA DUMP-----------
CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comment` text NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `comments` (`id`, `comment`) VALUES
(1, 'hello'),
(2, 'foo'),
(3, 'loner');

CREATE TABLE IF NOT EXISTS `replies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `comment` text NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `replies` (`id`, `cid`, `comment`) VALUES
(1, 1, 'world'),
(2, 2, 'bar'),
(3, 2, 'baz');

RETURNS-----------
+---------+----+-------+---------+
| comment | id | reply | replyid |
+---------+----+-------+---------+
| hello   |  1 | world |       1 |
| foo     |  2 | bar   |       2 |
| foo     |  2 | baz   |       3 |
| loner   |  3 | NULL  |    NULL |
+---------+----+-------+---------+

 

I would disagree. Use my first example. Store all the comments in one table. If it's a reply, populate a `parent` column with the ID of the comment it's replying to. Otherwise, leave it at 0. With the IF in the WHERE clause, it does the sorting for you.

 

You can also keep all of your comments in one table.

[xyph]

Made a 'better' query for the first example

 

Query

SELECT  `c`.`comment` ,  `c`.`id` ,  `r`.`comment` AS  `reply` ,  `r`.`id` AS  `replyid` 
FROM  `comments`  `c` 
LEFT JOIN  `replies`  `r` ON  `c`.`id` =  `r`.`cid` 
UNION 
SELECT  `comment` ,  `id` , NULL , NULL 
FROM  `comments` 
ORDER BY  `id` ,  `replyid`

 

Result

+---------+----+-------+---------+
| comment | id | reply | replyid |
+---------+----+-------+---------+
| hello   |  1 | NULL  |    NULL |
| hello   |  1 | world |       1 |
| foo     |  2 | NULL  |    NULL |
| foo     |  2 | bar   |       2 |
| foo     |  2 | baz   |       3 |
| loner   |  3 | NULL  |    NULL |
+---------+----+-------+---------+