Solved SQL query to populate user_topics table?

Anybody got a SQL query for this? Or is there a built-in function I haven't come across yet?

Well, an SQL query to populate a table is easy... but not without knowing what it is you want to populate it with. Why would you want to inject anything into this table through a query, rather than through the Kunena interface?

Kunena has API in directory /libraries/kunena so you don't know how to make the query but you need to know how to use the API

I have loaded messages and topics into tables from another forum and needed to reconstruct the contents of this table, which I have now done.

This blog post gave a useful SQL query for another necessary update, similar to the one I asked about (to save time and as my SQL knowhow is very rusty and I'm not that interested in getting to seriously to grips with it again for a one-shot task).

> "An SQL query is easy"

I wouldn't have asked if it was quite as easy that!

Sorry. My "easy" comment was not meant to be snarky. I just meant that (for some of us) writing an SQL insert query is easy, but even the best of us can't write a query without knowing what is being inserted.

If you had provided the information that was in your follow-up post into your original post, you probably would have had a more complete answer, much quicker.

Glad you got it solved.

Sorry I wasn't clearer; I thought populate the table given a set of attachments and message nrs was enough. No worries.

I used Excel to create the records for SQL to insert from hashes and filesizes etc. Worked fine apart from one thing I've yet to resolve.

Somewhere I need to update counters for nrs of topics (and attachments?). I get a SQL insert error when I try to add a new topic--now that I've got all my old data (users, topics, messages, incl. text and attachments) loaded.

harmlessdrudge wrote: Somewhere I need to update counters for nrs of topics (and attachments?).

You should not need to write something "custom". Use the Recount Statistics feature in the backend (I think there's a Wiki article about this).

harmlessdrudge wrote: I get a SQL insert error when I try to add a new topic--now that I've got all my old data (users, topics, messages, incl. text and attachments) loaded

Different problem, I think, and deserves a separate topic?

Speaking of separate topics, you combined two issues in another topic you posted here: Loading attachments problem and question . You correctly resolved the essential problem (uploading attachments with file types that are not on the allowed "list") but the other matter seems to be related to this topic. Have you also figured out the answer to the other matter and can your other topic be closed?

You should not need to write something "custom".

I would hope not! A SQL query is ok, if not too complicated. Actually, I tried that already (recounting the statistics).

The error message is 0 SQL=INSERT INTO `jos_kunena_topics` (`category_id`,`subject`,`hold`) VALUES ('2','Test new topic','4')

I've no idea what the 'hold' is about.

Yes, it's good point about the topics. My original problem was getting attachments into Kunena without entering them manually, and this I have now done. I was about to about post that I then got an error on adding an attachment (despite recounting statistics), and I found that actually the error was topic related.

I suppose it's involved somehow but I don't know how: I discovered that my import from Ning had missed the very first topic, so I incremented all the topic numbers (and thread nrs) and made the corresponding adjustments, including recreating indices, throughout, so that I have a new topic 1. It all looks fine, every thread, every topic, every attachment, and the totals in jos_kunena_categories for posts and replies are all fine.

PS Yes, other topic can be closed. Thanks.

Update: a record 0 is inserted despite the error, but no corresponding message record is entered. I can see one possibility: I've assumed that topics are 1...N (a single range) but if they're counted separately per category then... No, I don't think so. They were numbered 1..N earlier and I could add new topics.

Update 2: I inadvertently omitted AUTO_INCREMENT when recreating the index :woohoo:!

OK. That was it. Everything working now. I have successfully finished a migration from Ning. Just need to get avatars moved and then start to see what I can do to get everything else about the site working.

Hold is a value (normally it is 0 or 1) that defines whether a post is approved or not approved and should be of type tinyint(4). I don't know how this relates to the table _kunena_user_topics because the column is contained in the table _kunena_topics. It sounds like there's some database referential integrity problem at play (or possibly SQL syntactical problem).

Did you find that using Kunena Forum: Forum Tools » Recount Statistics answered your question about recounting your forum statistics?

Ah, ok. Makes sense. Thanks.

Is there any narrative account of the data structures anywhere? I can read and work out code if necessary but English is easier.

BTW No, recounting didn't fix (forum stats were fine), however I have a lead (see previous message).