MYSQL需要帮助使用php从每个“用户”提取数据

Here's what I have. This code grabs the data from all of the users and subtracts 1 from user_days then updates every user's user_days row.

这就是我。该代码获取所有用户的数据，并从user_days中减去1，然后更新每个用户的user_days行。

$result = mysqli_query($con,"SELECT * FROM users");

while($row = mysqli_fetch_array($result))
  {
  $minusone = $row['user_days']-1;
  mysqli_query($con,"UPDATE users SET user_days=$minusone");
  echo "<br />";
  echo $row['user_days'];
  }

The problem I'm having is this: Instead of subtracting 1 from each user and updating each users field, it's updating each user's field with the value from the first user.

我遇到的问题是:它不是从每个用户中减去1并更新每个用户字段，而是用第一个用户的值更新每个用户字段。

example: before updating user 1 has 30 days user 2 has 60 days

示例:在更新用户1之前，用户2有30天

after updating user 1 has 29 days user 2 has 29 days (instead of 59 days)

更新后用户1有29天用户2有29天(而不是59天)

Any help is appreciated and I hope this question is easy to understand.

感谢您的帮助，我希望这个问题容易理解。

Just to clarify, I DO want to update every field. I just don't want the updates to be duplicated from the first result.

澄清一下，我确实想更新每个字段。我只是不想从第一个结果中复制更新。

Thanks for all of the answers, this has given me a lot of help.

谢谢所有的答案，这给了我很多帮助。

7 个解决方案

#1

Why don't you just run UPDATE users SET user_days = user_days-1 WHERE id=XXXXX? And then select the whole thing?

为什么不运行更新用户设置user_days = user_days-1，其中id=XXXXX?然后选择整个东西?

#2

When you update your record, you need to specify the user id for the record of interest, otherwise you current query updates all the rows in your table.

更新记录时，需要为相关记录指定用户id，否则当前查询将更新表中的所有行。

#3

You should point which record to UPDATE

您应该指出要更新哪个记录

mysqli_query($con,"UPDATE users SET user_days=$minusone WHERE id=XXXXX");

#4

The problem is with the UPDATE statement. Without a WHERE clause it will apply the SET clause to every row in the database. Assuming you have a unique id column named id in the users table, you could modify your code like this:

问题在于UPDATE语句。没有WHERE子句，它将对数据库中的每一行应用SET子句。假设您在users表中有一个名为id的唯一id列，您可以这样修改代码:

while($row = mysqli_fetch_array($result))
{
  $minusone = $row['user_days']-1;
  $user_id = $row['id'];
  mysqli_query($con,"UPDATE users SET user_days=$minusone WHERE id=$user_id");
  echo "<br />";
  echo $row['user_days'];
}

#5

Use the following Query:

使用以下查询:

mysqli_query($con,"UPDATE users SET user_days=$minusone WHERE user_ID = '".$row['user_ID']."'");

#6

You're updating all the users with the same $minusone value. You need a WHERE clause in your update statement, like this:

您正在用相同的$minusone值更新所有用户。在更新语句中需要一个WHERE子句，如下所示:

$result = mysqli_query($con,"SELECT * FROM users");

while($row = mysqli_fetch_array($result))
  {
  $minusone = $row['user_days']-1;
  $id_user = $row['id_user'];
  mysqli_query($con,"UPDATE users SET user_days=$minusone WHERE id_user = $id_user");
  echo "<br />";
  echo $row['user_days'];
  }

Another way of doing what you want would be:

做你想做的事的另一种方式是:

...
$result = mysqli_query($con,"UPDATE users SET user_days = user_days - 1");
...

This assuming you want to substracts 1 to all user_days.

假设您想要将1替换为所有user_days。

#7

As already pointed out by other answers, the best way to do this is to replace your entire code block with one line.

正如其他答案已经指出的，最好的方法是用一行代码替换整个代码块。

mysqli_query($con, "UPDATE users SET user_days=user_days-1");

Then you can SELECT and display the information as needed.

然后您可以根据需要选择和显示信息。

Without knowing exactly what you are using user_days for, I'm thinking there may be a better approach to what you are trying to do. I am assuming this is some kind of subscription service, and this code will be run once per day to decrement the number of days to allow them to access the service.

在不知道您使用user_days是为了什么情况下，我认为可能有更好的方法来实现您的目标。我假设这是某种订阅服务，该代码将每天运行一次，以减少访问服务的天数。

A better approach would be to have a subscriptionExpires field in your database, which would hold a datetime value. Using your approach, if the job that runs this fails, every user will get an extra day. What if a web spider or a user finds your script, your users accounts will expire early. If you use an actual date for when the account expires, there's no guessing if the current value is correct.

更好的方法是在数据库中有一个subscriptionExpires字段，该字段将保存一个datetime值。使用您的方法，如果运行此操作的作业失败，每个用户将获得额外的一天。如果web spider或用户发现了您的脚本，那么您的用户帐户将提前过期。如果您使用一个实际的日期，当帐户到期时，没有猜测是否当前值是正确的。

#1