如何在单个表单元格中分组和显示多个结果集行值? [英] How to group and show multiple resultset row values within a single table cell?

问题描述

<?php
include("functions/functions.php");
$con=mysqli_connect("localhost", "root", "", "db_stvs");
$query="select * from tbl_preset_position where pp_status='Active'";
$query1="select distinct pp_name from tbl_preset_position where pp_status='Active'";
$res=mysqli_query($con, $query);
$res1=mysqli_query($con, $query1);
?>
<button class="btn btn-primary">Back</button>
<button class="btn btn-primary" data-toggle="modal" data-target="#addModal">Add Position Preset</button>

<table class="table-bordered">
    <th>Preset Name</th>
    <th>Positions Available</th>
    <?php
    while ($row1 = mysqli_fetch_assoc($res1)) {
    ?>
        <tr>
            <td>
                <?php echo $row1['pp_name'];?>
            </td>
            <td>
            <?php
            while ($row= mysqli_fetch_assoc($res)) {
                if($row['pp_name']==$row1['pp_name']) {
                    echo $row['pp_position']."<br>";
                }
                ?>
                <?php
            }
            echo "</td></tr>";
    }
    ?>
</table>

我可以显示第一行及其位置，但是当涉及其他行时，则没有数据显示.

I can display the first row with its position, but when it comes to the other rows, no data is showing.

我只想在正确的预设名称上显示特定位置. 例如:-

I just wanted to display the certain position on the right preset name. for example:-

" Sample preset has 3 Positions 
"Sample Preset| President, Vice PResident" 
"Sample Preset 2| Supremo, Ala Supremo"

推荐答案

最明智的做法是，在查询中使用GROUP BY和GROUP_CONCAT()来完全准备结果集.

Most sensibly, you should use GROUP BY and GROUP_CONCAT() in your query to completely prepare your resultset.

类似这样的东西:(未经测试)

Something like this: (untested)

SELECT pp_name, GROUP_CONCAT(pp_position SEPARATOR '<br>') AS pp_position
FROM tbl_preset_position
WHERE pp_status = 'Active'
GROUP BY pp_name
ORDER BY pp_name

然后，您可以正常地将数据回显到表中.

Then you can just echo out the data normally into your table.

否则，您将需要使用复杂的php来确定哪些行应附加数据，哪些行可以独立...

Otherwise, you'll need to use a convoluted bit of php to determine which rows should have appended data and which can stand alone...

现在，我尚未测试此代码，但我相信条件逻辑应该成立.您只需要ORDER BY pp_name，然后在迭代结果集时确定是否要显示该组中的第一个.

Now, I haven't tested this code, but I believe the conditional logic should hold up. You just need to ORDER BY pp_name then check for a new pp_name value as you iterate the resultset to determine if you are displaying the first in the group or not.

我将OO语法用于查询功能，因为它不太冗长.

I am using OO syntax for the query functions because it is less verbose.

<?php
include("functions/functions.php");

echo '<button class="btn btn-primary">Back</button>';
echo '<button class="btn btn-primary" data-toggle="modal" data-target="#addModal">Add Position Preset</button>';

if (!$con = new mysqli("localhost", "root", "", "db_stvs")) {
    echo 'Connect failed: ', $con->connect_error);  // never display errors to the public
} elseif (!$result = $con->query("SELECT pp_name, pp_position FROM tbl_preset_position WHERE pp_status = 'Active' ORDER BY pp_name")) {
    echo 'Syntax Error', $con->error;  // never show the exact error message to the public
} elseif (!$result->num_rows) {
    echo 'No Active Records Found';
} else {
    echo '<table class="table-bordered">';
        echo '<tr><th>Preset Name</th><th>Positions Available</th></tr>';
        $name = null;                                                            // establish a default value that won't be matched
        while ($row = $result->fetch_assoc()) {
            if ($row['pp_name'] !== $name) {
                if ($name !== null) {
                    echo '</td></tr>';
                }
                echo "<tr><td>{$row['pp_name']}</td><td>{$row['pp_position']}";  // write row for pp_name group
            } else {
                echo "<br>{$row['pp_position']}";                                // append all subsequent values in group
            }
            $name = $row['pp_name']; // update temporary variable
        }
        echo '</td></tr>';
    echo '</table>';
}

这篇关于如何在单个表单元格中分组和显示多个结果集行值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！