CODEWITHSUNDEEP
phpmysql
Mihir Ujjainwal
Mihir Ujjainwal

Reputation: 140

Comment function with MySQL and PHP

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

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

Upvotes: 0

Views: 534

Answers (1)

kero
kero

Reputation: 10638

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)

Upvotes: 1

Related Questions