mysql - Export all posts made by one user

[kgirl]

Hi,

I'm trying to export all posts made by one member on our forum. Ideally I'd like these to be exported into csv format, but anything human readable will do. I'm able to see all posts by running the following mysql command

SELECT * FROM post WHERE username='billybob';

I can then see all the data I want, but exporting it out is proving a bit more tricky.

I'm running mysql 5.0.95, PHP 5.2.17, Apache 2.2.3 on CentOS 5. I'm fairly comfortable at the command line, but will install phpmyadmin or such if its easier.

The reason I need this is because one of our very valued members passed away, and a copy of all his posts on the site have been requested by his students.

Can anyone help please?

[squidsk]

you'll need shell access to be able to do this, run the following command:

mysqldump -uusername -p database_name post -w"username='billybob'" > ~/billybobs_posts.sql

[kgirl]

Thank you, I have shell access so I will give that a go.

Do you happen to know a way I can get it into CSV or some format that people can read easily?

--------------- Added 1362962204 at 1362962204 ---------------

I've managed to get it into .csv format, thank you for your help with the SQL.

If anyone else comes across this and needs to know the same thing, I used this command from the mysql prompt

SELECT * from post where username='billybob' INTO OUTFILE '/tmp/billybob.csv';

[DrPrepper]

Hi, I've just created a query that I needed for my vBulletin4 forum. But since I couldn't find a similar thread for this question in the vb4 section, I've decided to share it here.

SELECT post.postid, post.threadid, thread.title, post.username, FROM_UNIXTIME(post.dateline) as date, post.pagetext from post, thread WHERE post.threadid = thread.threadid AND post.username = 'XXXXXXXXXX' ORDER BY date;

Just replace XXXXXXXXXX with the username.

This query combines the tables 'post' and 'thread' to get an output where you have both thread title and post text.

Note: if you have a table prefix, you should use it in this query as well. (For example, if your post table is vb123_post, then you need to replace all 'post' occuranced above with 'vb123_post')