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.

 

[DRJ478]

1. SELECT count(id) FROM myTable WHERE player_id = "myPlayer"

2. If the count > maximum

3. Delete from the table the number of records that are exceeding the quota. You can get that by $overage = $count - $maximum. If your message id's are autoincrement you can safely assume that a smaller id has been added earlier into the table. Therefore the following wuery should work:
DELETE FROM myTable WHERE player_id = "MyPlayer" LIMIT $overage

That's the basic idea.

 

[iranor]

I tried this :

function CleanMailBox($pid) {
mysql_connect("localhost","root","b1qxsst") 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;


}

And I receive this error :

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/neomaster/

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

on line 18

 

[skiflyer]

DELETE FROM myTable WHERE player_id = "MyPlayer" LIMIT $overage

Will probably work, but it's one of those things that may work but shouldn't. There's nothing in either the SQL'92 or the MySQL spec that says it should, so you shouldn't be too surprised if a future upgrade breaks it.

I think your approach is probably safer... but I'm uncertain where line 18 is, so it's hard to help...

As a general rule I would suggest the following structure for all mysql_query() calls...

$sql="SELECT OR INSERT OR WHATEVER";
$result=mysql_query($sql) or die(mysql_error().'<br /><br />'.$sql.'<br /><br />Function:'.__function__);

It will make it much easier to diagnose these problems.

 

[iranor]

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'") or die(mysql_error().'<br /><br />'.$sql.'<br /><br />Function:'.__function__);
$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;
}

And the output :

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'player_id'].'' at line 1



Function:cleanmailbox


(BTW , line 18 was on the full script , line 18 here means 7 here)

 

[skiflyer]

To know for certain you're going to need to pull that query out and put it into the variable $sql... but it appears you didn't send a value for $pid.

 

[iranor]

When I call the function I use CleanMailBox($pid); where pid is the id on the player.

 

[KarveR]

Dont the messages have a date of some sort (creation date or last modified or sender date) that you can base your query on ? maybe its worth adding a date field for this for future ease.

Also this would propbably be best asked in the myql forum instead of the php forum, where you may find more expertise.

(no offence to anyone here but I know a lot of us participate in both, however there are experienced members of the mysql forum who dont participate in the PHP forum).

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[iranor]

I can't base myself on the date. This structure is different from others.

There's two tables : mail and mail_link .

mail contains the information of the mail : date , content , subject , author.

mail_link contains the player id who will receive with the message id he is linked to.

I will use the ID of the mail to check the oldest message , as suggested by DRJ478

I may only needs help to set-up my function CleanMailBox.

 

[KarveR]

in that case what are the exact structures of the tables , and what key are they / can they be joined on ? (eg how do you associate what content was sent to whom, and when)


//karv

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[iranor]

Table mail :
id
author
content
date
subject

Table mail_link :
id
player_id
message_id
read

When a user posts a message , the mail data is inserted in the mail table , while the id of the player who will receive the message will be added in mail_link with the id of the message.

If a user forward a message , a new query is inserted in mail_link , wich is linking to the mail table and etc.

 

[DRJ478]

The DELETE query syntax allows for ordering. So, if you keep your message_id column incrementing consistently you just alter the suggested query:

DELETE FROM myTable WHERE player_id=$whatever ORDER BY message_id LIMIT $overage

That way you don't rely on the undocumented orderin within the physical table since you establish the ordering yourself. It also means one query will erase as many messages as needed.

 

[iranor]

What whouls I define ad $overage?

mysql_query("DELETE FROM mail_link WHERE player_id=$pid ORDER BY message_id LIMIT $overage");

explanation : users will have a mail limit of 20. I want no message to be deleted until they have reached the limit.

When they have reached it , start delete the oldest one when new one are coming.

 

[DRJ478]

The steps are still the same:
1. Count the number of messages.

SELECT count(message_id) FROM mail_link WHERE player_id=$pid

2. Evaluate it against the maximum number, e.g. 20:
If equal or larger calculate the overage, which would be limit - acutalCount + 1

3. Use the delete SQL.

# define max. number of messages
$limit = 20;
$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());
   # you could check how many rows were affected next...
}

 

[iranor]

Thanks!!! Everything works well

here's a star

 

[iranor]

BTW , is there a way to know the id of the message that was deleted? I wish to add this little query :

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

So the resulting :

$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());

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

But I don't know how to get the id of the message.

(Thanks for helping me DRJ478 , you are soo good)

 

[DRJ478]

You can only getthe message_id's with a SELECT query before they are deleted. So, here's an alternative that will give the number of messages and the message id's.
1. Get the result set:

SELECT * FROM mail_link WHERE player_id=$pid ORDER BY message_id

2. Get mysql_num_rows to ascertain the number of messages

$actualCount = mysql_num_rows($cResult);

Again, if >= $limit, to get the message ids loop trhough the result set while the messages are to be deleted.

I don't understand your query and use of $msg_id in the new code you added. What do you want to achieve?

 

[iranor]

Well , I have 2 tables.

Table mail :
id
author
content
date
subject

Table mail_link :
id
player_id
message_id
read

When the script is actived , this delete the oldest entr of the mail_link table.

I would like to delete in the same time the data in the mail table corresponding to the mail_link table.

Then , a select query looks in the mail_link with the message id to see if any other data is linked to the message in table mail.

If there's only one mail_link linking to the table mail , delete the mail_link and mail table data.


This check will be made everytime someone sends a message.

 

[DRJ478]

I assume that in the mail table id is equivalent to message_id in mail_link. So, what you really want is to find out the id's to be deleted and delete them in both tables.
You can build your SQL to have a FIND_IN_SET comparison where you pass the set of IDs as a comma separated list of strings, e.g.

DELETE FROM mail_link WHERE player_id=$pid AND FUND_IN_SET(message_id,$idSet)

$idSet would be a string "'1','23','55','78'" with the quoted id's separated by a comma. You can build the set when looping through the $overage id's.

You need not issue a SELECT query to find out if anything was linked. The DELETE will work and return 0 zero if no records were affected.

 

[iranor]

$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 AND FIND_IN_SET(message_id,$idSet) ORDER BY message_id LIMIT ".($row['actualCount']-$limit+1);
$dResult = mysql_query($dSQL) OR die("Delete query failed: ".mysql_error());

}
}

Outputs

Delete query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY message_id LIMIT 1' at line 1