Comment function with MySQL and PHP

Question

I am making a comment function for every post that comes on website. Brief view :

Every post has an id, when posted.
When the pages loads, MySQL query checks that is there is a table for comment box or not, if not then it creates one.
in the end of every comment table name, it has the post id for ex. if id of a post is 9 then the table name for this post will be feed_comment_9. (the table for comments is in another database)
I was able to connect the comment table and post box in the html view
But, when the INSERT INTO query turn came, i couldn't find a good way

My main question : Is there is any way i can connect these 2 database or can do anything that the insert query will go in the same comment table as of the post.

My codes :

while ($row = $results->fetch_assoc()) {
    //database change and creating and checking tables 
    $dbc->select_db("feed_update_comment");
$create_comment_table = "CREATE TABLE  IF NOT EXISTS feed_comment_" . $row['id'] . "( id MEDIUMINT NOT NULL AUTO_INCREMENT, full_name varchar(40), comment varchar(120), date_time varchar(100), PRIMARY KEY (id))";
$result_create_comment_table = $dbc->query($create_comment_table);
   //creating and checking finishes 



// making the comments box 
    $dbc->select_db("feed_update_comment");
     $select_comment_table = "SELECT * FROM feed_comment_" . $row['id'] . " ORDER BY id";
$result_query_select_comment_table = $dbc->query($select_comment_table );
if(!$result_query_select_comment_table) {
    $result_select_comment_table = array("full_name" => "", "comment"=> "No comments yet.");

}
$id_result_comment = '<script type="text/javascript">var DivId = $(this).parent(".feed_box_id").attr("id");</script>';
echo '<div id="feed_comment_box_' . $row['id'] . '"' . 'class="feed_comment_box_cl"><div id="add_comment_id" class="add_comment_cl">
<form class="comment_form" method="post" action="' .$_SERVER['PHP_SELF']  . '">
<input name="comment_full_name" type="text" class="input_comment_full_name" required>  </input> 
<textarea required name="input_comment_text" type="text" class="input_comment_text" ></textarea><input class="submit_input" name="comment_submit" type="submit"></input> <br>
 </form>' . $id_result_comment .' 
</div><br>
<div class="comment_box_cl">';      
while ($result_select_comment_table = $result_query_select_comment_table->fetch_assoc()) {
echo '<table tabindex="0" class="comment_box"><tr> <td class="comment_text">' . $result_select_comment_table["comment"] . '</td></tr><br>' . 
'<tr> <td class="comment_full_name">' .  $result_select_comment_table["full_name"]. '</td></tr><br>' . 
'<tr><td class="date_time_comment">' . $result_select_comment_table["date_time"] . '</td></tr><br>'
. '</table>';
}
    echo '</div></div>';    
    echo '</div>';          

    //making of comment box finishes


 //insert query (MAIN POINT)
  if ( isset($_POST["comment_submit"]) ) {
        $commenter_name = ($_POST["comment_full_name"]);
    $commenter_comment = ($_POST["input_comment_text"]);
        if (!empty($commenter_name) || !empty($commenter_comment)) {
            $dbc->select_db("feed_update_comment");
            $result_comment_submit = "INSERT INTO feed_comment_" . " (full_name,comment,date_time)" .  " VALUES('$commenter_name','$commenter_comment',DATE_FORMAT(NOW(),'%h:%i %p,  %W %M %e'))";
            $add_comment_submit = $dbc->query($result_comment_submit)
            or die ("<script type='text/javascript'>alert('not working!');</script>");

        }

    }

extra : There are many of these types look at starting the post, down to it comment box. The problem is there will be dozen of it.

There are many of these types

Unless you get lots (and I mean 10.000s) of comments, this is an overkill. Simply have a table comments which has the post id as a field (preferrably indexed and/as(?) foreign key). — kero
– kero, Commented Feb 13, 2015 at 19:01
in the same database ?, could you just help me by showing it in the answers — Mihir Ujjainwal
– Mihir Ujjainwal, Commented Feb 13, 2015 at 19:02
@kingkero hitted exactly on the spot... is there any particular reason you need to create a table for each post comments? if you create a comments table with a foreign key to the post table it solves your problems. — Ramy Deeb
– Ramy Deeb, Commented Feb 13, 2015 at 19:04
I agree completely with @kingkero, except I would change 10.000s to millions. — GolezTrol
– GolezTrol, Commented Feb 13, 2015 at 19:05

kero · Accepted Answer · 2015-02-14 02:57:32Z

1

To know which post a comment belongs to, you can use an input with type="hidden" like this

<input type="hidden" name="comment_post_id" value="' . $row['id'] . '">

and access it like you would any other field via $_POST['comment_post_id'].

Unless you have lots (and you have to consider the definition for lots depending the dbms you're using) of comments, this is an overkill. You create many tables feed_comment_X which contain only few entries.

The basic solution is to have one table comments, which could look like this

CREATE TABLE comments (
  comment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL FOREIGN KEY REFERENCES posts (id) [ON..],
  full_name VARCHAR(32) NOT NULL,
  comment TEXT NOT NULL,
  date_time DATETIME NOT NULL
);

You save all comments in this table and don't need to check for existance of and (if necessary) create a new table on each page load.

To get all comments you simply (using parameterized statements)

SELECT full_name, comment, date_time FROM comments
  WHERE post_id = :post_id

And add a new entry by

INSERT INTO comments (post_id, full_name, comment, date_time) VALUES
  (:post_id, :full_name, :comment, :date_time)

This allows fast operations if you want to get additional info, eg. the number of comments each post has:

SELECT p.id, .., COUNT(c.post_id) AS total_comments FROM posts AS p
  LEFT JOIN comments AS c on c.post_id = p.id
  GROUP BY (p.id)

edited Feb 14, 2015 at 2:57

answered Feb 13, 2015 at 19:11

kero

10.7k5 gold badges44 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mihir Ujjainwal Over a year ago

But, from where will i get the row[id] (:post_id) , when i tried in the while loop , it made copy of a comment for all posts. Should i make the $row global

kero Over a year ago

@MihirUjjainwal I hope my update clears things up. This way you need only one check (outside any loop!) if a has been comment posted

Collectives™ on Stack Overflow

Comment function with MySQL and PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related