[SOLVED] Query with array: problem

[Ashoar]

Last night i had assistance with merging 2 database query's into one so that i could get specific post information about specific boards.

Before doing so, the original query that fetched all of the boards worked as an array so that it would show all boards, the other query grabs post information for the board.

Since merging the 2  query's the code will only show the boards on the main page that has posts in it, instead of all of them.

 

Here is the piece of code:

<?php
include 'config.php';

print "<link rel='stylesheet' href='style.css' type='text/css'>";
print "<table class='maintable'>";
print "<tr class='headline'><td width=5%>Status</td><td width=65%>Board</td><td width=5%>Posts</td><td width=25%>Last post</td></tr>";

$boards = mysql_query("SELECT f.forum_name, f.forum_desc, f.board, COUNT(p.post) AS threads, MAX(p.showtime) AS showtime, MAX(p.lastposter) AS lastposter FROM forums f, post_reply p WHERE p.board=f.board AND p.parentid='0' GROUP BY f.forum_name, f.forum_desc, f.board")  
or die(mysql_error());

$boardCount = mysql_num_rows($boards);
for($count = 1; $count <= $boardCount; $count++) {
   $board = mysql_fetch_array($boards);

echo "<tr class='mainrow'><td><center>On</center><td><A href='board.php?board=".$board['board']."'>".$board['forum_name']."</a><p>".$board['forum_desc']."</p></td><td><center>".$board['threads']."</center></td><td>On ".$board['showtime']."<br><br>By <A href='member_profile.php?username=$board[lastposter]'>$board[lastposter]</a></a></td></tr>";
}
print "</table>";
?>

 

That is the code.

SO the array there is only showing boards on the main page that have posts in it.

Anyone see a way to fix it?

Apparently it may be the structure of the query.

 

Thanks.

[Maq]

It's hard to tell without knowing the design of how your forums are setup, but I believe this clause:

 

AND p.parentid='0'

 

would only grab posts from the "main board".

 

You may want to change it to something similar to:

 

AND p.parentid IN(0, 1, 2 ,3)

 

Again, I would need to know your table structure and how you differentiate the "main board" from children.

 

Hope this helps.

[Ashoar]

The parent id doesn't effect the boards, that is just to know whether a post is a reply or new thread.

The reason that is in there is so that i can get the number of threads for that board.

 

As i said it should have something to do with the order of the query, but i am not to sure as this query is based of Oracle, which Mark from here helped with.

He said that the order may need to change to this:

SELECT f.forum_name,
       f.forum_desc,
       f.board,
       SUM(p.post) AS threads,
       MAX(p.lastpostat) AS lastpostat,
       MAX(p.lastpostid) AS lastpostid
  FROM forums f INNER JOIN post_reply p ON p.board=f.board  AND p.parentid='0'
GROUP BY f.forum_name,
          f.forum_desc,
          f.board

 

But that did not help either.

[Mark Baker]

Maq

 

I suggested the query using an INNER JOIN to retrieve values from both tables, but my database of choice is Oracle where I use the Oracle-specific shortcut syntax for Joins; and I can never remember the standard SQL syntax. The query still needs to return all forum records, even when there are no post_reply records for that forum record

[Ashoar]

Thank you for clearing that up Mark.

 

I have been playing around with it for a while but can't manage to get it in an order that will work.

If there is anyone that knows what order this does need to be in based on Mark's post above i would like to hear

[Mark Baker]

SELECT f.forum_name,
       f.forum_desc,
       f.board,
       SUM(p.post) AS threads,
       MAX(p.lastpostat) AS lastpostat,
       MAX(p.lastpostid) AS lastpostid
  FROM forums AS f 
       LEFT JOIN post_reply AS p ON p.board=f.board AND p.parentid='0'
GROUP BY f.forum_name,
          f.forum_desc,
          f.board

[Ashoar]

That did the trick.

Thank you again Mark.

[Maq]

Maq

 

I suggested the query using an INNER JOIN to retrieve values from both tables, but my database of choice is Oracle where I use the Oracle-specific shortcut syntax for Joins; and I can never remember the standard SQL syntax. The query still needs to return all forum records, even when there are no post_reply records for that forum record

 

I see, I'm assuming you are talking about suggesting this to him the other night.  His query in his original post did not have a JOIN but he did match against common fields so there are no duplicates.  Which can have the same effect.

 

In any event, glad it's working.