Displaying database data problem

[davidjones1990]

Hey, I have written a script for a very simple PHP wall and comment system. This works fine but the problem I have is displaying the comments. It seems to display the comments associated with the post as well as the comments on the posts above it. I have checked the database and the  post ID's are correct.

 

Here is my code:

 

<?php
$wallDisplay = '';
$commentDisplay = '';

$wallDisplaySql = mysql_query("SELECT * FROM wall WHERE to_id='$id' ORDER BY datetime DESC") or die (mysql_error());

while($row = mysql_fetch_array($wallDisplaySql)){
$wallPostId = $row["id"];
$to_id = $row["to_id"];
$from_id = $row["from_id"];
$message = $row["message"];
$dateTime = $row["datetime"];


$getFromData = mysql_query("SELECT username FROM members WHERE id='$from_id'") or die (mysql_error());

while($row2 = mysql_fetch_array($getFromData)){
	$wallUsername = $row2['username'];
}

$displayComments = mysql_query("SELECT * FROM wallComments WHERE wallPostId='$wallPostId' ORDER BY datetime DESC");


while($row3 = mysql_fetch_array($displayComments)){
$wallComment = $row3['comment'];
$commentFrom = $row3['from_id'];
$commentDate = $row3['datetime'];


$getUsername = mysql_query("SELECT username FROM members WHERE id='$commentFrom'");

while($row4 = mysql_fetch_array($getUsername)){
	$commentUsername = $row4['username'];
}

$cheersCheck_pic = "members/$commentFrom/pic1.jpg";
$cheersDefault_pic = "members/0/defaultMemberPic.jpg";
if (file_exists($cheersCheck_pic)) {
    $cheers_pic = "<img src=\"$cheersCheck_pic?$cacheBuster\" width=\"40px\" />"; 
} else {
$cheers_pic = "<img src=\"$cheersDefault_pic\" width=\"40px\" />"; 
}

$commentDisplay .= '<table width="500px" align="right" cellpadding="4" bgcolor="#FFF">
        <tr>
          <td width="10%" bgcolor="#FFFFFF"><a href="member_profile.php?id=' . $commentFrom . '">' . $cheers_pic . '</a><br />
          </td>
          <td width="90%" bgcolor="#DBE4FD"><a href="member_profile.php?id=' . $commentFrom . '"><span class="blackText">' . $commentUsername . '</span></a> • <span class="blackTetx">' . $commentDate . '<br /><font size="1"></font></span><br />
          <span class="blackText">' . $wallComment . '</span></td>
        </tr>
      </table>';
}

$cheersCheck_pic = "members/$from_id/pic1.jpg";
$cheersDefault_pic = "members/0/defaultMemberPic.jpg";
if (file_exists($cheersCheck_pic)) {
    $cheers_pic = "<img src=\"$cheersCheck_pic?$cacheBuster\" width=\"40px\" />"; 
} else {
$cheers_pic = "<img src=\"$cheersDefault_pic\" width=\"40px\" />"; 
}

$wallDisplay .= '<table width="100%" align="center" cellpadding="4" bgcolor="#FFF">
        <tr>
          <td width="7%" bgcolor="#FFFFFF"><a href="member_profile.php?id=' . $from_id . '">' . $cheers_pic . '</a><br />
          </td>
          <td width="93%" bgcolor="#DBE4FD"><a href="member_profile.php?id=' . $from_id . '"><span class="blackText">' . $wallUsername . '</span></a> • <span class="blackTetx">' . $dateTime . '<br /><font size="1"></font></span><br />
          <span class="blackText">' . $message . '</span></td>
        </tr>
      </table>
  <div id="commentList">' . $commentDisplay . '</div>
  <div id="comment" align="right">
	<form id="comment" name="comment" method="post" action="member_profile.php?id=' .$id. '">
	<textarea name="comment" id="comment" rows="1" cols="35"></textarea>
		<input type="hidden" name="wallPostId" id="wallPostId" value="'. $wallPostId .'" />
		<input type="hidden" name="commentFrom" id="commentFrom" value="'. $_SESSION['id'] .'" />
	<input type="submit" name="submitComment" id="submitComment" />
	</form>
	</div><br />
	';
}
?>

 

I have been looking at it for ages but can think why this is happening.

 

Thanks in advance for any help

[Psycho]

I see lots of problems, but not sure which one, if any are the root of your problem:

 

NEVER run queries in loops. Learn how to do JOINS.

 

Why is there a while loop here:

	$getFromData = mysql_query("SELECT username FROM members WHERE id='$from_id'") or die (mysql_error());

while($row2 = mysql_fetch_array($getFromData)){
	$wallUsername = $row2['username'];
}

 

Anyway, I tried following your code and didn't want to take the time to try and really understand it. But, I did look at the queries and think I have a solution for those. I think this is the only query you need to run. It should have ALL the data for the wall post, the username of the wall post, the post comments, and the username for the comments. Give it a try and see if the results are correct for what you need

$query = "SELECT wall.id, wall.message, wall.datetime as post_date,
                 post_member.username as post_username,
                 wallComments.comment, wallComments.datetime as comment_date,
                 comment_member.username as comment_username
          FROM wall
          JOIN members AS post_member
            ON wall.from_id = post_member.id
          JOIN wallComments
            ON wallComments.wallPostId = wall.id
          JOIN members AS comment_member
            ON wallComments.from_id = comment_member.id
          WHERE to_id='$id'
          ORDER BY wall.datetime DESC, wallComments.datetime DESC"

[davidjones1990]

Hi thanks for the feedback, I know my code is shockingly bad but im a beginner just trying some things out at the moment. If the time comes to put anything online i'll definitely review my code and make changes for best practise.

 

Anyway the problem was I had the comment display too far down the page

 

Thanks for the tip about joining that will come in handy on several of my pages.