Increase and decrease row value by 1 in MySQL

Question

Hi I have a MySQL database table "points" the user can click a button and a point should be removed from their account, the button they pressed has an ID of another user, therefore their account must increase by one.

I have it working in jQuery and checked the varibles/posts in Firebug, and it does send the correct data, such as:

userid= 1  posterid = 4 

I think the problem is with my PHP page:

<?php   include ('../functions.php');  $userid=mysql_real_escape_string($_POST['user_id']); $posterid=mysql_real_escape_string($_POST['poster_id']);  if (loggedin())  { include ('../connection.php'); $query1 = "UPDATE `points` SET `points` = `points` - 1 WHERE `userID` = '$userid'"; $result1=mysql_query($query1);   $query2 = "UPDATE `points` SET `points` = `points` + 1 WHERE `userID` = '$posterid'"; $result2=mysql_query($query2);   if ($result1 && result2) {     echo "Successful";       return 1; } else {      echo mysql_error();     return 0;    } } ?> 

Any ideas? Thanks :)

Answer 1

Two queries to increase/decrease field value are not necessary:

mysql_query("UPDATE table SET field = field + 1 WHERE id = $number"); 

is a perfectly valid query as you can see next:

mysql> describe points; +--------+---------+------+-----+---------+-------+ | Field  | Type    | Null | Key | Default | Extra | +--------+---------+------+-----+---------+-------+ | uid    | int(11) | NO   | PRI | NULL    |       | | points | int(11) | YES  |     | 0       |       | +--------+---------+------+-----+---------+-------+ 2 rows in set (0.05 sec)  mysql> insert into points VALUES (1,0),(2,0); Query OK, 2 rows affected (0.14 sec)  mysql> select * from points; +-----+--------+ | uid | points | +-----+--------+ |   1 |      0 | |   2 |      0 | +-----+--------+ 2 rows in set (0.05 sec)  mysql> update points set points = points+1 where uid = 1; Query OK, 1 row affected (0.27 sec) Rows matched: 1  Changed: 1  Warnings: 0  mysql> select * from points; +-----+--------+ | uid | points | +-----+--------+ |   1 |      1 | |   2 |      0 | +-----+--------+ 2 rows in set (0.00 sec) 

Having that tested, are you sure you get into your if (loggedin()) clause?

I have to agree with KM, would be nice to see output of echo $query1; or echo $query2;