[kh99]

I think Lynne's thinking of vb4 when she mentioned contenttype - in vb3 there is no contenttype in the attachment table, there's just a postid.

I'm not a mysql expert or anything, but I think I came up with a way to do it using a temp table. There may be some way to do it in one query without a temp table, but I don't know. Anyway, here's what I have (in a series of queries):

CREATE TABLE temp_attach_count (
		id INT UNSIGNED NOT NULL DEFAULT '0',
		attach INT UNSIGNED NOT NULL DEFAULT '0'
	)

INSERT INTO temp_attach_count
SELECT postid as id, count(*) as count FROM attachment
WHERE postid > 0
GROUP BY postid

UPDATE temp_attach_count
LEFT JOIN post ON (post.postid = temp_attach_count.id)
SET post.attach = temp_attach_count.count

at this point the post counts should be fixed. If there was a problem, you can always do a "DROP TABLE temp_attach_count" and start again from the beginning.

TRUNCATE temp_attach_count

The TRUNCATE above is important - at this point you might want to check to make sure the temp_attach_count table has no rows.

INSERT INTO temp_attach_count
SELECT threadid as id, SUM(attach) as count FROM post
WHERE visible = 1
GROUP BY threadid

UPDATE temp_attach_count
LEFT JOIN thread ON (thread.threadid = temp_attach_count.id)
SET thread.attach = temp_attach_count.count

DROP TABLE temp_attach_count

This fixes the count for every post and thread that has an attachment in the attachment table. I thought about limiting it to attachments where the userid matched the user in question, but then I wasn't sure if it's possible for a post to include another member's attachment or not.

Edit: and I forgot to mention, if you have a table prefix set in your config.php then this code will need to be modified to add that (and if you don't understand what that is, don't worry because if you do have a prefix the second query will cause an error, no harm done).