在MySQL中将行值增加和减少1

一尘不染

在MySQL中将行值增加和减少1

sql

嗨，我有一个MySQL数据库表“点”，用户可以单击一个按钮，并且应该从他们的帐户中删除一个点，他们按下的按钮具有另一个用户的ID，因此他们的帐户必须增加一个。

我有它在jQuery中工作，并检查了Firebug中的变量/帖子，它确实发送了正确的数据，例如：

userid= 1 
posterid = 4

我认为问题出在我的PHP页面上：

<?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;   
}
}
?>

有任何想法吗？谢谢：）

阅读 221

2021-03-17

共1个答案

一尘不染

不需要两个查询来增加/减少字段值：

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

如下面所示，这是一个完全有效的查询：

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)

经过测试后，您确定会加入if (loggedin())条款吗？

我必须同意KM，很高兴看到echo $query1;或的输出echo $query2;

2021-03-17