在特定条件下归档数据

Question

I have two tables, projects and archive with identical columns. As you understand, I need to archive projects in the archive table, under certain conditions.

I wrote two requests, and they work fine if executed separately, but I'd like to assemble them.

$sql = "INSERT INTO archive (idDocument,documentNumber,submissionDate,projectName)
        SELECT idDocument,documentNumber,submissionDate,projectName
        FROM projects
        WHERE id='".$id."'";

$sql = "DELETE FROM projects
        WHERE id='".$id."'";

1/ First of all, how can I assemble these two requests? I keep getting syntax errors.

2/ Now, I assign projects a status (in a column named status) from "1" to "3" in PHP and I'd like to archive these projects two months after they have reached status "3". How can I do so?

Thank you very much for your help.

Answer 1

Do not know what You mean by assembling, maybe You want to query these two queries in just one simple call, then each query should be ended by query delimiter - a semicolon ;, like this:

$sql = "INSERT INTO archive (idDocument,documentNumber,submissionDate,projectName)
    SELECT idDocument,documentNumber,submissionDate,projectName
    FROM projects
    WHERE id='".$id."';

    DELETE FROM projects
    WHERE id='".$id."';";

If You want to archive+delete the projects with status 3 after two months they had been statused by 3, You will have to do these things:

1. when updating the project to status 3 also set the date_changed = NOW() (or similar expression)
2. write down a CRON job that will be executed let's say every day at 3:00 am.
3. the CRON job will call a PHP script that will walk down the projects table and archive + delete all the projects with status 3 where date_changed is less then or equal to NOW() - INTERVAL '2' MONTHS...

That should do the trick...