vb.org Archive - mysql - Export all posts made by one user

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - mysql - Export all posts made by one user (https://vborg.vbsupport.ru/showthread.php?t=295949)

kgirl

03-10-2013 12:53 AM

mysql - Export all posts made by one user

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

Code:

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

03-10-2013 04:28 AM

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

Code:

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

kgirl

03-10-2013 11:46 PM

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 [DATE]1362962204[/DATE] at [TIME]1362962204[/TIME] ---------------

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

Code:

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

DrPrepper

03-16-2015 10:14 AM

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.

Code:

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')

All times are GMT. The time now is 10:39 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.00937 seconds Memory Usage 1,716KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (4)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: