tags:

views:

76

answers:

5

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

alt text

comments

alt text

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

+1  A: 

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).

Tim Lytle
even i got that hint, but the problem is i am using POST as the form method, and if i have to get the news id from the url using the $_GET method. how do i do this?
Ibrahim Azhar Armar
There's nothing that says you have to use `$_GET` to put data in the URI; however, it certainly is possible to fill both `$_POST` and `$_GET`. I've updated with an example.
Tim Lytle
if i use the hidden input field then how do i know that the integer in the value attribute is the same as the current news (id)? how does it match with the current news?
Ibrahim Azhar Armar
Where's your comment form rendered? Your code should be providing the id to that form page. I updated with more examples.
Tim Lytle
my code will be creating the dynamic news per page per id, for example if i create a post then automatically it will assign a new id and a commenting form will be displayed in the bottom, now how do i tell the input hidden field in this form dynamically that this is the current news id?
Ibrahim Azhar Armar
Whatever code you have that's creating the 'dynamic news per page per id', whatever code is **loading the data from the `news` table** will have the **`new_id`**. So if you have an array of `news` data, just use the `id` element (`$new_id = $news['id']`). If you want an answer that references your code specifically, you need to post the code.
Tim Lytle
thank you tim that was the perfect answer and solved my problem to quite extent, i really appreciate the effort put on by you to explain the things.. thank you once again.
Ibrahim Azhar Armar
+1  A: 

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. ?>

Chris
if i use the hidden input field then how do i know that the integer in the value attribute is the same as the current news(id)? how does it match with the current news?
Ibrahim Azhar Armar
glad this helps, as far as matching comment and respective news its contextual to how you have the site configured. provide more specifics and I can help sort out any problems you are having.
Chris
yes it did help me to quite some extent, i am glad you helped me out in this, your answer was the most precise one, thank you..
Ibrahim Azhar Armar
+1  A: 

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.

Joe Hopfgartner
i would like to know the other way without using the hidden form field..
Ibrahim Azhar Armar
The 'other way' is to put the ID in the URI.
Tim Lytle
+1  A: 

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/

jigfox
if i use the hidden input field then how do i know that the integer in the value attribute is the same as the current news(id)? how does it match with the current news?
Ibrahim Azhar Armar
You can check with `$_SERVER['HTTP_REFERER']` if the request is comming from the right url. But this isn't a security feature since the user can forge the `HTTP_REFERER` like he could change the input value.
jigfox
@Ibrahim Because it is YOUR code which generates this page, this comments form and this input field. So, just write a code which will add that number to the hidden field. That's the way any web-application works.
Col. Shrapnel
@jigfox I wish there was a "downvote a comment" feature. Your comment both pointless and useless. Referer has nothing to do here and there is no security concerns at all. No need to forge anything if it can be just added straight way
Col. Shrapnel
@Col. Shrapnel: *"how do i know that the integer in the value attribute is the same as the current news(id)?"* I interpreted this as *"How do i know the post/get request is coming from the current news page itself?"* I believe the OP is concerned about security, because he wants to be sure the request is coming from the right page. Why didn't you answer, if you know better?!?
jigfox
@Col. Shrapnel: *"Because it is YOUR code which generates this page"* How do you know this. I can create a page with a form with his comments script as `action`. And now I can write comments on his site from my site. Therefore the thing with the referer.
jigfox
@Col. Sharpnael: http://www.owasp.org/index.php/Cross-Site_Request_Forgery_(CSRF)
jigfox
@jigfox then you have to tell him that it just doesn't matter. Do not answer senseless questions. Test it with your common sense first. There is nothing to concern. There is no need to do such a checking, and no way to be sure either.
Col. Shrapnel
@jigfox lol its paranoia :) Don't be so much concerned in security of such a trifle things like just an article feedback. Actually there is no users table in that db schema. So - nothing to forge lol. Again: **no need to steal something that's already is free**. Anyone already can comment any article by default. **No need to spoof an article id if you can just go comment that article!** Understand now?
Col. Shrapnel
**I understand!** Believe it or not! **BUT**: I believe the OP has these concerns and so I'm trying to help him, if he wants to make sure only requests from his page are valid then this is his right! *"how do i know that the integer in the value attribute is the same as the current news(id)?"* makes me believe he wants just this!
jigfox
it is called "disservice". Instead of explaining to the OP that their wish is senseless, you follow their way. You guys never think when answer. That saddens me.
Col. Shrapnel
Good that everybody has the freedom to express his own opinion!
jigfox
Yeah. It makes SO less professional and more enthusiast site.
Col. Shrapnel
Ibrahim Azhar Armar
+1  A: 

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;
Billism
What's in this comment you add here?
Col. Shrapnel
the problem is solved long back i used the html hidden field as the solution to my problem. anyway 1 up for your effort to answer the query.
Ibrahim Azhar Armar