php中mysql定期删除,使用PHP和MYSQLi在数据库中每24小时删除数据

I like to know how you do this:

I have made a sort of a chatbox that automatic updates it self with a script and it loads up the latest 50 chat messages from database.

Now i wonder, how to delete messages after each 24 hours but keep the first new 50 messages and delete the older ones after that.

Is this possible with MYSQLi and PHP or must i do it with a better solution?

UPDATE

Followed advise from Lund and a fast response from my provider:

I am not able to do Cron Jobs or MYSQL Events with my current plan.

Also i do not have the money for upgrading it, so erm.... if there is

another solution, thank you for helping me out on this matter folks.

I have a table called: chatbox

and have this in it:

C_ID

chattext

name

date

解决方案

Pseudo code (Won't actually work without some edits to fit your code, but its an idea) - I don't know how your data is stored, or its variable names. Or your table name. Or basically any information that I would need to actually get this to work.

$sql = ("SELECT * FROM ChatLog ORDER BY TimeAdded DESC"); //Order by descending should show the newest ones first.

$index = 1; //set an index to loop through

while($row = mysqli_query($con, $sql) { //use a while loop to go through the table

if($index <= 50) { //for the first 50 records

//do nothing

} else { //for everything after 50 records

$chatId = $row['chatLogID']; //ID or unique value for the actual message.

$sql = ("DELETE FROM ChatLog WHERE chatLogID='$chatId'"); //Delete it from the table

mysqli_query($con, $sql);//Execute query.

}

}

You can set this up to execute everytime a message is stored, or check it against the current time of the server to remove it daily.

UPDATE: THIS CODE SHOULD BE WORKING WITH YOUR CODE

$sql = ("SELECT * FROM chatbox ORDER BY time DESC"); //Order by descending should show the newest ones first.

$index = 1; //set an index to loop through

while($row = mysqli_query($con, $sql) { //use a while loop to go through the table

if($index <= 50) { //for the first 50 records

//do nothing

} else { //for everything after 50 records

$chatId = $row['C_ID']; //ID or unique value for the actual message.

$sql = ("DELETE FROM chatbox WHERE C_ID='$chatId'"); //Delete it from the table

mysqli_query($con, $sql);//Execute query.

}

}