如何为特定帖子制作评论系统并检索它？

Question

I am making question and answer system for a website. I am having an issue here. I want to make users add specific comments for each post. the script I made is similar to Quora.com so you can understand how my script is working... this is how my comment table looks like please take a look at the pic. how can I add specific comments for each post ?

    //code for insert comments to the tables 
    function setComments($connection) {
        if (isset($_POST['commentSubmit'])) {
            $uid = $_POST['uid'];
            $date = $_POST['date'];
            $message = $_POST['message'];

            //sql connection 
            $sql = "INSERT INTO comments (uid,date,message) VALUES ('$uid','$date','$message')";
            $result = $connection ->query($sql);
        }
    }
    //function for get comments from the databse 
    function getComments($connection) {
        $sql = "SELECT * FROM comments";
        $result = $connection ->query($sql);
        while($row = $result->fetch_assoc()) {
            //showing records 
            echo '<div id="comment_box">';
            echo $row['uid'].'<br>';
            //echo $row['cid'].'<br>';
            echo nl2br($row['message']).'<br>';
            echo $row['date'];
            echo '</div>';
            echo '<hr>';
        }
    }

I have made two functions here setComments is for insert comment
other function is for getComments

UPDATED

this question table is for ask question so users can ask the questions .

Answer 1

You just need a post_id in the comments table, so that each comment is related to a specific post. And declare it as a foreign key.

And then both functions set_comments and get_comments, should have a post_id parameter that will be inserted into the comments table and to query those comments for specific post:

INSERT INTO comments (uid,date,message, post_id) VALUES ('$uid','$date','$message', '$post_id');

After that, using this post_id you can query for those comments under specific post by this id.

Note that: try to use prepared statements instead for database operations.

---

Update:

The column you should add as a foreign key, should have the same exact data type as the column in the original table.

So, suppose you have a questions table, with Id as integer. Then the foreign key column you should add to the comments table should be as following:

ALTER TABLE comments ADD question_id INT NOT NULL;
ALTER TABLE comments ADD CONSTRAINT fk_question_id FOREIGN KEY (question_id) REFERENCES questions(id);