Where are PM's stored in the DB, got a subpoena

[Kestryll]

I received a subpoena for a few member's PMs but do not know how to find them in the database.

Is there a clear text file or table for PMs or are they encrypted?

[HMBeaty]

Just use this: http://www.vbulletin.org/forum/showthread.php?t=123166

[Kestryll]

That would help me read them individually but I need to be able to pull an individual user's PMs from the database to comply with the subpoena.

[Wayne Luke]

The pm table stores the meta information and the pmtext table stores the actual text. They are not encrypted.

[HMBeaty]

Looks like they're in the pmtext table

EDIT: Wayne beat me to it

[Kestryll]

The pm table stores the meta information and the pmtext table stores the actual text. They are not encrypted.

I found them in the database and can see how you Browse by using phpMyAdmin but I'm a relative newcomer with databases.
I let mine do it's things and don't stick my grubby paws under the hood, we get along better that way.

How would i use phpMyAdmin to isolate for download a single user's PMs?

[HMBeaty]

Select each one you want to download, then at the bottom, click export

[Kestryll]

Select each one you want to download, then at the bottom, click export

Seriously?

Wow, I've got almost 100,000 members, so I have to browse the table of everyone's PMs and select just the PMs of the individual members named in the subpoena!?!

That will take forever! The DoJ had better be paying me for all those hours!

[Lynne]

Try these on your test site and see if they work.

SELECT message FROM pmtext WHERE fromuserid = XXX;

SELECT message FROM pmtext WHERE touserarray LIKE '%i:XXX;s:%';

Change XXX to the userid you are interested in.

[trankin]

I had the same situation, I used php to compile sql queries:

$conn = mysql_connect( 'host', 'user', 'password' ) or die( mysql_error() );
mysql_select_db( 'dbname', $conn) or die( mysql_error() );

$useridName = array(
// list userid => username here
 '1' => 'username1'
,'2' => 'username2'
,'3' => 'username3'
);

$userid = array_keys( $useridName );

// if you want to filter by date
$datefilter = ' and pmtext.dateline >= ' . mktime(0, 0, 0, 1, 1, 2011);


for($i=0, $j=count($userid); $i<$j ; $i++){
    $sql = 'select count(*) as TotalPM from pmtext where pmtext.fromuserid = \'' . intval($userid[$i]) . '\'';
    $sql .= $datefilter;
    $result = mysql_query( $sql, $conn ) or die( mysql_error());
    if($row = mysql_fetch_assoc($result)){
        print($userid[$i] . "\t: " . $useridName[$userid[$i]]  . "\t: " . $row['TotalPM'] . "\n");
    }
    #print $sql . "\n";
}

$columnList =  '';

$columnList .= 'pmtext.fromuserid';
$columnList .= ', pmtext.fromusername';
$columnList .= ', pmtext.title';
$columnList .= ', pmtext.message';
$columnList .= ', FROM_UNIXTIME( pmtext.dateline ) as dateline';

$sqlAll =  'select ' . $columnList . ' ';
$sqlAll .= 'from pmtext where ';
$sqlAll .= 'pmtext.fromuserid in ( ';
$sqlAll .= implode( ',', $userid ); 
$sqlAll .= ' ) ';
$sqlAll .= $datefilter;


echo $sqlAll;

echo "\n";

mysql_close( $conn );
exit(); 

Hope this help!

[Suiram]

here's something you could do assuming the users did not delete their pm's. if they didn't it would work like a charm. you manually change their passwords and then log in as them. it's your site so you're god. if they did delete them then you fall back on the database extraction.

i don't want to hijack the thread, but will pose my own question. is there a way to make vbulletin not keep any messages or threads in the database if they are deleted? is it some legal requirement that they be retained? in fact i think i did make my own thread about this question, or a very similar one. what if i, as site owner/admin do not want pm's stored in the database once a user deletes it?

[Lynne]

If a user deletes a PM, and the person who sent the PM deletes it from their sent box, then the PM will no longer be in the database. But, both users - the sender and receiver - must delete it.

[trankin]

...
SELECT message FROM pmtext WHERE touserarray LIKE '%i:XXX;s:";
...

Just to correct the query here:

SELECT message FROM pmtext WHERE touserarray LIKE '%i:XXX;s:%'; 

[Lynne]

Whoops! Thanks. I'll go correct mine.

[peeceelee]

To obtain all posts from a user, could we just do a similar query to the post table? Would this cover all posts by a user?

SELECT pagetext FROM post WHERE userid = XXX

Would we need to grab from postedithistory as well to get the actual original posts and any consequent edits? Anything else I'm forgetting?

Thanks!