php - 如何在 php 中对具有相似值的数据进行分组？

Question

我在数据库中有一个名为“交易”的表。我想列出此表中的值。结果应以 HTML 表格格式显示。结果表中的行应按金额分组。“交易”表中有一个名为“金额”的列。

这是我的代码:

$s1 = DB::table('transactions')
        ->select(DB::raw("GROUP_CONCAT(selected_seats SEPARATOR '') as selected_seats"),'userid','amount','show_id')
        ->where("show_id","=",$s)  
        ->groupBy('userid')
        ->groupBy('amount')
        ->orderBy('userid','ASC')
        ->orderBy('amount', 'DESC')
        ->get();

我正在通过循环从 $s1 变量中检索值。

输出如下:

我想要这样的结果:

请点击上面给出的链接。我是这个论坛的新手，所以不允许我在问题中添加图片。请帮忙。

完整代码如下:

  if($s1 != null)

{

echo "<div class='panel-body'>";
    echo "<table class='table table-responsive'>"; $c = 1;
    echo "<th>Drama Title</th>";
    echo "<th>Show Date</th>";
    echo "<th>Seat booked</th>";
    echo "<th>Amount</th>";
    echo "<th>User</th>";


    for($i=0;$i<count($s1);$i++)
    {



        echo "<tr><td>".$shows1[0]->show_title."</td>";
        echo "<td>".$shows[0]->show_date."</td>";
        echo "<td>";
        echo $s1[$i]->selected_seats; 
        echo "</td>";
        /*echo $s1[$i]->userid."<br/>";
        echo $s1[$i]->amount."<br/>";*/

        $transactions = DB::table('transactions')->where('userid',$s1[$i]->userid)->where('show_id',$s1[$i]->show_id)->get();
        //var_dump($transactions);
        $total_amount = 0;
        //echo "<pre>Users<br/>"; var_dump($transactions);

        foreach ($transactions as $transaction)
        {
            //echo "userid&nbsp;".$s1[$i]->userid."&nbsp;"."show id:&nbsp;".$transaction->show_id."&nbsp;&nbsp;"."<br/>";
           // echo "amount:&nbsp;".$transaction->amount."&nbsp;";
            $amount = $transaction->amount;
            $total_amount = $total_amount + $amount; //echo $amount."<br/>";
            $c = $c+1;
            //echo "no. of seats:".$c;
            $users = DB::table('users')->where('id',$s1[$i]->userid)->get();

        } 
        echo "<td>".$total_amount."</td>";

        //echo $s1[$i]->userid."<br/>";
        //echo "<td>".$users[0]->name."</td>";
        //echo "<pre>"; var_dump($users);
        echo "</td>";
        if(isset($users[0]))
        {
            //echo "values are set"; 
            echo "<td>".$users[0]->name."</td></tr>";
        }
        else
        {
            //echo "null value"; 
            continue;
        }

    }   




    /*echo $shows[0]->show_date."<br/>";
    echo $shows[0]->show_title;*/
    //echo "<pre>"; var_dump($s1);
    //echo "<td>".$users[0]->name."</td>";
    //echo "</tr>";
    echo "</table>";
    echo "</div>";
?>

}别的{ echo "没有找到记录";

}

Answer 1

我认为最简单的方法是在打印数据或准备数据发送到 View 的循环中计算数量。由于您的数据按 user_id 排序，因此当 user_id 发生变化时，您始终可以在循环中更改 amount 变量。您可以使用 count(explode($seat_variable)) 在单次循环运行中获取座位数。查看下面的示例代码。

$num_seat = 0;金额 = 0； $running_user_id = -1;
foreach ($row as $entry) {
...
if ($running_user_id != $entry['user_id']) {//检查是否是同一个用户
$running_user_id = $entry['user_id'];
$num_seat = 0;金额 = 0；//为单独的用户重置变量值。
}
$num_seat += count(explode($entry['selected_seats']));
$金额+= $entry[‘金额’]；
...
}

而且我假设您在查询中缺少诸如电子邮件之类的数据。

提问者添加他的代码后的代码更新。

if($s1 != null) {
    echo "<div class='panel-body'>";
    echo "<table class='table table-responsive'>"; $c = 1;
    echo "<tr>";
    echo "<th>Drama Title</th>";
    echo "<th>Show Date</th>";
    echo "<th>Seat booked</th>";
    echo "<th>Amount</th>";
    // echo "<th>User</th>";
    echo "</tr>";

$category = ""; $num_seats = 0;
for ($i=0; $i<count($s1); $i++) {
    if ($category != $amount) { 
        if ($i > 0) { // print totals 
            echo "<tr>
            <td colspan='3' align='right'>Total</td>
            <td>".$num_seats."</td>
            <td>".($num_seats * $amount)."</td>
        </tr>";

            echo "<tr><td colspan='5'>&nbsp;</td></tr>"; // empty row after totals printed
            $num_seats = 0; //resetting number of seats per category
        }
        echo "<tr><td colspan='5'><h2>Category : ".$amount."</h2></td></tr>"; // printing category line in the given table
        $category = $amount; // this logic is to prevent category line printing for each row
    }
    $transactions = DB::table('transactions')->where('userid',$s1[$i]->userid)->where('show_id',$s1[$i]->show_id)->get();
    echo "<tr>";

    $users = DB::table('users')->where('id', $s1[$i]->userid)->get();
    // Check below values are correct or not
    echo "<td>".$users[0]->name."</td>";
    echo "<td>".$s1[$i]->userid."</td>";
    echo "<td>".$s1[$i]->email."</td>"; // get user's email property here
    echo "<td>".$s1[$i]->selected_seats."</td>";

    $num_seats += count(explode(',', $s1[$i]->selected_seats));
    echo "<td>&nbsp;</td></tr>"; // empty colomn for amount and the row end
}
    echo "</table>";
    echo "</div>";
}