[D_tunisia]

I've got a little out of my depth with this one, so I wonder if anyone can help!

I've go the following code:

PHP Code:

<?php



include ("dbConfig.php");



$result = mysql_query("SELECT * FROM posts");



echo "<table border=0>\n";



while ($myrow = mysql_fetch_row($result)) {



        printf("<tr><td><font face=verdana size=2><b>%s</b><br><font face=verdana size=1>Posted by %s<br><br><font face=verdana size=2>%s<br><a href=\"comment.php?id=%s\">Comment on this post</a><br><br></td></tr>\n",

        $myrow[2], $myrow[1], $myrow[3], $myrow[0]);





}



echo "</table>\n";



?>

The code looks at my message post table and pulls out all of the results (http://69.73.155.48/index2.php).

All of the above works fine, and I have now created a second table called "comments" and created the input forms for the users. Again, this works fine up till this point.

The problem I am having is that I want to post all of the comments below the relevant posts on the main page. I just cannot get it to work. I've tried nesting another while loop into the code but I keep getting errors. When the user posts a comment it is stored in the comment table with the userID number and the message which it is relevent to.

If anyone can help I would be really greatful!

[mdhall]

Originally Posted by D_tunisia

I've got a little out of my depth with this one, so I wonder if anyone can help!

I've go the following code:

PHP Code:

<?php
 
include ("dbConfig.php");
 
$result = mysql_query("SELECT * FROM posts");
 
echo "<table border=0>\n";
 
while ($myrow = mysql_fetch_row($result)) {
 
        printf("<tr><td><font face=verdana size=2><b>%s</b><br><font face=verdana size=1>Posted by %s<br><br><font face=verdana size=2>%s<br><a href=\"comment.php?id=%s\">Comment on this post</a><br><br></td></tr>\n",
        $myrow[2], $myrow[1], $myrow[3], $myrow[0]);
 
 
}
 
echo "</table>\n";
 
?>

The code looks at my message post table and pulls out all of the results (http://69.73.155.48/index2.php).

All of the above works fine, and I have now created a second table called "comments" and created the input forms for the users. Again, this works fine up till this point.

The problem I am having is that I want to post all of the comments below the relevant posts on the main page. I just cannot get it to work. I've tried nesting another while loop into the code but I keep getting errors. When the user posts a comment it is stored in the comment table with the userID number and the message which it is relevent to.

If anyone can help I would be really greatful!

I've done something similair. When you pull your "posts" data, get the id.
In your "comments" table, have a field called "post_id", which holds the id of the post that it relates to. Then do a query of the comments table, matching the comments "post_id" to the posts' id, something like SELECT * FROM comments WHERE post_id='$id'". (Something along these lines).

Hope this helps a little.

[D_tunisia]

Thanks for the help, but I really need more advice. I've tried out adding a new while loop within the current one and it isn't working. I would be really greatful if someone can give me some more detail on this one.

The code I have is:

PHP Code:

<?php



include ("dbConfig.php");



$result = mysql_query("SELECT * FROM posts");



echo "<table border=0>\n";



while ($myrow = mysql_fetch_row($result)) {

        

        printf("<tr><td><font face=verdana size=2><b>%s</b><br><font face=verdana size=1>Posted by 



%s<br><br><font face=verdana size=2>%s<br><a href=\"comment.php?id=%s\">Comment on this 



post</a><br><br></td></tr>\n",

        $myrow[2], $myrow[1], $myrow[3], $myrow[0]);



}



echo "</table>\n";



?>

The comments table is called "comments" and the post ID field is "post_id". I'm also not sure how to "get" the post number from the posts table and use it to compare to the comments table.

[Infinite_Hackers]

try insted of mysql_fetch_row use mysql_fetch_array.. see if that works.

Also can you show me your table structure if this doesn't work.

PHP Code:

<?php



include ("dbConfig.php");



$result = mysql_query("SELECT * FROM posts");



echo "<table border=0>\n";



while ($myrow = mysql_fetch_array($result)) {

        

        printf("<tr><td><font face=verdana size=2><b>%s</b><br><font face=verdana size=1>Posted by



%s<br><br><font face=verdana size=2>%s<br><a href=\"comment.php?id=%s\">Comment on this



post</a><br><br></td></tr>\n",

        $myrow[2], $myrow[1], $myrow[3], $myrow[0]);



}



echo "</table>\n";



?>

[D_tunisia]

I'm still having a lot of problems with this, does anyone else have any good ideas?

Thanks.

[NeverMind]

use Joins to make it easy!
something like this query might help:

Code:

SELECT p.*, comments.* FROM posts AS p LEFT OUTER JOIN comments ON p.id=comments.post_id ORDER BY p.time;

this code will bring each post with its comments ! and will sort them by post time ..

[D_tunisia]

Thanks for the help. I have entered that code but I keep getting this error:


Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/ittechni/public_html/index2.php on line 81


Am I doing something silly? Here is the new cde I have:

PHP Code:

<?php



include ("dbConfig.php");



$result = mysql_query("SELECT p.*, comments.* FROM posts AS p LEFT OUTER JOIN comments ON p.id=comments.post_id ORDER 



BY p.time;");



echo "<table border=0>\n";



while ($myrow = mysql_fetch_row($result)) {



        printf("<tr><td><font face=verdana size=2><b>%s</b><br><font face=verdana size=1>Posted by 



%s<br><br><font face=verdana size=2>%s<br><a href=\"comment.php?id=%s\">Comment on this 



post</a><br><br></td></tr>\n",

        $myrow[2], $myrow[1], $myrow[3], $myrow[0]);





}



echo "</table>\n";



?>

[NeverMind]

change this line:

PHP Code:

$result = mysql_query("SELECT p.*, comments.* FROM posts AS p LEFT OUTER JOIN comments ON p.id=comments.post_id ORDER BY p.time;"); 

to:

PHP Code:

$result = mysql_query("SELECT p.*, comments.* FROM posts AS p LEFT OUTER JOIN comments ON p.id=comments.post_id ORDER BY p.time")or

die(mysql_error()); 

if there is any error, it should be printed to screen, tell us the error message if anything appear ..

[D_tunisia]

Getting there! I get this error now:

Unknown column 'p.time' in 'order clause'

[NeverMind]

you should've changed it silly !!
change it to the field name that you want your results to be sorted by or just remove it!

Code:

ORDER BY p.THE_FIELD_YOU_WANT_TO_SORT_RESULT_BY;

oh, that was long

I assumed that you had a field that contain date/time so just change it as I said !