Comment system

[champmanking]

I am trying to code a comment system where I can leave comments on all pages. Every page has its own id.

I would like a comment box where I can add and see other comments already posted.

This is my code thus far:

db table

`id` INT AUTO_INCREMENT,
`page_id` INT NOT NULL,
`comment_content` TEXT,
`timestamp` TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (`id`)

form

<form name="comment_content" action="addcomment.php" method="post">
  <input type="hidden" id="page_id" value="$page_id" />
  <textarea id="content"></textarea>
  <input type="submit" />
</form>

addcomment.php

<?
$page_id = $_POST['page_id'];
$comment_content = $_POST['comment_content'];

mysql_query('INSERT INTO `comment` (`page_id`, `comment_content`) VALUES('.$page_id.', "'.$comment_content.'");
?>

Can anyone help me with this?

[stoopkid]

Read over your table structure again, so yes, comment_id will be your page_id, but since the names are a little confusing I'd change it from comment_id to page_id.

[EvolutionFallen]

I agree with stoopkid. Since this is a foreign key for a page ID, you should name it page_id. I would also recommend renaming the field called "comment" to "content" or "comment_content". Since your table is already called "comment", having a field with the same name might get confusing. Also, you can use the TEXT datatype for it instead of VARCHAR(1024) (unless you're using VARCHAR for a specific reason).

[champmanking]

Thanks, both. I've edited it.

Now how do I go about using what I have for what I want?

I want to integrate it with this: http://www.daniweb.com/web-development/php/threads/402714

[Graphix]

Adding comment part

1. Database

Alright, let's review: you have a table with four columns: comment_id, page-id, comment_content, timestamp

So your database is set up and you can start writing code.

2. HTML form

You have made an initial form, but does it cover the two columns it needs to fill? page_id, check, comment_content, check. So your form is done.

3. PHP code

You retrieve the values given to you by the form and execute them in a query. So this is done too.

So you're done for the adding comment part (assuming it works, you should test the variables and add a few fake comments to make sure it works). Also it's not really secure, in my experience, you should a captcha or some sort of verification before you put code like this online.

I once allowed anyone to add comments to my website without a captcha, and it resulted in 50000 (no really, I am not kidding) comments of advertisements about viagra.

Also you might consider cleaning your variables before executing them in an query (prevents SQL-injection):

$variable = htmlentities(addslashes($_POST['variable']));

Showing comments part

You now need to retrieve the rows from the table, run through the result with a loop, echo'ing each comment. If you don't know how, try reading a good book or searching online.

~G

[Graphix]

Oh and if you run into a problem with the "showing comments"-part: first try to figure out what the problem is yourself and if you still can't find the solution, post a reply and I'il take a look at it.

[champmanking]

My problem is that I don't know how to make each comment exclusive to that particular page.

[Graphix]

You have the variable $page_id in your HTML form, which specifies which page the comment should be placed.

You only need to retrieve the comments from the database that have $page_id as page_id.

I'il give you an head start:

SELECT * FROM table_name WHERE column='value'

Now you need to apply that SQL-query to your code, and loop through the result. If you don't know how:

You now need to retrieve the rows from the table, run through the result with a loop, echo'ing each comment. If you don't know how, try reading a good book or searching online.

~G

[champmanking]

Nothing is being added to the database and therefore shown. Any ideas?

form and page

$sql = mysql_query("SELECT * FROM comments WHERE page_id = $myid") or die(mysql_error());
while ($row = mysql_fetch_array($sql)) {
$comment = $row['comment'];
}

}else{
  echo 'No content for this ID';
}
}
?>
<html>
<body>
 <form name="comment" action="addcomment.php" method="post">
  <input type="hidden" id="myid" value="<?php echo $myid ?>" />
  <textarea id="comment"></textarea>
  <input type="submit" />
</form>
</body>
</html>

addcomment.php

<?php

if(isset($_POST['submit'])){


$myid = $_POST['myid'];
$comment = $_POST['comment'];

$mymysql = mysql_connect("","","") or die ("can't connect");

mysql_select_db("", $mymysql );

mysql_query("INSERT INTO comments 
(page_id, comment) VALUES($myid, '$comment' ) ") 
or die(mysql_error()); 

}

?>

[EvolutionFallen]

In "form and page", line 14 the hidden field shouldn't have a $ in the id attribute. So instead of id="$myid" , it should look like this:

<input type="hidden" id="myid" value="<?php echo $myid ?>" />

[EvolutionFallen]

Also, your page_id field in your db is of type INT, so you shouldn't have quotes around $myid on line 14 of addcomment.php.

[champmanking]

Does $myid = $_POST; stay the same then?

Edit: Thanks. I made the changes, however it still doesn't add to DB.

[EvolutionFallen]

Does $myid = $_POST; stay the same then?

Yes, that should stay the same.

Sorry, I missed another correction. I fixed it in my previous post, but I'll repost it here. This is the correct code for the hidden field:

// The id="myid" defines the name of the $_POST index (i.e., $_POST['myid'] ). 
// The value is what is assigned to the array location at $_POST['myid']. In this case, you are outputting the value of the variable $myid.
<input type="hidden" id="myid" value="<?php echo $myid ?>" />

Just a quick question: I know on line 1 of "form and page" you refer to $myid in your SQL query. Is this variable initialized earlier on that page?

[champmanking]

Thanks.

Yes it is; here's the top half of the form and page php file:

if (isset($_GET['id'])) {
$myid = $_GET['id'];
$mymysql = mysql_connect("","","") or die ("can't connect");

mysql_select_db("", $mymysql );
$sql = "SELECT * FROM content WHERE id = '$myid' LIMIT 1";
$row = mysql_query( $sql, $mymysql );
if (mysql_num_rows($row)>0) {
  $d = mysql_fetch_array($row);
  echo "<h1>Introduction:<br /></h1>";
  echo $d['content'];

Still not working. :(

[EvolutionFallen]

I see you're calling mysql_error(). Is this not returning any messages?

[champmanking]

None whatsoever.

[champmanking]

Update on my code:

addcomment.php

<?php

if (isset($_REQUEST['Submit'])) {

$myid = $_POST['myid'];
$comment = $_POST['comment'];

$mymysql = mysql_connect("host","user","pass") or die ("can't connect");

mysql_select_db("db", $mymysql );

$sql = "INSERT INTO comments (page_id, comment) VALUES ($myid, $comment)" or die(mysql_error());
mysql_query( $sql, $mymysql );

}

?>

form and show page

$sql = mysql_query("SELECT * FROM comments WHERE page_id = $myid") or die(mysql_error());
while ($row = mysql_fetch_array($sql)) {
$comment = $row['comment'];
}

}else{
  echo 'No content for this ID';
}
}
?>
<html>
<body>
 <form name="comment" action="addcomment.php" method="post">
  <input type="hidden" id="myid" value="<?php echo $myid; ?>" />
  <textarea id="comment"></textarea>
  <input type="submit" name="Submit" value="Submit" />
</form>
</body>
</html>

[EvolutionFallen]

addcomment.php -- Added quotes to SQL query:

<?php
if (isset($_REQUEST['Submit'])) {

   $myid = $_POST['myid'];
   $comment = $_POST['comment'];

   $mymysql = mysql_connect("host","user","pass") or die ("can't connect");

   mysql_select_db("db", $mymysql );

   $sql = "INSERT INTO comments (page_id, comment) VALUES ($myid, '$comment')" or die(mysql_error());
   mysql_query( $sql, $mymysql );
}

?>

By the way, did you change the name of the comment content field in the DB like i suggested? 'Cause if so you need to change it in the query above.

[champmanking]

My DB is:

comments 
(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
page_id INT NOT NULL, 
comment TEXT)

Just added the quotes but it still won't work.

[Graphix]

Alright... I haven't taken a look at the thread for a few hours now and see EvolutionFallen helping :) Well OK.

Some small remarks:

>> You should still read through my previous reply and clean the values you collect with POST, as this makes your SQL vunerable for attacks!

>> If you check the following bit of code EvolutionFallen provided:

$sql = "INSERT INTO comments (page_id, comment) VALUES ($myid, '$comment')" or die(mysql_error());
   mysql_query( $sql, $mymysql );

You should notice that you declare an string to an variable and if THAT doesn't work, you let mysql print its last error??? Pretty strange coding to me! Take a look at this:

$query = "THIS is MY query";
$result = mysql_query($query) or die("Could not execute query");

It prevents the visitor from seeing errors if the occur AND they tell you that the query is wrong. Ofcourse, if you are testing and want to know why a query fails, you put mysql_error() as parameter for die() . But remember to remove it when putting it on a live site ;).

Also, regarding your form and show page:

while ($row = mysql_fetch_array($sql)) {
$comment = $row['comment'];
}

Is a correct loop, but you are not echo'ing anything! Try this:

while ($row = mysql_fetch_array($sql)) {
  $comment = $row['comment'];
  echo $comment;
}

Also the returning reply "Still not working" doesn't really help clarify what's going wrong (although more experienced programmers can spot it easier).

You might want to take a look in a good PHP book (I started off with PHP 5 & MySQL for Dummies, and then went on to more advanced books) to learn more about looping and such. php.net is always a good reference for functions!

~G

[EvolutionFallen]

Oh wow, good catch with the mysql_error, Graphix. Can't believe I slid right over that.

[champmanking]

Thanks. I've made the changes and it now says it can't execute the query.

[EvolutionFallen]

A little more detail? It should say why, what caused the error?

[champmanking]

It just says could not execute query.

[champmanking]

I changed to die(mysql_error()) and it says "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' )' at line 1"

This is the code:

$query = "INSERT INTO comments (page_id, comment) VALUES ($myid, '$comment')";
$result = mysql_query($query) or die(mysql_error());

[EvolutionFallen]

Try echoing $query right after you assign it to see what the contents are. See if it looks correct.

[champmanking]

Interestingly, this comes out:

INSERT INTO comments (page_id, comment) VALUES (, '')You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '')' at line 1

Seems $myid and $comment are empty.

[champmanking]

Can anyone fix this? ;D

[champmanking]

Anyone know why $myid and $comment are empty?

[champmanking]

Solved it myself!! The problem was in the form.

<html>
<body>
 <form name="comment" action="addcomment.php" method="post">
  <input type="hidden" id="myid" value="<?php echo $myid; ?>" />
  <textarea id="comment"></textarea>
  <input type="submit" name="Submit" value="Submit" />
</form>
</body>
</html>

<textarea id="comment"></textarea>

should have been

<textarea name="comment"></textarea>

Nice overlooking ;)

Thanks for helping along the way, guys.