SQL Comment table insert statement

Question

Hi there,

I want to develop a system where a user should be able to post the comments on the author published news.

I am very much confused about the Insert Statement that i should be using to store the user commenting system, i have two mysql table one is news and another is comments below is the screenshot of two tables.

news

comments

in the comments table i have defined a foreign key (new_id) , in which i want to store the value that is related to the particular news for example a news with id no. 7, how do i achieve this dynamic feat? how do i automatically relate it to the news when a user post the comment (nevertheless to say that the user will be giving the input from the form )?

EDIT : I want to use One news article on one page.

thank you

Answer 1

Generally that id (the id of the entity you're attaching something to) is either in the URI the form is POSTed to, or is simply a hidden element in the form.

For example:

<?php 
  //somehow you need to set this value, if the comment form is on the same
  //page as the news then you should already have this id. If not, then you
  //have to provide the 'stand-alone' comment page with the id you expect it
  //to be using
  $new_id = 7 
<form method='post' action='/news/<?php echo $new_id ?>/comment/'>
  <input type='hidden' name='new_id' value='<?php echo $new_id ?>'>
  <input tyle='text' name='Name'>
  ...
</form>

With that form you can either parse the URI to determine what the foreign key should be, or use the hidden field.

Update: Showing how to use both $_GET and $_POST (so you don't have to parse the URI):

<form method='post' action='/comments/?new_id=<?php echo $new_id ?>'>

As always, check all user input, regardless of where it comes from (the URI, a POST a GET).

Answer 2

Well first off you need to know how you are going to view a news item? Is this going to have all news articles on one page and below each news article is a to post new comments? If so then each of these forms generated per news article should have the news ID in the form potentially as .

Example:

<p>News article 1.</p>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
   <input type="hidden" name="new_id" value="1"/>
   <textarea name="comments"></textarea>
   <input type="submit" name="submit" value="Post COmment"/>
</form>

<p>news article 2</p>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
   <input type="hidden" name="new_id" value="2"/>
   <textarea name="comments"></textarea>
   <input type="submit" name="submit" value="Post COmment"/>
</form>

Then on this page at the top you can check for whether or not user pressed submit button:

<?php
  if(isset($_POST['submit'])){
       //$_POST['new_id'] is news article id 
       //$_POST['comments'] is comments for this 
       //sql to store new_id = $_POST['new_id'] and comments = $_POST['comments']
  {

---

Alternatively: Lets say on your home page you have links to each news article and you retrieve them on subsequent page using $_GET. So index.php displays news and getNews.php is where news is displayed. You could want to on index.php generate a link to getNews.php?id=

THis way on getNews.php you know which news article to get using $_GET['id'] and you can easily post comments to this using a similar technique to above, take $_GET['id'] and toss it into your form on getNews.php as hidden field.

Caution: be careful and sanitize your $_GET variable before using it. ?>

Answer 3

first your structure looks good. i assume "new_id" is id of the newspost! i would switch from datetime to timestamp. its range is smaller but i dont think you are gonna have posts in the past? and it has additional features like automatical timezone conversion.

anyways! the usual approach is to include the "news_id" as a hidden form field in the form that is used to submit the comment!

then you can fetch it with $_POST["whatever-you-named-it"];

and then you construct your insert statement... dont' forget to mysql_real_escape_string() every user supplied data to avoid mysql injection.

Answer 4

you could add an hidden input field to your comments form like this:

<input type="hidden" name="new_id" value="7"/>

Then in your php code you get the value via $_POST['new_id'] or $_GET['new_id'] depending on what method you're using.

The you can use the following code to generate the SQL:

$new_id = mysql_real_escape_string($_POST['new_id']);
$comment = mysql_real_escape_string($_POST['comment']);
$sql = "INSERT INTO comments (comment,new_id) VALUES ('$comment','$new_id')"

If shortened it, you still have to add the other values. But I hope now it's clear how you can do this.

---

If you don't want to use the hidden field you can add a get parameter to the action url like this:

<form action="your_script.php?new_id=<?= $new_id ?>">

Then you get it as $_GET['new_id'].

---

Update:

If you're concerned for security and want to make sure nobody ist trying to forge a request, you should take a look at http://www.codewalkers.com/c/a/Miscellaneous/Stopping-CSRF-Attacks-in-Your-PHP-Applications/1/

Answer 5

You asked about the SQL INSERT statement, so I assume you are concerned simply with the SQL...

Using AUTO_INCREMENT, LAST_INSERT_ID(), and TRANSACTION...

Set [news].[id] to be an AUTO_INCREMENT value type. Then using a transaction, you should be able to do something like this:

START TRANSACTION;
INSERT INTO news VALUES('2010-08-21','','','','','')
INSERT INTO comments VALUES(,'2010-08-21','','','','','',1,LAST_INSERT_ID())
COMMIT;