Does this mysql code work for deleting people's PMs if they have only 1 PM?

[SnickersTK]

been making this code and I admit that I am very new to mysql, so it might not make sense.

I haven't dared to try it out.

PHP Code:

<?php
$connection = mysql_connect("localhost", "username", "password");
   mysql_select_db("database", $connection); //connect to server and select database
   
require ("global.php"); 
$time = time() - (60 * 24 * 60 * 60);

$userid_result = query("SELECT `userid` ' 
        . ' FROM `user` ' 
        . ' WHERE `pmtotal` = 1 ' 
        . ' INTERSECT '
        . ' Select `fromuserid` ' 
        . ' FROM `pmtext` ' 
        . ' WHERE `dateline` < $time");
        //Make sure that the user-ids selected with 1 total PM only gets choosen if the PM is 60 days old.  


$sql = ' TRUNCATE `*` ' 
        . ' FROM `pm` ' 
        . ' WHERE `userid` = $userid_result';
         
$sql = 'TRUNCATE `*` ' 
        . ' FROM `pmtext` ' 
        . ' WHERE `fromuserid` = $userid_result'; 
         
MySQL_query($sql) or die('MySQL Failed Because: ' . mysql_error()); 

?>

Any assistance is greatly appreciated. I think it is a nice hack. I am running a giant board and all members who signs up gets an welcome PM (very popular and I don't want to get rid of it)

[Tuk4]

Well, never tried to work with PMs and stuff.
But suggestions to how i would make it, i would check the registration date - and check the message in PM date - and if 1. there for if the user have new message from another user, and he only have 1 pm, it wont delete it everytime.

[Farcaster]

It is notable that in MySQL v4.0 and on, TRUNCATE TABLE drops ALL ROWS, and does not take a WHERE clause. You also need to execute the queries and return the rows before using the results in another query. Even then, you are going to need to use explode to put them into a comma delimited string that you can use in the WHERE clause with IN. The easiest thing to do would probably be to execute a query to get all of the PMs you want to delete and then iterate through them and delete them using the PM datamanager. If you do it manually, you need to: 1) update the user table with the new post count, 2) delete the references in PM, and 3) delete the pm text from the pmtext table, BUT only if no other pm is referencing the same textid.

[Attilitus]

Originally Posted by Farcaster

It is notable that in MySQL v4.0 and on, TRUNCATE TABLE drops ALL ROWS, and does not take a WHERE clause. You also need to execute the queries and return the rows before using the results in another query. Even then, you are going to need to use explode to put them into a comma delimited string that you can use in the WHERE clause with IN. The easiest thing to do would probably be to execute a query to get all of the PMs you want to delete and then iterate through them and delete them using the PM datamanager. If you do it manually, you need to: 1) update the user table with the new post count, 2) delete the references in PM, and 3) delete the pm text from the pmtext table, BUT only if no other pm is referencing the same textid.

Ha ha... quoted for Notability.

[Dismounted]

And the ' . 's aren't needed.