执行for循环或foreach循环以更新用户表 [英] Execute for loop OR foreach loop to update users table

问题描述

实际上，我想运行一个循环，其中应多次更新用户数据.就像我有一个查询来获取记录之前一样:

Actually I wanted to run a loop in which the users data should be updated multiple times. Like i have a query to get the record before:

select id, pkgid, userid from lendinglogs where status = 1
And I get the result like this:
-------------------------------
id     userid      pkgid
1      6           1
2      7           2
2      7           2
-------------------------------
foreach ($selectUsers as $row) {

$userid= $row->userid;

//Now I selects the earning value from the table against the userid and pkgid like:

$selectROI = DB::table('genpackageroi')->select('*')->where('userid', '=', $userid)->where('pkgid', '=', $pkgID)->where('pkgcount', '=', $pkgCount)->orderBy('id')->first();

//Returned data

------------------------------------
id     userid      pkgid     amount
1      6           1         0.54
2      7           2         1.23
2      7           2         0.94
-----------------------------------

$value = $selectROI->amount;
//Update users data
$updateUsers = DB::statement("Update users SET earning = '$value' WHERE id = '$userid'");

}

现在应将用户收入更新为1.23 + 0.94 = 2.17

Now the users earning should be updated with the amount of 1.23 + 0.94 = 2.17

但是，当我执行此代码时，用户数据仅更新一次，只有一个值，如:1.23，但是应该更新为2.17.这意味着循环仅运行一次.虽然我需要2次更新用户数据，但我有两个用户ID相同，但程序包ID不同.我的意思是用户的收入应该用genpackageroi表中返回的值进行更新.

But when I execute this code the user data updates only one time with only one value like: 1.23, however it should be updated with the amount of 2.17. It means the loop runs for one time only. While I need it to update the user data 2 times I there are two users id which are same but the package ids are different. I mean the user's earning should be updated with the values that are returned from the genpackageroi's table.

有人知道该怎么做吗?

-----------------------更新----------------------- --------------

-----------------------Update-------------------------------------

查询:

$selectROI = DB::table('genpackageroi')->select('*')->where('userid', '=', $userid)->where('pkgid', '=', $pkgID)->where('pkgcount', '=', $pkgCount)->orderBy('id')->first();

echo "Userid: ".$selectROI->userid."<BR> Amount: ".$selectROI->amount."<BR>";

结果:

-------------------------
userid      amount
15          1.08
15          1.83
--------------------------

现在我们有两个用户ID，并且两者都相同，这是一次更新用户数据，而应该更新两次，因为我们对同一个用户有两个值.

Now we have two user ids and both are same, it is updating the user data one time while it should update two times because we have two values against the same user.

------------------------------------用户的建议3532758 -------- ----------------

------------------------------------Suggestion By user3532758 ------------------------

查询:

$selectROI = DB::table('genpackageroi')->select(DB::raw('sum(amount) as total_amount'))->where('userid', '=', $userid)->where('pkgid', '=', $pkgID)->where('pkgcount', '=', $pkgCount)->groupBy('userid')->first();

dd($selectROI);

结果是:

userid        amount
15            199.99999989942
15            399.99999918416

这完全是错误的，也是无法预料的结果.

Which is totally wrong and unaspected result.

推荐答案

解决方案2:您的更新语句.添加到数据库中的现有值.

Solution 2: Your update statement. Add to the existing value in the database.

$updateUsers = DB::statement("Update users SET earning = earning + '$value' WHERE id = '$userid'");

解决方案1，您说它返回错误的结果. 扩展我的评论，我认为类似的事情可能对您有用.

Solution 1, which you say returns the wrong results. Extending on my comments, I think something like this could work in your case.

在金额列上求和以获取总金额

Do a SUM on the amount column to get the total amount

$selectROI = DB::table('genpackageroi')->select(DB::raw('sum(amount) as total_amount'))->where('userid', '=', $userid)->where('pkgid', '=', $pkgID)->where('pkgcount', '=', $pkgCount)->first();

现在$selectROI->total_amount应该将两个值加起来.

Now $selectROI->total_amount should have the both values added up.

这篇关于执行for循环或foreach循环以更新用户表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！