SQL Query to Update Attach Column in Post & Thread Tables

[fordforumsonlin]

Hello All!

I was wondering if anyone with an understanding of SQL might be able to help me with a little problem I've got with my forum?

One of my users had 'accidentally' deleted all of her attachments on our forum, but didn't notify us until a week or so later. I've tried to partially update my database with my limited knowledge of MySql and have done so but now facing a small issue.

The attachments are showing in her profile, and when I log in as an admin and edit the post where the attachments are meant to be showing they are being displayed but they are not showing on the thread after I refresh the page. I contacted the vbulletin support who kindly told me what the issue was...

Hi Paul

There are two sets of data that need to be updated in your database for the attachments to show:

- attach column in the post table
- attach column in the thread table

These values should be the total of the number of attachments for that post/thread. It should be possible to write a query that calculates the number for each post and updates the values there then a second query to add the values from the updated post table to update the thread one but you would need to post in the forums for further help with this as this is outside the realms of ticket support I'm afraid.

Best regards,

I have attached a couple of screenshots to help further identify the problem.

Does anyone know how to write a query that could help solve my issue? Any help would be gratefully received,

Regards,
Paul

[Lynne]

Since this would reply on the contenttypeid for your Post, you will need to list what it is from your contenttype table. Normally, it would be 1, but sometimes users reinstall and get those numbers messed up.

Have you tried to write these queries yourself - at least taken a stab at it?

[fordforumsonlin]

Hello Lynne,

Thanks for getting back to me, I wouldn't know where to start in order to do this, I'm very new to MySQL and have only recently learned the difference between flushing a table and dropping a table!!

Do you think it would be complicated for someone to do it? I would be happy to pay someone if it is a lot of work, perhaps you know of someone?

Regards,
Paul

[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).

[fordforumsonlin]

Kh99 you are amazing!

I followed your steps exactly and you've completely fixed my website! Your code was perfect, but for those facing a similar issue, the below code should be changed:

SET thread.attach = temp_attach_count.count

SET thread.attach = temp_attach_count.attach

Thank you soo very much! also big thanks to Lynne.

Regards,
Paul

[kh99]

Originally Posted by fordforumsonlin

I followed your steps exactly and you've completely fixed my website! Your code was perfect, but for those facing a similar issue, the below code should be changed:

SET thread.attach = temp_attach_count.count

SET thread.attach = temp_attach_count.attach

Oops - yeah, I changed the name of that field but missed one. Thanks.