SQL Doubling

[TimFA]

Odd problem. The below code is part of a .php file that will display a certain text depending upon: index.php?resource=1/2/3, etc. (the actual code is in a separate file, included("")) I deciding to add a commenting system to it, using DBs. It ALMOST works, but for some strange reason if I post one comment then try to post a different one it comes up identical to the previous. I.e. if I commented once to say "This thing sucks", then changed my mind the next time my comment (no mater what I type) always says "This thing sucks". Not as in I'm trying to edit the previous one, a completely new comment. On the page to display them theres just the orig and nothing else. But the DB has multiple identical entries.



<?php
$resource=$_GET['resource'];
$username=$_POST['username'];
$comment=$_POST['comment'];
include("../phpscripts/dbc.php");


$SQL=" SELECT * FROM comments ";
//$SQL = $SQL . " WHERE category = '$category' ";

$retid=mysql_db_query($db, $SQL, $cid);

if (!$retid) { echo( mysql_error()); }
else {
while($row = mysql_fetch_array($retid)) {
$username=$row['username'];
$comment=$row['comment'];
$comments="comments:
<br>
<br>
$username said: $comment";
}
}

if ($logged=="yes") {
if($_SERVER['REQUEST_METHOD']=="POST") {

$SQL=" INSERT INTO comments ";
$SQL=$SQL . " (username, resource, comment) VALUES ";
$SQL=$SQL . " ('$username', '$resource', '$comment') ";

$result=mysql_db_query($db,"$SQL",$cid);

if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n"); }
}
$commentbox="Please use the below box to comment.
<br>
<br>
<form name=\"comment\" action=\"index.php?resource=$resource\" method=\"POST\">
<input name=\"username\" type=\"hidden\" value=\"$user\">
<textarea name=\"comment\" class=\"descr_field\">
</textarea>
<br>
<br>
<div class=\"button\">
<a href=\"#\" onClick=\"document.comment.submit();\">
Comment
</a>
</div>
</form>";
}
else {
$commentbox="You must be logged in to comment, please login of create an account.";
}



I hope I explained it right.


Thanks,
Tim


EDIT: Also there is more to the script but it repeats in this all the way down, just changed text:



if ($resource=="1") {
$title="asdasd";
$path="asdasd";
$description="asdasdasdasd";

print("<div id=\"maintitlediv\">~whatever whatever $title
</div>
</div>
Details of $title.<br>
<br>
<img src=\"http://fassist.profusehost.net/images/public_images/images/$path.png\" alt=\"asd\">
<br>
<br>
<br>
$description
<br>
<br>
$comments
<br>
$commentbox");

die();
}

[alexjewell]

Ok, what I'm noticing is how often you use the variable $comment. I only skimmed your script, so I'm not sure if this is the issue, but it could cause issues if you reuse variable names like that...

Here's a simple example of how that can mess things up:



if($object = 'wall'){
if(isset($_POST['baseboard'])){
$color = $_POST['baseboard'];}
if(isset($_POST['wall'])){
$color = $_POST['wall'];}
echo '<p>The baseboard color is: '.$color;
echo '<p>The wall color is: '.$color;}


Notice how, probably accidentally, the variable $color was used to carry the color of both the baseboard and the wall...meaning, when you go to echo $color, it will only show the color of the wall and not the baseboard. Any second definition of a variable will override the last definition...

Take a close look at your variables and their repetition. Try to be as specific as possible. For example, maybe $comment=$_POST['comment']; should be $postedComment=$_POST['comment'];
It's little things like that which will make a code either totally confusing or totally understandable...and it makes problems, like this, much easier to fix, as things make more sense when they're specifically organized and/or named.

If you fix the variables and the problem persists, post the new code and I'll take a look at it.

[TimFA]

I think you caught it mate. Here look:



$comment=$_POST['comment'];
...
$comment=$row['comment'];


So I overrode the new comment with the old one. But, then, shouldn't it display multiple same comments on the page? Anyways, thanks, I'll check it out.

Tim

[alexjewell]

But, then, chouldn't it display multiple same comments on the page?

Hmmm, depends...what is the $resource variable, exactly?

[TimFA]

I was going to use it to separate out between items, since it automatically does index.php?resource=1 then so if when commented it says resource 1 the php knows where to look. That confused me when I re-read but I think you'll get the idea. At the moment it doesn't do anything, the commented out line is what will do it. Before I ever even had that column in the table it still did this though.



//$SQL = $SQL . " WHERE category = '$category' ";


Clearly its currently set for category not resource, but w.e.

[alexjewell]

Ok, well change the variables and see what that does and then post the new code.

[TimFA]

Thanks man, I ran into one more problem which after some brain storming I fixed. Heres what I changed to make more than one comment show up:

FROM:


while($row = mysql_fetch_array($retid)) {
$posted_username=$row["username"];
$posted_comment=$row["comment"];

$comments="Comments<br><br>$posted_username said: $posted_comment";
}


TO:


$comments="Comments:<br><br>";

while($row = mysql_fetch_array($retid)) {
$posted_username=$row["username"];
$posted_comment=$row["comment"];

$comments=$comments.="$posted_username said: $posted_comment";
}


And now everything works fine.

Tim

[boogyman]

$comments=$comments.="$posted_username said: $posted_comment";
}

change to



$comments = $comments. $posted_username ."said: ".$posted_comment;


Better solution
I am not sure why you want to continually put the "comments" there but you can do something like



while($row = mysql_fetch_array($retid)) {
$comments = "<div>";
$comments .= "\n\t<p>Comments:</p>";
$comments .= "\n\n\t<p>". $row["username"] ." said: ". $row["comment"]."</p>";
$comments .= "\n</div>";
}

and it would output something along the lines of



<div>
<p>Comments:</p>
<p>USERNAME said: COMMENT</p>
</div>
<div>
<p>Comments:</p>
<p>USERNAME said: COMMENT</p>
</div>
<div>
<p>Comments:</p>
<p>USERNAME said: COMMENT</p>
</div>
<div>
<p>Comments:</p>
<p>USERNAME said: COMMENT</p>
</div>

which would render on the screen like



Comments:
USERNAME said: COMMENT

Comments:
USERNAME said: COMMENT

Comments:
USERNAME said: COMMENT

depending on the margins / padding you assigned to the div and the paragraphs

[TimFA]

I'm confused? Why? What difference does mine make??? I realize at the moment it just puts them on top of eachother, but it works. It can call out multiple comments. I'll change it as needed for aesthetic purposes.

[boogyman]

I am going to assume you mean

$comments=$comments.="$posted_username said: $posted_comment";
if I am correct, after the first comment that should concatenating the comment every time to the $comments variable... so in theory it should look like



Comments:

USERNAME1 said: COMMENT1Comments:

USERNAME1 said: COMMENT1Comments:

USERNAME2 said: COMMENT2


for the second comment and


Comments:

USERNAME1 said: COMMENT1Comments:

USERNAME1 said: COMMENT1Comments:

USERNAME2 said: COMMENT2

USERNAME1 said: COMMENT1Comments:

USERNAME2 said: COMMENT2Comments:

USERNAME3 said: COMMENT3


for the third, etcetc...
I say this because 1 there are no line-breaks between the ending comment and the new one, and also you are re-assigning the comments variable by concatenating the existing variable to the end of the string, rather then just adding the "current" comment onto the end of the 1 comment variable you declared....

so in theory this should break, but in practice well, that is left up to the individual rendering engine. So unfortunately I believe you need to concatenate the strings together, which you are doing, just in an awkward way


$comments="Comments:<br><br>";

while($row = mysql_fetch_array($retid)) {
$posted_username=$row["username"];
$posted_comment=$row["comment"];

$comm = $comments."$posted_username said: $posted_comment";
}

that would solve issue of re-assigning the variable, however it doesn't solve the other issue of the comments not being on a new line... that can be solved by adding the break lines either before the Comments:<br><br> or after the $posted_comment

[TimFA]

I planned on:

Comments:<br>

<br>$posted_username

That way 2 linebreaks before the first comment then one inbetween each comment.

[boogyman]

that makes more sense, but in your script, you are including the Comments:<br><br> with the while loop.
If you only want 1 Comments (which is a good idea) just print out the comments before looping the array

[TimFA]

No, no. Sorry I explained it wrong man heres a copy of the actual script:



<?php
include("../phpscripts/dbc.php");

$resource=$_GET['resource'];
$posting_username=$_POST['username'];
$posting_comment=$_POST['comment'];
$comments="Comments:<br>";

$SQL=" SELECT * FROM comments ";
//$SQL = $SQL . " WHERE category = '$category' ";

$retid=mysql_db_query($db, $SQL, $cid);

while($row = mysql_fetch_array($retid)) {
$posted_username=$row["username"];
$posted_comment=$row["comment"];

$comments=$comments.="<br>$posted_username said: $posted_comment";
}

if ($logged=="yes") {
if($_SERVER['REQUEST_METHOD']=="POST") {

$SQL=" INSERT INTO comments ";
$SQL=$SQL . " (username, resource, comment) VALUES ";
$SQL=$SQL . " ('$posting_username', '$resource', '$posting_comment') ";

$result=mysql_db_query($db,"$SQL",$cid);

if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n"); }
}
$commentbox="Please use the below box to comment.
<br>
<br>
<form name=\"comment\" action=\"index.php?resource=$resource\" method=\"POST\">
<input name=\"username\" type=\"hidden\" value=\"$user\">
<textarea name=\"comment\" class=\"descr_field\">
</textarea>
<br>
<br>
<div class=\"button\">
<a href=\"#\" onClick=\"document.comment.submit();\">
Comment
</a>
</div>
</form>";
}

else {
$commentbox="You must be logged in to comment, please login of create an account.";
}


Thanks for all your help everyone.


Tim