comment function

[serdas]

i have this code to do simple comment on the listings on my site

require_once($config['basepath'].'/include/listing.inc.php');
require_once($config['basepath'].'/include/misc.inc.php');
$misc = new misc();
$listingID = $_GET['listingID'];
$sql = "SELECT * FROM default_en_comments WHERE listingID = '$listingID'";

$recordSet = $conn->Execute($sql);
if ($recordSet === false) {
$misc->log_error($sql);
}
while (!$recordSet->EOF) {

$comments = $misc->make_db_unsafe ($recordSet->fields[comment]);
$name = $misc->make_db_unsafe ($recordSet->fields[name]);

$display .= '<li>' .$comments. ' By ' .$name. '</li>';
$recordSet->MoveNext();
}
return $display;
}

but the problem with this is, if someone posts comment on my listing i don't have an option to delete that comment if it is not proper coment, who ever owns that lisitng i want them to be able to delete unnecessary comments.

also if the comment is empty and some body submit an empty message it still posts. can that be stopped

or anywher i can get something like this if exist?
thank you

[Ole Raptor]

in your users table do you have roles set? if you do you could get the current users role. lets say they are an admin or mod. if they are either you can add a delete button. they push that and delete the comment from the db

[diafol]

You can stop empty posts quite easily:

if(trim($POST['comment'])) == ""{
//do nothing
}else{
//your usual add a comment code
}

If you want to allow deletes for your list creators, do as the Professor suggests. I assume your listings table has a creator's id field. Just ensure that when the creator's logged in (I assume you have a users table), delete buttons/links appear next to all the comments on HIS/HER listings (and not anybody else's)

e.g.

if($logged_user_id == $creator_id)echo "<a href=\"delete_comment.php?id={$comment_id}&amp;confirm={$confirm}\">delete post</a>";

The $confirm variable is there for security. This could be a salted md5 code, e.g.

md5(md5($user_id . $comment_id . "salt strings are cool"));

When the delete_comment.php page accepts the url, just check the $_GET['confirm'] against the $user_id (from SESSION vars or cookies??) and the $_GET['id'] via the same salted md5 expression.

[serdas]

yes i have a user and guest system, but i don't want one user to delete other user's listing comments though.
how would the whole code would look like?

[diafol]

e.g.

if($logged_user_id == $creator_id)echo "<a href=\"delete_comment.php?id={$comment_id}&amp;confirm={$confirm}\">delete post</a>";

Thought I covered this.

[serdas]

I don't think this code creates a comment id i think, you would know better, it asigns the comement for the page which is equal to listing id. this my table

CREATE TABLE IF NOT EXISTS `default_en_comments` (
  `comment` varchar(256) NOT NULL,
  `listingID` varchar(6) NOT NULL,
  `name` varchar(30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

is there a code or a tutorial where i can something like i need.

thank you

[diafol]

I don't think this code creates a comment id i think, you would know better, it asigns the comement for the page which is equal to listing id. this my table

CREATE TABLE IF NOT EXISTS `default_en_comments` (
  `comment` varchar(256) NOT NULL,
  `listingID` varchar(6) NOT NULL,
  `name` varchar(30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

is there a code or a tutorial where i can something like i need.

thank you

No it doesn't. You need a comment_id (primary key) placed at the beginning. I strongly suggest that most/all your tables have a primary key (usually an autoincrement integer) so that records can be indexed easily. It saves a lot of hassle. If you already have the table up and running. You can modify the table by adding this field. It should be automatically filled when you then view your records.

[serdas]

i am sorry i am a way beginner, so all need to do is add a field in the table, set at int and default 0, would that be all i need to do?

but how would i get the creater id, creater id should be equal to the listing's creater id right? also i need to create a file called delete_comment.php? how should that look?

than you

[serdas]

i get this error when i try to add the field

Error
SQL query: 

ALTER TABLE `default_en_comments` ADD `comment_id` INT NOT NULL AUTO_INCREMENT FIRST 

MySQL said:  

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

[nav33n]

i get this error when i try to add the field

Error
SQL query: 

ALTER TABLE `default_en_comments` ADD `comment_id` INT NOT NULL AUTO_INCREMENT FIRST 

MySQL said:  

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

You should make this column comment_id as primary key. I guess, you have a user table(which should also have a column 'user_id' and it should be a primary key). When saving the comment from a particular user, say with user_id=1, insert a record in the comments table for this user (There should also be a column user_id (foreign key) in comments table so that you know who posted the comment).
Here is an example of the table structure I am talking about.
This is the users table.

Table USERS
 -user_id (auto increment, primary key)
 -user_name (varchar)
 -user_password(varchar)
 .....other user details

This would be the comments table

Table comments
 -comment_id (auto increment, primary key)
 -user_id (which references user_id of user table)
 -comment (varchar/text)
.... and all the other fields which you think are required.

When the user posts a comment, you need to insert a record in the comment table where user_id is the user_id of the logged user. This way, you can also have an option to edit the posted comment/or delete it.

I hope this will clear some doubts!

Cheers,
Nav

[diafol]

SOunds as though you've already got an autoincrement field. Pehaps making it unsigned as well may help? I'm no expert I'm afraid.

OK here goes:

In your main listings page:

{listings}

Comments table:

(get the listing_id and the user_id (creator) from a previous sql call (for displaying listings)
So you should have $creator_id and $listing_id .
In addition, a logged user should have a $user (or similar) floating around somewhere.
If the $creator_id is the same as the $user , then give the user the links to delete any comments.

$rs = mysql_query("SELECT * FROM defaults_en_comments WHERE listing_id = '{$listing_id}'" ORDER BY comment_id); //or 'ORDER BY comment_id DESC' for reverse order

while($data = mysql_fetch_array($rs)){
  if($creator_id == $user){
    $deletestring = "<a href=\"delete_comment.php?id={$data['comment_id']}&amp;confirm=" . md5(md5('$data['comment_id']' . $user . 'saltiness')) ."\">Delete comment</a>";
  }else{
     $deletestring = "&nbsp;";
  }

  echo "<tr><td colspan=\"2\">" .  stripslashes($data['comment']) . "</td><tr>";
  echo "<tr><td>{$data['name']}</td><td>{$deletestring}</td></tr>";

}

When the creator then clicks the Delete link, (s)he will be taken to the delete_comment.php page where the $_GET variables are processed and validated.

if(isset($_GET['id']) && isset($_GET['confirm'] && $_GET['confirm'] == md5(md5('$_GET['id']' . $user . 'saltiness'))){

$rs = @mysql_query("DELETE FROM default_en_comments WHERE comment_id = '$_GET['id']");
}

header("Location:" . $_SERVER['HTTP_REFERER']);

Obviously, you should check that the thing is deleted ( mysql_affected_rows ) and maybe a msg could be propagated to the referring page. Ensure however that no output occurs before the header() function or you'll get an error.

[serdas]

First of All Thank you very much taking your time to help, i can only have this part done where i can equal the logged user with the listing creater
I am very sorry but i am all confused with the rest, i am not experience on this at all,
someone provided the code for me but did not have time to help any further. all i got was this table

CREATE TABLE IF NOT EXISTS `default_en_comments` (
  `comment` varchar(256) NOT NULL,
  `listingID` varchar(6) NOT NULL,
  `name` varchar(30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and the code

require_once($config['basepath'].'/include/listing.inc.php');
require_once($config['basepath'].'/include/misc.inc.php');
$misc = new misc();
$listingID = $_GET['listingID'];
$sql = "SELECT * FROM default_en_comments WHERE listingID = '$listingID'";

$recordSet = $conn->Execute($sql);
if ($recordSet === false) {
$misc->log_error($sql);
}
while (!$recordSet->EOF) {

$comments = $misc->make_db_unsafe ($recordSet->fields[comment]);
$name = $misc->make_db_unsafe ($recordSet->fields[name]);

$display .= '<li>' .$comments. ' By ' .$name. '</li>';
$recordSet->MoveNext();
}
return $display;
}
?>

and some form to put on the page.

i cannot really touch my user table which is not in this table
would it be possibe to tell me step by step what i should do and how, i know no one got the time to do this but hoping someone will, also can i add the delete function in this code instead of having a seperate file?

thank you

[diafol]

If you don't want to create a new page, you could send the delete url to itself with the $_GET code at the top. It's not usually advisable, but it should work. At least you won't need the header() function then.

I'm sorry to say, if you don't understand the previous posts, you'll need to brush up your php/mysql skills with some basic tutorials/good quality books.

I know you're just looking for a quick fix, but working with other people's code and fixes can get really messy, really quickly unless you actually understand what's going on.

As for your tables: leave the users table alone as long as it has a user_id field - you don't need to mess with it. It's your comments table that's poorly thought out/implemented.

Let me illustarte the ideal (IMO) schema:

[B]USERS[/B]
users_id (primary key, autoincrement integer not null) 
firstname
surname
...

[B]LISTINGS[/B]
listings_id (primary key, autoincrement integer not null) 
listing_name
user_id(integer not null) *THIS WILL BE TAKEN FROM THE USERS TABLE
...

[B]COMMENTS[/B]
comment_id(primary key, autoincrement integer not null)
comment_user_id (integer not null) *THIS WILL BE TAKEN FROM THE USERS TABLE (optional field) 
listing_id (integer not null) *THIS WILL BE TAKEN FROM THE LISTINGS TABLE
comment (text)
posted (datetime)
...

(... denotes other fields or end)

[serdas]

if($logged_user_id == $creator_id)echo "<a href=\"delete_comment.php?id={$comment_id}&amp;confirm={$confirm}\">delete post</a>";

where would i put this in the code and how would i get back to page i was?

thank you

[serdas]

i am sorry, i miss posted the first one

[serdas]

If you don't want to create a new page, you could send the delete url to itself with the $_GET code at the top. It's not usually advisable, but it should work. At least you won't need the header() function then. could you please show me how would it look as a whole code, i am going crazy i cannot understand it, it's been days, but could not do it.

thank you