Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update MySQL timestamp even if row data is unchanged

Tags:

php

mysql

I have a PHP script that imports sql scripts into a MySQL 5 database. Each script contains nothing but UPDATE statements that each update 1 row in a table (MyISAM).

If a row has not been inside one of these scripts for 2 days it must be deleted. The table has a timestamp column. However when the UPDATE statement doesn't change any columns the timestamp is not updated and I have no way of telling wether the row was in the import file or not. Is there a way to force this timestamp update, even if no data changes?

EDIT: Further clarification.

The importfiles are gzipped files that contain about 450.000 rows, each row is 1 UPDATE statement.

Here's the PHP function that handles the import files:

private function ImportFile($filename) {
    $importfile = gzopen($filename, "r");
    if (!$importfile) {
        throw new Exception("Could not open Gzip file " . $filename);
    }

    while (!gzeof($importfile)) {
        $line = gzgets($importfile, 4096);
        if (!$line) {
            throw new Exception("Error reading line number $line Gzip file $filename");
        }

        if (strlen(trim($line)) > 0) {
            $this->DB->Query($line);
        }
    }

    gzclose($importfile);
}
like image 897
klennepette Avatar asked Mar 11 '11 09:03

klennepette


1 Answers

You could simply have an update for all the fields that where not updated, for instance:

UPDATE mytable SET timestamp=NOW() WHERE id IN (1, 5, 6, ...);
like image 143
Vincent Mimoun-Prat Avatar answered Nov 14 '22 22:11

Vincent Mimoun-Prat