Insert counter if doesn't exist otherwise update it

Databases
1

I want to limit the uploads on a site to 3 uploads per user.

I have therefore created a separate table to store a count of the uploads per email address as follows


CREATE TABLE IF NOT EXISTS `uploadsperuser` (
  `strEmail` varchar(255) NOT NULL,
  `intCount` int(3) NOT NULL,
  PRIMARY KEY (`strEmail`,`intCount`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Before inserting a record in the uploads table I want to check the count and insert or increment depending on which is necessary.

Is the following close to what I need?


$biEmail = "tester@test.com";

$sql = "INSERT INTO uploadsperuser (strEmail, intCount) VALUES ($biEmail, 1) ON DUPLICATE KEY UPDATE intCount = intCount + 1";

Thanks inadvance

2

Your primary key has to be only strEmail, otherwise once the counter has been updated to 2, the next time you’ll insert a new row with counter value 1 for the same email.

3

Thanks Guido for spotting this error. Much appreciated. I haven’t done any database work for quite some time so very rusty.

4

I am getting the following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ ‘1’) ON DUPLICATE KEY UPDATE intCount = intCount + 1’ at line 1

Here is the code - any ideas what the syntax error is?



$biEmail = "tester@test.com";

$sql = "INSERT INTO uploadsperuser (strEmail, intCount) VALUES ($biEmail, 1) ON DUPLICATE KEY UPDATE intCount = intCount + 1";
5

No worries, I worked out the problem.

Should have been



$sql = "INSERT INTO uploadsperuser (strEmail, intCount) VALUES ('$biEmail', '1') ON DUPLICATE KEY UPDATE intCount = intCount + 1";