Maximum of entries , delete the oldest in the limit (mysql) 1

[iranor]

Hi all. I have a mail system on y website , but I want a number of maximum mail in their account.

The tables works like this :

Table mail : The id of the message , author , subject , date and content.

Table mail_link : The player id (to message) , message_id (the id of the message in table mail) and read ( if user have read or not the messgae)

When a user send a message to someone, the message info is stored in the mail table. Then , it adds the id of the player that will receive the message and the id of the message in mai_link.

I want to call a function named CleanMailBox everytime a user send a message , this function will check the guy that will receive the message if he has the maximum messages autorised. If so , I want the script to delete the oldest entry to let space for the new one.

 

[iranor]

function CleanMailBox($ToPlayerID) {
mysql_connect("localhost","root","b1qxsc35stdq99st") or die('MySQL error!');
mysql_select_db("neomaster_filedb") or die('MySQL error!');

$limit = 26;
$cSQL = "SELECT count(message_id) AS actualCount FROM mail_link WHERE player_id=$ToPlayerID";
$cResult = mysql_query($cSQL) OR die("Query failed: ".mysql_error());
$row = mysql_fetch_assoc($cResult);
# evaulate against max number
if ($row['actualCount']>= $limit){
$dSQL = "DELETE FROM mail_link WHERE player_id=$pid ORDER BY message_id LIMIT ".($row['actualCount']-$limit+1);
$dResult = mysql_query($dSQL) OR die("Delete query failed: ".mysql_error());
//////////////////////////////////////////

$resultat = mysql_num_rows(mysql_query("SELECT * FROM mail_link WHERE message_id='$msgid'"));
if ($resultat != 0) {}else{
mysql_query("DELETE FROM mail WHERE id='$msgid'");
}
}
}

This function is called everytime a user sends a message. But , the function acts on the player who will receive the message. If user have 25 mails , the box is cleaned , the older message is deleted and the new one comes. But , when the message is deleted(in mail_link) , I need to check in Mail_link if anything else links to the message. If nothing does, delete the entry in table mail for the current message.

BUT , if another data in mail_link is linking to the mail , don't delete the message in mail. The message would be deleted when the user having the lastest mail_link to the message is deleted. You understand?

I know that this concept is a little harder...

I need to add the query that is after the //////

$MSGID , Being the id of the message who was too old so he was deleted.

(I called the function by CleanMailBox("1"); )

 

[DRJ478]

Now we are mixing things up. Since we need the IDs to be captured for deletion in multiple tables the LIMIT approach is not applicable anymore. It is aslo not necessary anymore since the IDs are specifically named. The LIMIT portion of the query is superfluous and should be removed.

After the removal you want to ascertain if there are further references to the mail_id in the mail_link table. Just issue a count() for that message_id and see if it returns more than 0 zero. If not, delete the message in the mail table. This procedure is basically the same as the initial SELECT statement, just that the WHERE clause does not filer by player_id.

 

[iranor]

This is my resulting code :

function clean_mail_box($ToPlayerID) {
$limit = GEN_MSG_LIMIT;
$row = $this->db->query_fetch_assoc("SELECT count(message_id) AS actualCount, min(message_id) AS messageid FROM mail_link WHERE player_id=$ToPlayerID");
if ($row['actualCount']>= $limit){
$this->db->query("DELETE FROM mail_link WHERE player_id=$ToPlayerID ORDER BY message_id LIMIT ".($row['actualCount']-$limit+1);
$selectnum = $this->db->num_rows("SELECT * FROM mail_link WHERE message_id=".$row['messageid']."");
if ($selectnum == 0) {
$this->db->query("DELETE FROM mail WHERE id = ".$row['messageid']."");
}
}
}

Thanks for the help!