如何在PHP+MySQL中获取每行最大值并返回列名，赋值给dominant列

Hey there! Let's fix your issue where you need to grab the maximum value from o_result, c_result, e_result, a_result, n_result for each row in your test_report table, then display both the max value and its corresponding column name in the dominant column. I've got two straightforward approaches for you to pick from:

Approach 1: Handle It Directly in MySQL (More Efficient) #

This method does all the heavy lifting in your SQL query, which is better for performance especially if you have a large dataset. We'll use MySQL's GREATEST() function to get the max value, and a CASE statement to map that value back to its column name.

Updated SQL Query #

SELECT 
    test_id,
    date_of_submission,
    status,
    o_result,
    c_result,
    e_result,
    a_result,
    n_result,
    -- Get the maximum value across the 5 columns
    GREATEST(o_result, c_result, e_result, a_result, n_result) AS max_dominant_value,
    -- Match the max value to its column name (use friendly labels if you want)
    CASE 
        WHEN o_result = GREATEST(o_result, c_result, e_result, a_result, n_result) THEN 'Openness'
        WHEN c_result = GREATEST(o_result, c_result, e_result, a_result, n_result) THEN 'Conscientiousness'
        WHEN e_result = GREATEST(o_result, c_result, e_result, a_result, n_result) THEN 'Extraversion'
        WHEN a_result = GREATEST(o_result, c_result, e_result, a_result, n_result) THEN 'Agreeableness'
        WHEN n_result = GREATEST(o_result, c_result, e_result, a_result, n_result) THEN 'Neuroticism'
    END AS dominant
FROM test_report 
ORDER BY test_id ASC

Updated PHP Code #

Now adjust your PHP to use the new query results. Here's the full modified table body:

<tbody>
<?php 
$result = mysqli_query($connection, "
    SELECT 
        test_id,
        date_of_submission,
        status,
        o_result,
        c_result,
        e_result,
        a_result,
        n_result,
        GREATEST(o_result, c_result, e_result, a_result, n_result) AS max_dominant_value,
        CASE 
            WHEN o_result = GREATEST(o_result, c_result, e_result, a_result, n_result) THEN 'Openness'
            WHEN c_result = GREATEST(o_result, c_result, e_result, a_result, n_result) THEN 'Conscientiousness'
            WHEN e_result = GREATEST(o_result, c_result, e_result, a_result, n_result) THEN 'Extraversion'
            WHEN a_result = GREATEST(o_result, c_result, e_result, a_result, n_result) THEN 'Agreeableness'
            WHEN n_result = GREATEST(o_result, c_result, e_result, a_result, n_result) THEN 'Neuroticism'
        END AS dominant
    FROM test_report 
    ORDER BY test_id ASC
") or die(mysqli_error($connection));

while ($row = mysqli_fetch_array($result)) {
    $id = $row['test_id'];
?>
<tr>
    <td><?php echo $row['test_id']; ?></td>
    <td><?php echo $row['date_of_submission']; ?></td>
    <td><?php echo $row['status']; ?></td>
    <td><?php echo $row['o_result']; ?></td>
    <td><?php echo $row['c_result']; ?></td>
    <td><?php echo $row['e_result']; ?></td>
    <td><?php echo $row['a_result']; ?></td>
    <td><?php echo $row['n_result']; ?></td>
    <!-- Show both the dominant trait name and its value -->
    <td><?php echo $row['dominant'] . ' (' . $row['max_dominant_value'] . ')'; ?></td>
    <td>
        <button class="btn search"><a href="view_participant.php?id=<?php echo $id; ?>" class="icon-search"></a></button>
        <button class="btn edit"><a href="view_participant.php?id=<?php echo $id; ?>" class="icon-edit"></a></button>
        <button class="btn btn-danger"><a href="crud/delete_part.php?id=<?php echo $id; ?>" class="icon-remove"></a></button>
    </td>
</tr>
<?php } ?>
</tbody>

Approach 2: Process in PHP (More Flexible) #

If you prefer to keep your SQL simple and handle the logic in PHP, this approach lets you easily handle edge cases like multiple columns having the same max value (e.g., o_result and c_result both equal to 90).

Modified PHP Code #

<tbody>
<?php 
$result = mysqli_query($connection, "select * from test_report order by test_id ASC") or die(mysqli_error($connection));
while ($row = mysqli_fetch_array($result)) {
    $id = $row['test_id'];
    
    // Create an array mapping column names to their values (use friendly labels here)
    $trait_results = [
        'Openness' => $row['o_result'],
        'Conscientiousness' => $row['c_result'],
        'Extraversion' => $row['e_result'],
        'Agreeableness' => $row['a_result'],
        'Neuroticism' => $row['n_result']
    ];
    
    // Get the maximum value from the array
    $max_value = max($trait_results);
    // Get all trait names that have this max value (handles ties automatically)
    $dominant_traits = array_keys($trait_results, $max_value);
    // Format the display: show all tied traits if there are multiple
    $dominant_display = implode(', ', $dominant_traits) . ' (' . $max_value . ')';
?>
<tr>
    <td><?php echo $row['test_id']; ?></td>
    <td><?php echo $row['date_of_submission']; ?></td>
    <td><?php echo $row['status']; ?></td>
    <td><?php echo $row['o_result']; ?></td>
    <td><?php echo $row['c_result']; ?></td>
    <td><?php echo $row['e_result']; ?></td>
    <td><?php echo $row['a_result']; ?></td>
    <td><?php echo $row['n_result']; ?></td>
    <td><?php echo $dominant_display; ?></td>
    <td>
        <button class="btn search"><a href="view_participant.php?id=<?php echo $id; ?>" class="icon-search"></a></button>
        <button class="btn edit"><a href="view_participant.php?id=<?php echo $id; ?>" class="icon-edit"></a></button>
        <button class="btn btn-danger"><a href="crud/delete_part.php?id=<?php echo $id; ?>" class="icon-remove"></a></button>
    </td>
</tr>
<?php } ?>
</tbody>

Quick Notes #

• MySQL Method: Faster for large datasets since the database optimizes the calculation. Best if you don't need to handle ties (or can add more complex CASE logic for ties).
• PHP Method: More flexible for custom formatting, tie handling, or any extra logic you might want to add later.

内容的提问来源于stack exchange，提问作者Solomon Berhanu Sole