php 在 MySQL 中增加和减少行值 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2834866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Increase and decrease row value by 1 in MySQL
提问by Elliott
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.
嗨，我有一个 MySQL 数据库表“点”，用户可以单击一个按钮，一个点应该从他们的帐户中删除，他们按下的按钮有另一个用户的 ID，因此他们的帐户必须增加一个。
I have it working in jQuery and checked the varibles/posts in Firebug, and it does send the correct data, such as:
我让它在 jQuery 中工作并检查了 Firebug 中的变量/帖子，它确实发送了正确的数据，例如：
userid= 1
posterid = 4
I think the problem is with my PHP page:
我认为问题出在我的 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;
}
}
?>
Any ideas? Thanks :)
有任何想法吗？谢谢 ：）
回答by acm
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?
经过测试，你确定你进入了你的if (loggedin())条款吗？
I have to agree with KM, would be nice to see output of echo $query1;or echo $query2;
我必须同意KM，很高兴看到echo $query1;或echo $query2;
回答by Raj Kumar Das
Here is the example query, tested by me and it is working 100%
这是我测试过的示例查询，它 100% 工作
$query="UPDATE table_name SET `hit_count`=(`hit_count`+1) WHERE `id` = '1'";
回答by Zeeshan Akhter
update table_name set col_name=col_name+1 where sqId = 12
But if your col_nameby default value is null or empty it never works, so make sure that col_namedefault value is 0or any integer value.
但是，如果您col_name的默认值为 null 或为空，则它永远不会起作用，因此请确保col_name默认值为0或任何整数值。
回答by KM.
try adding in something to print out your actual SQL command, before the if ($result1 && result2):
尝试在以下内容之前添加一些内容以打印出您的实际 SQL 命令if ($result1 && result2)：
ECHO '$query1='.$query1.'<br>';
ECHO '$query2='.$query2.'<br>';
this will help to see what it is sending to the database
这将有助于查看它发送到数据库的内容
回答by indieboy
update 'tablename' set 'columnname1'='columnname1' + 1 where 'columnname2'='value';
eg: update students set englishmarks=englishmarks + 1 where name='Rahul';
例如： update students set englishmarks=englishmarks + 1 where name='Rahul';
Edit:(Explanation) "UPDATE" keyword is used to update a vaule in the table. Here I am updating a value in the table "students". "SET" keyword updating the english marks by 1(just like in C language, how we increase the value of an integer, i=i+1) and condidtion is given where name is "Rahul".
编辑：（解释）“UPDATE”关键字用于更新表中的值。在这里，我正在更新“students”表中的一个值。“SET”关键字将英文标记更新 1（就像在 C 语言中，我们如何增加整数的值，i=i+1）并给出条件，其中名称为“Rahul”。
So englishmarks of Rahul are incremented by 1
所以 Rahul 的英文标记加 1
回答by Pham Quang
In laravel Migration do:
在 Laravel 迁移中执行：
\DB::statement('SELECT @pos:=0;');
\DB::statement('UPDATE users SET company_id = ( SELECT @pos := @pos + 1 ) WHERE `id` = '1';');
If want to change all records, remove the WHERE id= '1'
如果要更改所有记录，请删除 WHERE id= '1'
It will insert number increment to your records like:
它将在您的记录中插入数字增量，例如：
+-----+------------+
| id | company_id |
+-----+------------+
| 1 | 1 |
| 12 | 2 |
| 23 | 3 |
+-----+------------+
