Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
  #1  
Old 03-10-2013, 12:53 AM
kgirl kgirl is offline
 
Join Date: Jul 2004
Location: Wales, UK
Posts: 10
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default 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?
Reply With Quote
  #2  
Old 03-10-2013, 04:28 AM
squidsk's Avatar
squidsk squidsk is offline
 
Join Date: Nov 2010
Posts: 969
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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
Reply With Quote
Благодарность от:
kgirl
  #3  
Old 03-10-2013, 11:46 PM
kgirl kgirl is offline
 
Join Date: Jul 2004
Location: Wales, UK
Posts: 10
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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';
Reply With Quote
  #4  
Old 03-16-2015, 10:14 AM
DrPrepper DrPrepper is offline
 
Join Date: Jan 2012
Posts: 19
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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')
Reply With Quote
Благодарность от:
kh99
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 12:35 PM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.03914 seconds
  • Memory Usage 2,192KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (4)bbcode_code
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (4)post_thanks_box
  • (2)post_thanks_box_bit
  • (4)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (2)post_thanks_postbit
  • (4)post_thanks_postbit_info
  • (4)postbit
  • (4)postbit_onlinestatus
  • (4)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • fetch_musername
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • post_thanks_function_fetch_thanks_bit_start
  • post_thanks_function_show_thanks_date_start
  • post_thanks_function_show_thanks_date_end
  • post_thanks_function_fetch_thanks_bit_end
  • post_thanks_function_fetch_post_thanks_template_start
  • post_thanks_function_fetch_post_thanks_template_end
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete