Pagination error in phpbb - desperate for help!

[Somerset Bob]

I've posted my query at the phpbb forum, where I got half an answer and then
no more. I posted it again at another phpbb forum, where I got half an
answer and no more. I posted it in alt.php, where I got half an answer, then
no more.

In desperation, I'm now posting again in alt.php, alt.comp.lang.php and
comp.lang.php in the hope that someone will find the time to help.

This was my original query:

*****
I've got 5 Categories, 20 forums in all.

Three of them show "Page 1 of 0" at the bottom and "GotoPage" at top and
bottom. The forums are all very lightly populated with posts - two have only
one post each, one has four.

I've tried re-synching them, but no good.

They were all forums where some previous posts were created and then deleted
when testing the board before going live - could this be part of the
problem? Will it correct itself when a certain number of posts have been
made into them? Is there anything I can do now to correct it?

*****

The first (and only) reply I got was:

***

if you have deleted the posts manually using sql query, then you haev to
update the phpbb_forums table too, where it keeps track of the number of
posts and topics, try changing that and see

***

To which I replied:

*****

The posts were deleted using the Moderator Control Panel. Don't know if
that's what you mean by deleting them manually using sql query.

I've just installed phpMyAdmin, and can now see a host of features new to
me. Rather confusing. I've only been using php for a month. Can you suggest
what I should do to update the phpbb_forums table? I assume it's something I
can do in phpMyAdmin.

*****

To which there was no further reply. At the other forum, where I posted the
same original question, I was asked what software versions I was using. I
provided the answers. No further reply.

I'm using:

phpbb 2.0.6
phpMyAdmin 2.5.4

My ISP is running:

php 4.3.2
MySQL 4.0.15a

The first reply I got in alt.php was:

"And have you tried updating the phpbb_forums table then?"

To which I replied:

"Well, that's what I'm asking - how to do this. So the answer's no. Because
I don't know how to. How would you do it?"

The reply was:

***** *****

I don't have to do it, cause i wrote my own forum ;)

But anyway, i'm bored so i download the phpBB2 source.

First i looked under the db directory but that seems to contain database
astraction layer implementations.

Then i thought, hey, the man is having problems with the forum view, so i'll
have a look viewforum.php The first thing i read in that code: $sql =
"SELECT * FROM " . FORUMS_TABLE . " WHERE forum_id = $forum_id"; Bingo! this
must be the right place.

A little further i notice (line 246): $sql = "SELECT COUNT(t.topic_id) AS
forum_topics FROM " ...
I wonder: Do they know about JOIN? Hell, it's their code.

I guess includes/funtions_post.php is a good start, cause here you can read
what happens when someone posts. Upto you to find out which tables/rows
haven't been updated.

Next i really would like to know about their databasedesign, and i end up in
install/mysql_schema.sql.

Have a look at the phpbb_forums table, cause my guess is that there you need
to change some values.

Darn they alreay said that at phpbb, so i'll be more precise: Have a look at
following columns of the table because here is where your problem is:

forum_status tinyint(4) DEFAULT '0' NOT NULL,
forum_order mediumint(8) UNSIGNED DEFAULT '1' NOT NULL,
forum_posts mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
forum_topics mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,

So what you need to do:
(Make a backup of your database, so you don't feel like killing me in case
something goes wrong)

Foreach forum in phpbb_forums:
-) Get the number of posts that belong to a forum from phpbb_posts
select count(*) as count from phpbb_posts where forum_id=$forum_id
update phpbb_forums set forum_posts=$count where forum_id=$forum_id
-) Get the number of topics that belong to a forum from phpbb_topics
select count(*) as count from phpbb_topics where forum_id=$forum_id
update phpbb_forums set forum_posts=$count where forum_id=$forum_id

Done.

***** *****

To which I replied:

***** *****

Thanks for taking the time to reply.

Unfortunately, I'm still not clear on *where* I'm supposed to do what you
advise me to do, or how to go about it.

When I log into to my database using phpMyAdmin, I can see "phpbb_forums"
listed amongst the files in the left-hand frame. When I click it, in the
main frame I see "Database [mydatabasename] - Table phpbb_forums running on
[servername]". The selected tab at the top is "Structure". There's a list of
21 fields, including the four you mentioned -

forum_status tinyint(4) DEFAULT '0' NOT NULL,
forum_order mediumint(8) UNSIGNED DEFAULT '1' NOT NULL,
forum_posts mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
forum_topics mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,

Down the bottom of the frame I see a text input box titled "Run SQL
query/queries on database [mydatabasename]". In the input box it says:

"SELECT * FROM `phpbb_forums` WHERE 1"

and this looks similar to the syntax you've used - so is this where I type
in the instructions you've provided? That is to say: do I type in -

select count(*) as count from phpbb_posts where forum_id=$forum_id

- and click "Go"?

Do I then type in the next line of your advice -

update phpbb_forums set forum_posts=$count where forum_id=$forum_id

- and click "Go"?

Do I follow this with -

select count(*) as count from phpbb_topics where forum_id=$forum_id

- and click "Go", followed by -

update phpbb_forums set forum_posts=$count where forum_id=$forum_id

- and click "Go"?

Sorry to be so dense, but php and SQL is very new to me.

***** *****

No further reply has been forthcoming.

Please - any help in clarifying what I must do and how I should do it will
be much appreciated.

Thanks.

BK