Comment box with replies

Databases
1

Hello all. Good evening.

I currently have a website were you post comments, but I want there to be a way for people to make replies to those comments. I’m new to SQL and PHP, but I think the way to do this would be to make a new table for the replies and have an ID that connects to the original comments table. How would I go about doing this?

This is what my code currently looks like,

The comments SQL I recently exported:


CREATE TABLE IF NOT EXISTS `confess` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `confess` text COLLATE utf8_unicode_ci NOT NULL,
  `ip` text COLLATE utf8_unicode_ci NOT NULL,
  `date` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=144 ;

Replies table I recently created in PhpMyAdmin


CREATE TABLE IF NOT EXISTS `reply` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idconfess` int(11) NOT NULL,
  `ip` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `date` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `reply` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

The current code on the web page


<?php require_once('Connections/Confession.php'); ?>

<?php include 'site/header.php'; ?>

     <p><strong>Confession </strong>posted on <strong><?php echo htmlspecialchars($row_confess['date']); ?></strong></p>
      <p><?php echo nl2br( htmlspecialchars( $row_confess['confess'] ) ); ?></p>

<?php
//echo replies here
?>

<?php include 'site/footer.php'; ?>

Thanks.

2

you could use a second table, but it’s easier if you use the same table

first, you need to rationalize and standardize your datatypes

in the confess table, you have ip and date as TEXT columns – TEXT is intended for large blocks, e.g. 65K, of, um, text

use VARCHAR(45) and DATETIME

in your reply table, you have a name column, but why wasn’t there one in the confess table?

CREATE TABLE confess
( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
, ip VARCHAR(45) NOT NULL
, postdate DATETIME NOT NULL
, replyto INTEGER
, name VARCHAR(45)
, confess TEXT NOT NULL
);

the replyto column contains the id value, if any, of the confess the reply is made in reply to (you had called this idconfess)

replyto will be NULL for original confessions

3

I just decided to add the name field when I created the second table, I plan on adding one on to the confess table as well, but my main focus is getting this reply feature to work.

4

Ok I created the confess table using the code you have provided.

5

I’m still having issues.

6

oh…

want me to guess what they are?

7

Nevermind.