CodeIgniter实现数据库单值展示及最小差值关联信息查询指导请求

Got it, let's walk through how to build this in CodeIgniter step by step. First, a quick note: since you didn't specify how your two tables are related, I'll assume they share a common key (like an id column) to join them. If your actual join condition is different (e.g., value1 links to value3), just swap that out in the SQL queries below.

1. Model (application/models/Calculation_model.php) #

This model handles the database query to fetch the minimum difference and corresponding name. We use a subquery to first find the smallest (value3 - value2) value, then join back to get the matching name (this works even if multiple rows have the same minimum difference).

<?php
class Calculation_model extends CI_Model {

    public function get_min_diff_with_name() {
        // Subquery to find the absolute minimum difference
        $subquery = $this->db->select('MIN(b2.value3 - a2.value2) as min_val')
                            ->from('table_a a2') // Replace with your first table name
                            ->join('table_b b2', 'a2.id = b2.id') // Update join condition here
                            ->get_compiled_select();

        // Main query to get the name(s) matching the minimum difference
        $query = $this->db->select('a.name, (b.value3 - a.value2) as min_diff')
                          ->from('table_a a') // Replace with your first table name
                          ->join('table_b b', 'a.id = b.id') // Update join condition here
                          ->where("(b.value3 - a.value2) = ($subquery)")
                          ->get();

        return $query->result_array();
    }
}
?>

2. Controller (application/controllers/Home.php) #

The controller loads the model, fetches the data, and passes it to the view.

<?php
class Home extends CI_Controller {

    public function index() {
        // Load the model
        $this->load->model('Calculation_model');
        
        // Fetch the minimum difference data
        $data['min_diff_result'] = $this->Calculation_model->get_min_diff_with_name();
        
        // Load the view with the data
        $this->load->view('min_diff_view', $data);
    }
}
?>

3. View (application/views/min_diff_view.php) #

This view renders a small HTML table to display the results, with basic styling and handling for empty data cases.

<!DOCTYPE html>
<html>
<head>
    <title>Minimum Difference Result</title>
    <style>
        .small-result-table {
            border-collapse: collapse;
            margin: 2rem 0;
            font-family: Arial, sans-serif;
            font-size: 0.9rem;
            min-width: 350px;
            box-shadow: 0 2px 5px rgba(0,0,0,0.1);
        }
        .small-result-table th, .small-result-table td {
            border: 1px solid #ddd;
            padding: 0.75rem;
            text-align: left;
        }
        .small-result-table th {
            background-color: #f8f9fa;
            font-weight: bold;
        }
        .no-data {
            text-align: center;
            color: #666;
        }
    </style>
</head>
<body>
    <h2>Minimum (value3 - value2) & Corresponding Name</h2>
    <table class="small-result-table">
        <thead>
            <tr>
                <th>Name</th>
                <th>Minimum Difference</th>
            </tr>
        </thead>
        <tbody>
            <?php if (!empty($min_diff_result)): ?>
                <?php foreach ($min_diff_result as $row): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($row['name']); ?></td>
                        <td><?php echo htmlspecialchars($row['min_diff']); ?></td>
                    </tr>
                <?php endforeach; ?>
            <?php else: ?>
                <tr>
                    <td colspan="2" class="no-data">No matching records found.</td>
                </tr>
            <?php endif; ?>
        </tbody>
    </table>
</body>
</html>

Key Notes to Adjust for Your Setup #

• Replace table_a and table_b with your actual table names.
• Update the JOIN condition (a2.id = b2.id) to match how your tables are related (e.g., if value1 in the first table links to value3 in the second, use a2.value1 = b2.value3).
• If you're using CodeIgniter 4, adjust the model to extend CodeIgniter\Model and use namespaces (the core query logic stays the same).

内容的提问来源于stack exchange，提问作者dilip kumar .R aradhya