I'm building a small report in a PHP while loop. The query I'm running inside the while() loop is this:
INSERT IGNORE INTO `tbl_reporting` SET datesubmitted = '2015-05-26', submissiontype = 'email', outcome = 0, totalcount = totalcount+1
I'm expecting the totalcount column to increment every time the query is run. But the number stays at 1. The UNIQUE index composes the first 3 columns.
Here's the Table Schema:
CREATE TABLE `tbl_reporting` (
`datesubmitted` date NOT NULL,
`submissiontype` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`outcome` tinyint(1) unsigned NOT NULL DEFAULT '0',
`totalcount` mediumint(5) unsigned NOT NULL DEFAULT '0',
UNIQUE KEY `datesubmitted` (`datesubmitted`,`submissiontype`,`outcome`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
When I modify the query into a regular UPDATE statement:
UPDATE `tbl_reporting` SET totalcount = totalcount+1 WHERE datesubmitted = '2015-05-26' AND submissiontype = 'email' AND outcome = 1
...it works.
Does INSERT IGNORE not allow adding numbers? Or is my original query malformed?
I'd like to use the INSERT IGNORE, otherwise I'll have to query for the original record first, then insert, then eventually update.
Aucun commentaire:
Enregistrer un commentaire