Display total amount

[Ashoar]

I am working on a piece of code that will get the amount of threads made within a certain board so that they can be displayed on the main page of the forum.

 

If i use this code without any of the board information, it works fine and will display the total amount of threads made through out the entire forum.

So i believe i am doing something wrong when trying to find out what board i need to get the posts from.

 

Here is the bit of code:

$boards = mysql_query("SELECT forum_name, forum_desc, board FROM forums") or die(mysql_error());
$threads=mysql_numrows(mysql_query("SELECT post FROM post_reply WHERE board='".$name[board]."' AND parentid='0'"));
$boards2 = mysql_num_rows($boards);
for($count = 1; $count <= $boards2; $count++)
{
$name = mysql_fetch_array($boards);

print "<tr class='mainrow'><td><A href='board.php?board=$name[board]'>$name[forum_name]</a><p>$name[forum_desc]</p></td><td>$threads</td><td>timeofposthere<br>lastpost here</a></td></tr>";
}

 

The Thread variable and mysql query is the one that gets the posts. Parentid is just to make sure it is a thread and not a reply.

 

The board variable and query is fetching all of the boards from the database along with their names, descriptions and id.

 

I need to try and get the amount of posts in each board. The boards links are made up by their id:

e.g: board.php?board=test

 

So can someone check to see if i am doing this right?

 

Thanks

[Zhadus]

Take this line:

<?php
$threads=mysql_numrows(mysql_query("SELECT post FROM post_reply WHERE board='".$name[board]."' AND parentid='0'"));
?>

And change it to this so you can evaluate it better:

<?php
$qString = "SELECT post FROM post_reply WHERE board='".$name[board]."' AND parentid='0'";
$tQuery = mysql_query($qString) or die();
$threads = mysql_num_rows($tQuery);
?>

 

Also... your $threads value uses mysql_numrows() instead of mysql_num_rows(). I believe it's just a typo since you said it works fine without throwing an error normally.

[Mark Baker]

Simplify your multiple queries into one:

$boards = mysql_query("SELECT f.forum_name, f.forum_desc, f.board, SUM(p,post) AS threads 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());

foreach($boards as $board) {
   print "<tr class='mainrow'><td><A href='board.php?board=$board['board']'>$board['forum_name']</a><p>$board['forum_desc']</p></td><td>$board['threads']</td><td>timeofposthere<br>lastpost here</a></td></tr>";
}

 

This will also simplify things when you want to add "timeofposthere" and "lastpost here"

[Ashoar]

Thank you for the replies.

I will try both of these out and see how it goes.

[Ashoar]

Update:

Neither of these helped to solve the problem.

 

Any other ways this could be done?

[Zhadus]

You said none of it solved the problem, but what was the information that it output to the screen for the information I gave you?

[Ashoar]

None, it just displayed the "0" that is normally there.

[Zhadus]

Echo out $qString and make sure it is carrying the correct variables.

i.e.

<?php
$qString = "SELECT post FROM post_reply WHERE board='".$name[board]."' AND parentid='0'";
echo "Query: $qString <br />";
$tQuery = mysql_query($qString) or die();
$threads = mysql_num_rows($tQuery);
?>

[Ashoar]

The output:

Query: SELECT post FROM post_reply WHERE board='' AND parentid='0'

 

That's not right

[Zhadus]

Exactly, and that is the problem, the board value isn't passing through.

Where are you getting $name[board] from?

[Ashoar]

I may not have given enough info.

 

I want to select the amount of posts that have been made in each board, just like on a a normal forum, the front mage that has Topics and Replies.

When making a post, all information is stored and there is a row on the table for the forumid where the id of the forum the post was made in is saved.

 

What i am trying to do is get the total of all the posts made in each board.

 

I need to try and find a way of matching the posts up with the id of the board on the main page.

I thought maybe "$name[board]" would get the name of the board that the script is on and get the posts for it.

 

Maybe there is another way of doing so?

The posts are saved to a row called forumid and the board id is a row called "Board"

[Mark Baker]

And the output you got for my query???

 

Assuming you noticed I'd forgotten to fetch the row?

$boardsQuery = mysql_query("SELECT f.forum_name, f.forum_desc, f.board, SUM(p,post) AS threads 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);
   print "<tr class='mainrow'><td><A href='board.php?board=$board['board']'>$board['forum_name']</a><p>$board['forum_desc']</p></td><td>$board['threads']</td><td>timeofposthere<br>lastpost here</a></td></tr>";
}

 

[Ashoar]

And the parse error:

"Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING"

 

I wouldn't know how to fix that, i have never merged queries like that before.

[Mark Baker]

Typo - Comma rather than dot in the query at SUM(p,post)

Missing { in the for statement

$boards = mysql_query("SELECT f.forum_name, f.forum_desc, f.board, SUM(p.post) AS threads 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);
   print "<tr class='mainrow'><td><A href='board.php?board=$board['board']'>$board['forum_name']</a><p>$board['forum_desc']</p></td><td>$board['threads']</td><td>timeofposthere<br>lastpost here</a></td></tr>";
}

[Ashoar]

Output:

 

<= $boardCount; $count++) { $board = mysql_fetch_array($boards); print "$board['forum_name']

$board['forum_desc']
$board['threads']timeofposthere
lastpost here"; } ?> 

[Mark Baker]

Output:

 

<= $boardCount; $count++) { $board = mysql_fetch_array($boards); print "$board['forum_name']

$board['forum_desc']
$board['threads']timeofposthere
lastpost here"; } ?> 

That looks more like a problem with opening and closing php tags

[Ashoar]

Just noticed the php file didn't update wholey and left out half of the code.

 

Once again:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

[Mark Baker]

Once again:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

Hopefully, the error should also tell you what line it's on.

 

But you could try using

   echo "<tr class='mainrow'><td><A href='board.php?board=".$board['board']."'>".$board['forum_name']."</a><p>".$board['forum_desc']."</p></td><td>".$board['threads']."</td><td>timeofposthere<br>lastpost here</a></td></tr>";

[Ashoar]

Output:

 

Test Board

This is a test board. All test posts will be made here to check and make sure everything works.
0
timeofposthere
lastpost here 

 

So it is still a matter of trying to get some sort of connection between the board id so that the posts for it can display.

[Mark Baker]

Change SUM(p.post) to COUNT(p.post) in the query

 

I'm having a few mental aberrations today.

[Ashoar]

Output:

 

Test Board

This is a test board. All test posts will be made here to check and make sure everything works.
1
timeofposthere
lastpost here 

 

Seems that may have worked, there is only 1 post in that board. I will add another to see if it updates.

 

EDIT:

That seems to have done the trick, it works perfectly now.

Thank you for the help.

I will take a look through the coding you provided and work through what i needed to change in my original coding.

[Mark Baker]

For "timeoflastpost" and "lastpost here", change the query to:

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, post_reply p WHERE p.board=f.board AND p.parentid='0' GROUP BY f.forum_name, f.forum_desc, f.board

using the appropriate column names instead of lastpostat as lastpostid

 

 

[Ashoar]

Thanks.

One more thing.

 

Using this code though has re-moved my board array, so now only the first board in the MYSQL database is appearing.

[Mark Baker]

Using this code though has re-moved my board array, so now only the first board in the MYSQL database is appearing.

Is the first board the only one with any threads?

I suspect this might be the case

 

I'm more used to working with Oracle's syntax for joins, using a shortcut syntax, than I am with MySQL

 

In Oracle, I'd use:

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, 
       post_reply p 
WHERE p.board(+)=f.board 
   AND p.parentid(+)='0' 
GROUP BY f.forum_name, 
          f.forum_desc, 
          f.board

I can never remember the equivalent in MySQL; but it's something like:

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

I'm sure some of the MySQL guys on the forum can correct any mistakes

[Ashoar]

Yes it is the only one with posts inside of it.

I am trying to fix that at the moment, but once again it is a bit troubeling because i have never used or seen a MYSQL query being used like such.

 

Also a question for you Mark.

Once again this is due to the query type.

Am i able to swap the parentid='0' around so that it will only effect the thread count?

It comes after the last post as well, so the last post will be the last thread made instead of the last reply. I suspect all i had to do was move the parent id in front of the thread part of the query, but that did not work.