MySQL incrementing int with PHP

Question

I'm new to PHP and mysql so this is probably an easy question to answer.

I'm making a "image A vs image B" site (just so I can learn a few PHP+mysql concepts).

Two images load on screen, you click the one you like most. If you click on image A it gets +1 added to its rating, and image B gets + 1 added to its negative rating. I want the positive votes and negative votes to be separate which is why I don't use just one value.

The table looks like;

 imageID | votesUP | votesDOWN
 X54vb   |     1   |   0     
 pe9D3   |     0   |   1     

If image X54vb went against image pe9D3 and won, the table would look like the example above.

I want to make one query to increment the value (and create the entry if it doesn't exist).

$voteup is the image id in this example;

REPLACE INTO images SET votesUP = votesUP + 1, imageID = '" . $voteup . "';

This creates the entry if it doesn't exist, but doesn't increment the value if it does. The value is always exactly 1.

I also tried;

UPDATE images SET votesUP = votesUP + 1 WHERE imageID = '" . $voteup . "';

This increments the value, but doesn't create an entry if it doesn't exist. Nothing seems to happen when the entry matching $voteup hasn't been created.

So, basically, I'd like to have just one query that will create an entry (if it doesn't exist), then increment it if it does.

I'd rather not attempt to read from the entry to confirm if it exists or not, then create an entry if it doesn't, then increment it. I'd like an efficient single query if possible.

This is the table I've created;

CREATE TABLE images
(
imageID CHAR(15),
votesUP INT DEFAULT 0,
votesDOWN INT DEFAULT 0,
PRIMARY KEY (imageID)
)"

Also, what exactly is the difference between REPLACE INTO and UPDATE?

Thanks.

Answer 1

What you need is use a INSERT ... ON DUPLICATE KEY UPDATE ... This would be what you need, i think.

INSERT INTO images ( imageID, votesUP ) values
     ( '" . $voteup . "', 1 )
  ON DUPLICATE KEY UPDATE votesUP=votesUP+1;

But this would be just for the increment, the decrement would be the other way around

INSERT INTO images ( imageID, votesDOWN ) values
     ( '" . $voteup . "', 1 )
  ON DUPLICATE KEY UPDATE votesDOWN=votesDOWN+1;

Here is the docs INSERT ... ON DUPLICATE KEY UPDATE Syntax

Answer 2

REPLACE INTO does an INSERT if the record doesn't exist or replaces it when it does exist. You can't use the previous value (x = x + 1), because it replaces it. It's something that's usually only used for import scripts and that kind of ugly stuff. An UPDATE is what you want to use in most cases.

As you stated, you can't UPDATE if the record does not exist yet. So you should first check if it exists and INSERT it if it doesn't exist yet. An alternative is to try an UPDATE, look at the affected rows (which is returned) and then INSERT if 0 affected rows. The first way solution is the more common solution. In both cases you have to do it in 2 steps.

I have a couple of comments on your data model (if you don't mind):

1. Try to user INTs for id's, not CHARs. A CHAR will always allocate a certain number of bytes (in your case 15), which is quite big for a primary key. It influences performance.
2. Your data model doesn't seem to be very logical for a "image vs image" system. It would make more sense to have a link between two images. For example, you'd have an "images" table for the actual images (id, imageUrl, etc), a "competition" table to indicate a competition between 2 images (id, views, etc) and a "score" table with (id, competitionId, imageId, votesUp, votesDown). This way you could query competitions, the images scores and separate image scores from the actual competition (a competition being 1 image vs 1 other image).

Good luck.

edit: As Jorge Campos and bjarkig82 stated, a ON DUPLICATE KEY UPDATE is a good solution to do this in 1 double step

Answer 3

Have you tried : this?

In you case, you could use:

INSERT INTO images
  (imageID, votesUP,)
  VALUES
  ($voteup, 1)
ON DUPLICATE KEY UPDATE
  votesUP = votesUP + 1;