Delete entry if limit is reached

[iranor]

I have a mailsystem on my website.

Everytime I execute CleanMailBox on the specified player_id , I want the script to check if the user have more messages than allowed , and if so , delete the oldest one to make room for the new one.

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

$query = mysql_query("SELECT COUNT(message_id) as nbmail, MIN(message_id) as oldmail FROM mail_link WHERE player_id='$pid'");
$NbMsg = mysql_result($query, "nbmail");

//If the mailbox is full, delete the oldest message
if ($NbMsg > 2) {


$OldestMessage = mysql_result($query, "oldmail");

$Query = "DELETE FROM mail_link WHERE message_id = $OldestMessage";
sql_query($Query);

return 2;
}
return 1;
}

But this function doesn't works. I'm getting error saying that : Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/neomaster/

http://www/serv/hostfile/notes.php

on line 18

(BTW , line 18 is the first mysql_result in this function.)

 

[sleipnir214]

What version of MySQL are you running?



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[iranor]

4.0.18 I think.

 

[miahmiah900]

I think what you need to do is change this line:

$OldestMessage = mysql_result($query, "oldmail");

The string $query is not a mysql resource, it is a string.

On this line, you are getting a MySQL result:

$NbMsg = mysql_result($query, "nbmail");

So, on the mysql_result call, use $NbMsg instead of $query.

That should fix it... If not, I'm sure a review of the MySQL functions in the PHP manual would be educational.

 

[iranor]

I have this query

$limit = 26;
$cSQL = "SELECT count(message_id) AS actualCount FROM mail_link WHERE player_id=$pid";
$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());

}

But I need to know wich message_id has been deleted , since I want to add another query that will delete an entry in another table :

$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'");

$msgid will be the id of the message deleted in the last query.

 

[iranor]

anybody any ideas?

 

[sleipnir214]

Are you looking for a different answer from the thread in the PHP forum where you asked the same question?



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[iranor]

I ain't. With the resulting function in php forum , I realised this question was more oriented mysql forum.