CODEWITHSUNDEEP
phpmysql
Lea Castel
Lea Castel

Reputation: 11

Empty a MySQL table hourly

I have a chat script written on PHP Ajax and MySQL. It keeps the message logged on a table "usermsg".

My problem is that I have only a MySQL db of 100mb and I can't keep all the messages, as I have 55 users and my db quota is not enough to stock all and when it's full my chat stops automatically. So I perform truncat from PhpMyAdmin each time I feel that it is almost full. What I want to do is somehow perform truncat hourly so it can clear my table every hour.

Can somebody help with a PHP script or something to do that? Thanks.

Upvotes: 0

Views: 4584

Answers (3)

Tamil Selvan C
Tamil Selvan C

Reputation: 20209

Use cronjobs to execute the php for an hourly or use mysql events to achieve this task

Method 1:

create a php and do cron jobs for it

<?php
// write database connection code
mysqli_query("TRUNCATE TABLE tablename");
?>

and call this php in cron

php /path/to php file

Method 2:

create mysql EVENT. give privilege to accesss event for table 

SET GLOBAL EVENT_SCHEDULER = ON;
CREATE EVENT e_truncate
    ON SCHEDULE
      EVERY 1 HOUR
    DO
      TRUNCATE TABLE tablename

Upvotes: 3

Jay Bhatt
Jay Bhatt

Reputation: 5651

You can use event scheduler for this.

In phpmyadmin run following queries.

Enable it using 

SET GLOBAL EVENT_SCHEDULER = ON;

And create an event like this

  CREATE EVENT delete_messages

    ON SCHEDULE EVERY 3 DAY

    DO

    TRUNCATE TABLE 'tableName';

Here's a nice tutorial on this.

Phpmyadmin event scheduler

Upvotes: 3

Ben
Ben

Reputation: 913

I feel doing it hourly ignores a simpler option.

Another option is to remove the oldest entry every time a new entry is made. This removes the need for a cron job which dumps the entire table, as that could interrupt the chat by removing entries made only moments ago that are still relevant to the chat.

So in your "addMessage" sql, simply remove the oldest entry before you submit the new one.

Upvotes: 3

Related Questions