Member Avatar for santunu23

is it possible to move one mysql table data to another table after 1 or 2 days like we have 2 table one is tbl1 another one is tbl2 tbl1 contain column uname and performance i want to move uname and performance data in to tbl2 after 2 or 3 days how t o do that

  • 3 Contributors
  • 6 Replies
  • 311 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

A reliable pattern is: add a timestamp column to the rows you want to age out, then run one scheduled job (either a single MySQL EVENT or a cron job that calls a PHP script) that moves everything older than N days. That avoids creating per-user events and the confusing random names you hit — don’t generate a separate event per user. As suggested, you want to base the decision on a date difference; do that with a stored created_at/inserted_at column. ’s cron idea is perfectly valid if you prefer keeping scheduling outside MySQL. If you used rand() to name events (as discussed), stop doing that — it makes management and debugging hard.

Example: a single daily MySQL event (conceptual)

CREATE EVENT archive_course_entries
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
  INSERT INTO course_finish (course_id, username, course_name, finished_at)
    SELECT course_id, username, course_name, NOW()
    FROM course_entry
    WHERE created_at <= NOW() - INTERVAL 2 DAY;

  DELETE FROM course_entry
    WHERE created_at <= NOW() - INTERVAL 2 DAY;
END;

If you prefer cron + PHP, run a small script every hour/day that moves rows in batches (to avoid long locks). Example pattern (PDO, transactional, batch-safe):

// pseudocode: connect with PDO, then:
$pdo->beginTransaction();
// fetch up to N ids older than 2 days
// INSERT ... SELECT WHERE id IN (...)
// DELETE WHERE id IN (...)
$pdo->commit();

Checklist / cautions: 1) Add and index a DATETIME column (e.g., created_at). 2) Test on a copy and keep backups. 3) Use transactions or process in small batches to prevent data loss or table locks. 4) If the table is large, consider partitioning by date so old data can be archived quickly. 5) If using MySQL events, ensure event_scheduler=ON and avoid creating many dynamic events — one well-written job is easier to maintain.

Recommended Answers

All 6 Replies

Member Avatar for Arif_suhail_123

yah it is possible, but u said after one days or two days, but does not describe after one days or two days of what, means after registration, after activation or of there payment, i am assuming it can be any thing, you can do some thing like this

$day=current_date-registration_date

if($day>2)
run your query to get this informatin from table1,
run your query to insert it in the table2,
run your query to delete that value from table1,

job done its just a structure not the code, and there other complication too, unless you provide some code here, it will be very hard for others to help you out,

thanks

Member Avatar for santunu23

ok i am tring some code and back to you soon

Member Avatar for santunu23

sorry for the late response i was sick thats why can't post the code is here i am ok with this i can find the event and it work as i expected but there are also a problem a cripy problem actually that is my event name is't change ramdomly , accorrding to my code is should not be so how to change event name ramdoly any suggestion ..

$insertquery="CREATE EVENT JOY
    ON SCHEDULE  AT CURRENT_TIMESTAMP + INTERVAL 3 MINUTE
    DO
      BEGIN
       insert into course_finish (course_id, username, course_name) select username,course_name,course_id from course_entry where username = '".$user."';

delete from course_entry where username = '".$user."';
Member Avatar for Member #120589

Alternatively if you want to run a routine at regular intervals, you can set CRON JOBS to run a php script at a particular time / interval.

Member Avatar for santunu23

diafol it's done using rand()

Member Avatar for Member #120589

I have no idea what you mean. You want to run a routine at random intervals?

sorry for the late response i was sick thats why can't post the code is here i am ok with this i can find the event and it work as i expected but there are also a problem a cripy problem actually that is my event name is't change ramdomly , accorrding to my code is should not be so how to change event name ramdoly any suggestion ..

This makes little sense to me. I imagined that you wanted to run an 'archive' type routine that moved records from one table to another on a regular-ish basis.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.