[SOLVED] Joint query

[Ashoar]

I was just wondering if there is a way to join these 2 MYSQL query's into one.

 

$postcount0="SELECT * from Users where Username='$getreplies3[author]'";

$postcount02=mysql_query($postcount0) or die("Could not get postcount");

$postcount03=mysql_fetch_array($postcount02);

$getreplies="Select * from post_reply where parentid='$id' order by postid asc"; 

$getreplies2=mysql_query($getreplies) or die("Could not get replies");

while($getreplies3=mysql_fetch_array($getreplies2))

 

Thanks.

[AdRock]

If there is a common field in both tables you could do a join like if you had a username field in the post_reply table

[Ashoar]

No i don't, i just have the author field, which holds the usernames of replies to a post.

 

Basically what is happening here is that the postcount variables are grabbing the post count of the users who have replied and their signatures, so that i can output them in their post.

 

Getreplies variables are grabbing all of the replies for the thread, which is determined by the postid in the query.

 

But when i add these it does not show the post count and signatures.

If i move the post count query's under the get replies query's it doesn't show the replies.

 

I assumed it may be fixed my merging the query's together.

 

Here is the rest of the code in case the problem is something else:

$postcount0="SELECT * from Users where Username='$getreplies3[author]'";

$postcount02=mysql_query($postcount0) or die("Could not get postcount");

$postcount03=mysql_fetch_array($postcount02);

$getreplies="Select * from post_reply where parentid='$id' order by postid asc"; 

$getreplies2=mysql_query($getreplies) or die("Could not get replies");

while($getreplies3=mysql_fetch_array($getreplies2))

{

   print "<tr class='mainrow'><td valign='top'><p><A href='member_profile.php?username=$getreplies3[author]'>$getreplies3[author]</a></p><p>Posts: $postcount03[postcount]</p></td><td valign='top'>Last replied to at $getreplies3[showtime]<br><hr>";

   $message=strip_tags($getreplies3['reply']);

   $message=nl2br($message);

   print "$message<hr><br>$postcount03[signature]<br>";

   print "</td></tr>";

}

[AdRock]

I am currently doing something like you.

 

I have 4 tables: boards, topics, messages and users

 

In my board table, I have boardid and boardname

In topics table, I have topicid, topicname and boardid which joins to the board table

 

same with the messages table.  I have topicid which joins on the topics table so it's possible to join all 3 tables.

 

You can alos join the topics table and messages table to the users table on the userid/username.

 

Tiy could do somethihng like

 

"SELECT b.*, t.*. m.* FROM boards b INNER JOIN topics t ON b.boardid=t.boardid INNER JOIN messages m ON t.topicid = m.topicid"

[Ashoar]

Ah see i have no way of joining the 2 boards together like that.

So is there no other method i could join these or even just solve the problem i posted above:

 

"Basically what is happening here is that the postcount variables are grabbing the post count of the users who have replied and their signatures, so that i can output them in their post.

 

Getreplies variables are grabbing all of the replies for the thread, which is determined by the postid in the query.

 

But when i add these it does not show the post count and signatures.

If i move the post count query's under the get replies query's it doesn't show the replies.

 

I assumed it may be fixed my merging the query's together."

[AdRock]

Might be worth having a look here

 

http://www.w3schools.com/Sql/sql_join.asp

[Ashoar]

I took a look, but i don't think any applies to this situation, that i can see.

 

Just a note, i use the same method to display the thread posts and it works, just fine.

$gettopic="SELECT * from post_reply where postid='$id'";

$gettopic2=mysql_query($gettopic) or die("Could not get topic");

$gettopic3=mysql_fetch_array($gettopic2);

$postcount="SELECT * from Users where Username='$gettopic3[author]'";

$postcount2=mysql_query($postcount) or die("Could not get postcount");

$postcount3=mysql_fetch_array($postcount2);

print "<tr class='mainrow'><td valign='top'><p><A href='member_profile.php?username=$gettopic3[author]'>$gettopic3[author]</a></p><p>Posts: $postcount3[postcount]</p></td><td valign='top'>Last replied to at $gettopic3[showtime]<br><hr>";

$message=strip_tags($gettopic3['post']);

$message=nl2br($message);

print "$message<hr><br>$postcount3[signature]<br>";

 

It seems to be this part in the replies section that is causing the problem:

while($getreplies3=mysql_fetch_array($getreplies2))

The while statement, it is the only difference in from the above.

But if i change it to a normal array it doesn't work.

 

[AdRock]

If you could post your table structure and what you want it to do i'll have a look

[Mark Baker]

 
SELECT p.*, 
       u.author
  FROM post_reply R,
       users U
WHERE r.postid='$id'
   AND U.Username=R.author

[Ashoar]

Mark, that is almost what i am looking for, except when using that i get the mysql error "Could not get threads".

[Ashoar]

Problem is fixed.

Thanks for the help.