×
Kunena 5.1.5 Released - Security Release (14 Oct 2018)

The Kunena team has announce the arrival of Kunena 5.1.5 [K 5.1.5] which is now available for download as a native Joomla extension for J! 3.8.x. This version addresses most of the issues that were discovered in K 5.1 and issues discovered during the development stages of K 5.1. This is a Security release.

× This is for users to help other users, to discuss topics that are related to forum administration in general or problems in running Joomla. This is not the place to ask for Joomla support. If you want assistance with Joomla please ask at forum.joomla.org

Solved SQL query to populate user_topics table?

More
4 years 8 months ago #1 by harmlessdrudge
Anybody got a SQL query for this? Or is there a built-in function I haven't come across yet?

Please Log in or Create an account to join the conversation.

More
4 years 8 months ago #2 by jimrowland
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?

Please Log in or Create an account to join the conversation.

More
4 years 8 months ago #3 by xillibit
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 don't provide support by PM, because this can be useful for someone else.

Please Log in or Create an account to join the conversation.

More
4 years 8 months ago - 4 years 8 months ago #4 by harmlessdrudge
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!
Last edit: 4 years 8 months ago by harmlessdrudge.

Please Log in or Create an account to join the conversation.

More
4 years 8 months ago #5 by jimrowland
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.

Please Log in or Create an account to join the conversation.

More
4 years 8 months ago #6 by harmlessdrudge
Sorry I wasn't clearer; I thought populate the table given a set of attachments and message nrs was enough. :unsure: 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.

Please Log in or Create an account to join the conversation.

  • sozzled
  • Visitor
4 years 8 months ago - 4 years 8 months ago #7 by sozzled
Replied by sozzled on topic SQL query to populate user_topics table?

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?
Last edit: 4 years 8 months ago by sozzled.

Please Log in or Create an account to join the conversation.

More
4 years 8 months ago - 4 years 8 months ago #8 by harmlessdrudge

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.
Last edit: 4 years 8 months ago by harmlessdrudge.

Please Log in or Create an account to join the conversation.

  • sozzled
  • Visitor
4 years 8 months ago - 4 years 8 months ago #9 by sozzled
Replied by sozzled on topic SQL query to populate user_topics table?
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?
Last edit: 4 years 8 months ago by sozzled.

Please Log in or Create an account to join the conversation.

More
4 years 8 months ago - 4 years 8 months ago #10 by harmlessdrudge
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).
Last edit: 4 years 8 months ago by harmlessdrudge.

Please Log in or Create an account to join the conversation.

  • Not Allowed: to create new topic.
  • Not Allowed: to reply.
  • Not Allowed: to add attachements.
  • Not Allowed: to edit your message.
Time to create page: 0.089 seconds