Export Threads

[@ngel]

I need to export the posts of the threads of certain forums in csv, maintaining information such as the name of the author, the date of the post etc. To give you an example, what I need is something like this:

____________________

Forum / Subforum
Title of Thread

Author 1
Date
Post 1

Author 2
Date
Post 2

...
____________________

I've tried to export the post table but then posts are not structured under Threads and Forums.
I imagine I should export the posts table along with something else, but I don't know what.
Can you please help me?

[kh99]

You could do something like this:

SELECT  forum.title_clean, thread.title, post.title, post.username, post.dateline, post.pagetext
INTO OUTFILE 'posts.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' 
FROM post
    LEFT JOIN thread ON (post.threadid = thread.threadid)
    LEFT JOIN forum ON (thread.forumid = forum.forumid)
WHERE thread.visible = 1 AND post.visible = 1

I imagine you'll need to tweak some things, but this might be a starting point.

[@ngel]

Thank you VERY much for your help.
When I try to execute it though (both on phpmyadmin and in the admin panel) I get

error number: 1045
error desc: Access denied for user

what should I do?

[kh99]

I think that means that the mysql user you're logged in as doesn't have some permission that's necessary. Maybe it can't create a file, I'm not sure. I don't know much about mysql administration so I don't know what to do about it. If you don't do your own administration then maybe you can ask your host about it.

Maybe you could try specifying a full path instead of just 'posts.csv', if you know the path to your directory on the host. Or maybe if the server is on a different computer you won't be able to do it at all, I'm not sure.

Another possibility would be if you have a database backup you could restore it to another computer (like your local computer, installing mysql first if you don't already have it). Or you could abandon the sql idea and write a php script to do it. Take ou tthe entire "INTO OUTFILE" line and write a loop to get each row and print it to a file.

ETA: looking at this page in the mysql docs: http://dev.mysql.com/doc/refman/5.6/en/select-into.html it says you need FILE privilege and the file would be created on the server's host, so maybe using INTO OUTFILE isn't an option for you.

[@ngel]

Thank you very much, so can I just print the results on screen instead of creating a file until I find out the exact query?