Multiple Queries and Results

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:

<?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!

YappyDog

ccgorman

It is likely that the error you had was because you did not have two result sets. Give this a try, you may need to edit the second query and printf:

<?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]);

$result1 = mysql_query("SELECT * FROM comments WHERE posts_id=".$myrow[0]);

while ($myrow = mysql_fetch_row($result1)) { 
	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";

ultraslacker

Use JOINs rather than a query in a while loop.

So...

$result = mysql_query("SELECT post.id, post_subject, comment.id, comment.blah
FROM post LEFT OUTER JOIN comment 
ON post.id = comment.post_id
ORDER BY post.id");

D_tunisia

Thanks for the help, 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

This is the code I have; am I doing something silly?

<?php

include ("dbConfig.php");

$result = mysql_query("SELECT post.id, title, comment.id, comment.post_id FROM post LEFT OUTER JOIN comment ON 

post.id = comment.post_id ORDER BY post.id");

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";

?>

ccgorman

The problem is the SQL syntax, but I couldn't give an exact answer without the table format, but here is a go:

title is missing table

SELECT post.id, post.title, comment.id, comment.post_id FROM post LEFT OUTER JOIN comment ON post.id = comment.post_id ORDER BY post.id

or why not try:

SELECT post., comment. FROM post, comment WHERE post.id = comment.post_id ORDER BY post.id

and use

while($myrow=mysql_fetch_array($result)){
// and refer to variables by name $myrow["id"], $myrow["post_id"]...
}

ultraslacker

Try the sql query out in a mysql client (from mysqladmin, if nothing else).

Originally posted by ccgorman
SELECT post., comment. FROM post, comment WHERE post.id = comment.post_id ORDER BY post.id

An outer join is probably needed - unless the query should return only the posts that have comments.