Multiple entries storing in database.

[codeboy89]

I am having a problem with my coding below, this script saves a users comment for my website. The problem is, if a user saves a comment then saves another comment, and so on. There will be tons of that users comments saved in the database.

 

What needs to be done to remove their previous comment from the database when saving a new one or only allowing the latest comment to be stored in the database.

 

THE CODE:

------------------------------------

 

<?php

$character_limit = 500;

session_start();

 

if(isset($_SESSION['logged_id']) && isset($_SESSION['logged_user']) && isset($_SESSION['pass']))

{

$check = mysql_query("SELECT * FROM users WHERE user_id=".$_SESSION['logged_id']." AND username='".$_SESSION['logged_user']."' AND password='".$_SESSION['pass']."'");

 

if(mysql_num_rows($check)==0)

{

header("Location:index.php?msg=notlogged");

}

} else {

header("Location:index.php?msg=notlogged");

}

 

$has_error = 0;

$error = array();

 

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

{

 

if(isset($_POST['comment']) && !empty($_POST['comment']))

{

$comment = mysql_real_escape_string(strip_tags($_POST['comment']));

 

if(strlen($comment) > $character_limit)

{

$error['comment'] = "Only $character_limit chars. allowed";

}

} else {

$error['comment'] = "At least 1 char. is needed";

}

 

if(empty($error))

{

$insert = mysql_query("INSERT INTO comments(`id`,`user_id`,`comment`,`created_at`) VALUES(0,".$_SESSION['logged_id'].",'".$comment."','".time()."')");

 

$check_comment = mysql_query("SELECT * FROM comments WHERE user_id=".$_SESSION['logged_id']);

 

if(mysql_num_rows($check_comment) > 0)

{

$update = mysql_query("UPDATE comments SET comment='".$comment."',created_at='".time()."' WHERE user_id=".$_SESSION['logged_id']);

 

} else {

 

$insert = mysql_query("INSERT INTO comments(`id`,`user_id`,`comment`,`created_at`) VALUES(0,".$_SESSION['logged_id'].",'".$comment."','".time()."')");

}

} else {

$has_error = 1;

}

}

else if(isset($_POST['logout']))

{

header("Location:logout.php");

}

 

$get_comment_result = mysql_query("SELECT * FROM comments WHERE user_id=".$_SESSION['logged_id']." ORDER BY id DESC LIMIT 0,1");

 

if(mysql_num_rows($get_comment_result) > 0)

{

$get_comment_data = mysql_fetch_array($get_comment_result);

$saved_comment = stripslashes($get_comment_data['comment']);

} else {

$saved_comment = "";

}

?>

[jonsjava]

I cleaned up your query, and had it execute (setting a variable to a query does no good, if you don't call the variable).

<?php
$character_limit = 500;
session_start();

if(isset($_SESSION['logged_id']) && isset($_SESSION['logged_user']) && isset($_SESSION['pass']))
{
$check = mysql_query("SELECT * FROM users WHERE user_id=".$_SESSION['logged_id']." AND username='".$_SESSION['logged_user']."' AND password='".$_SESSION['pass']."'");

if(mysql_num_rows($check)==0)
{
	header("Location:index.php?msg=notlogged");
}
} else {
header("Location:index.php?msg=notlogged");
}

$has_error = 0;
$error = array();
if(isset($_POST['submit']))
{
if(isset($_POST['comment']) && !empty($_POST['comment']))
{
	$comment = mysql_real_escape_string(strip_tags($_POST['comment']));
	if(strlen($comment) > $character_limit)
	{
		$error['comment'] = "Only $character_limit chars. allowed";
	}
}
else {
	$error['comment'] = "At least 1 char. is needed";
}
if(empty($error))
{
	$insert = mysql_query("INSERT INTO comments(`id`,`user_id`,`comment`,`created_at`) VALUES(0,".$_SESSION['logged_id'].",'".$comment."','".time()."')");
	$check_comment = mysql_query("SELECT COUNT(*) as `total_found` FROM comments WHERE user_id=".$_SESSION['logged_id']);
	$row = mysql_fetch_assoc($check_comment);
	$total_found = $row['total_found'];
	if($total_found != 0)
	{
		mysql_query("UPDATE comments SET comment='".$comment."',created_at='".time()."' WHERE user_id=".$_SESSION['logged_id']);

	} else {

		mysql_query("INSERT INTO comments(`id`,`user_id`,`comment`,`created_at`) VALUES(0,".$_SESSION['logged_id'].",'".$comment."','".time()."')");
	}
} else {
	$has_error = 1;
}
}
else if(isset($_POST['logout']))
{
header("Location:logout.php");
}

$get_comment_result = mysql_query("SELECT * FROM comments WHERE user_id=".$_SESSION['logged_id']." ORDER BY id DESC LIMIT 0,1");

if(mysql_num_rows($get_comment_result) > 0)
{
$get_comment_data = mysql_fetch_array($get_comment_result);
$saved_comment = stripslashes($get_comment_data['comment']);
} else {
$saved_comment = "";
}
?>

Notice how I have it do a count on the database side? this makes the polling much easier. It will either return 0 or 1 (or more, if it's form before). If it returns more than one, ALL comments that that user posted will be changed over to the new comment.

[lmhart]

Maybe I am off in what you are wanting.  If a user can only have one comment then why not just search for user and if found update the comment.  If not the add user and post comment.

[codeboy89]

thanks jon, do i need to create a total_found entry for my table?

[codeboy89]

i cannot seem to make it work, i am still having multiple entries in the comments table from each user.

[codeboy89]

Maybe I am off in what you are wanting.  If a user can only have one comment then why not just search for user and if found update the comment.  If not the add user and post comment.

 

I am wanting to drop the old 'comments' entry per user when they save a new comment, or overwrite the existing entry with their new comment, currently the script is flooding the database with a entry for everytime the user saves.

[MadTechie]

I am wanting to drop the old 'comments' entry per user when they save a new comment, or overwrite the existing entry with their new commen

 

jonsjava code should do that fine,

 

however to save confusion remove this line

$insert = mysql_query("INSERT INTO comments(`id`,`user_id`,`comment`,`created_at`)  VALUES(0,".$_SESSION['logged_id'].",'".$comment."','".time()."')");

as its not used

 

Try clearing all comments from a user in your database and re-testing

(or create a new user)

[codeboy89]

can you tell me if i need to create "total_found" inside the comments table, and if so, should it just be:

 

total_found int(10) or what?

[MadTechie]

No Total_found is an alias of COUNT(*)

[codeboy89]

alright, it is working now, i removed the line you mentioned also. thanks!