Deleting Data each 24 hours in Database with PHP a

问题:

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

回答1:

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.
    }

}

回答2:

You should check MYSQL Events.

You can create an Event (e.g. the delete function) an schedule it to run once a day.

MySQL Create Event docs