INSERT IGNORE INTO and UPDATE in one statement
I am running a script that serves downloads for my users. I would like to track their traffic at the level of each byte and I count how many bytes they have uploaded to $bytes
. I want to register it in my database and I am using the following function:
register_shutdown_function(function() {
global $bytes;
/* Save the traffic to the database */
$db = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$st = $db->prepare('INSERT IGNORE INTO `stats`
SET `date` = CURDATE(), `bytes` = :bytes');
$st->bindParam(':bytes', $bytes);
$st->execute();
$st = null;
$db = null;
});
This query runs once, when the download is complete, a new entry in the table with the following data:
date | bytes
------------------------
2013-02-03 | 2799469
however, with every other load, the field bytes
does not change. There are no new records and no changes to the records that are already in the table. It is quite obvious that the problem is, the request is trying to insert a record, but if it already exists, then it is aborted. I need an update instruction like this:
UPDATE `stats`
SET `bytes` = `bytes` + :bytes
WHERE `date` = CURDATE()
but I would like to do the whole operation in one request. A query that will create a record if it doesn't exist, and if it does, update the existing record.
Can this be done or will I have to run two queries on every boot?
source to share
You might want to explore ON DUPLICATE KEY UPDATE
. You can read about it here .
Your request will look something like this.
$st = $db->prepare("INSERT INTO `STATS`
VALUES('CURDATE()', :bytes)
ON DUPLICATE KEY UPDATE `BYTES` = `BYTES` + :bytes");
You should also avoid using INSERT IGNORE INTO
it because no error will be generated in the case of duplicate lines, only a warning.
source to share