Exporting single user's posts to csv

[ilfllc]

Hi, is it possible to export a single user's posts into csv or another format? Ideally I'd like to export all posts made by a specific user, along with date/time stamps and IP used at the time of posting. Just wondering if there's a query someone has used to accomplish this successfully in the past.

Thanks!

[Trevor Hannant]

All the data you're looking for is held in the 'node' table in the database (username, post text, IP address, timestamp) - you could simply query that from a backup (I'd use a backup instead of the live database for safety) in phpMyAdmin then export the results.

[glennrocksvb]

'node' table only contains preview text. If the OP wants all the content then they have to join the 'text' table.

[Wayne Luke]

Your basic query will be:
select n.*, t.* from `node` as n join `text` as t on (n.nodeid=t.nodeid) where n.userid=1 into outfile 'output.csv' fields terminated by ',' lines terminated by '\r\n'

You can adjust the fields and the userid. Though if your server is secured in certain ways, this will fail.

If you have remote access to the database, you can use MySQL Workbench to do this as well.

[ilfllc]

Thanks very much, Wayne. I was able to piece it together from the sample query you provided.

Cheers

[ilfllc]

Actually, I realized what I really need would be deleted posts by a specific user within a specific timeframe. Is that even possible? I'm not sure how long, or even if, vBulletin retains deleted posts.

Thanks in advance!

[Wayne Luke]

You can run queries against the publishdate field in the node table to set a timeframe. This is a UNIX timestamp so if you need to manipulate it, you would have to use the date time functions in MySQL.

"Soft" deleted posts are not actually deleted from the database. Permanently deleted posts are removed immediately.

[ilfllc]

Thanks again, Wayne.