CODEWITHSUNDEEP
phpmysql
klennepette
klennepette

Reputation: 3196

Update MySQL timestamp even if row data is unchanged

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);
}

Upvotes: 7

Views: 8890

Answers (2)

Syntax Error
Syntax Error

Reputation: 4527

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

Similar to using NOW() - as long as a TIMESTAMP column doesn't allow NULL values you can force it to update by setting that column to NULL.

UPDATE mytable SET timestamp=NULL

Upvotes: 6

Vincent Mimoun-Prat
Vincent Mimoun-Prat

Reputation: 28541

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, ...);

Upvotes: 9

Related Questions