在CodeIgniter中关联三张表并展示，求疾病多症状关联实现方案

Got it, let's tackle this CodeIgniter table join problem for your disease-symptom many-to-many relationship. Since you already have the three tables set up (disease, symptoms, relation pivot table), I'll walk you through the model, controller, and view code to fetch and display the data properly.

First, we'll add two methods to your existing disease model—one for a simple concatenated symptom list, and another if you need full control over individual symptom data.

<?php
class Disease_model extends CI_Model {

    // Method 1: Get diseases with symptoms as a comma-separated list (great for quick displays)
    public function get_diseases_with_symptoms_simple() {
        $this->db->select(
            'disease.id_disease, 
             disease.name as disease_name, 
             disease.details as disease_details, 
             GROUP_CONCAT(symptoms.name SEPARATOR ", ") as symptoms_list'
        );
        $this->db->from('disease');
        // Left join ensures diseases without symptoms still show up
        $this->db->join('relation', 'disease.id_disease = relation.id_disease', 'left');
        $this->db->join('symptoms', 'relation.id_symptoms = symptoms.id_symptoms', 'left');
        $this->db->group_by('disease.id_disease'); // Group by disease to combine symptoms
        
        return $this->db->get()->result();
    }

    // Method 2: Get diseases with full symptom objects (use if you need to display individual symptom details)
    public function get_diseases_with_symptoms_detailed() {
        $query = $this->db->select('disease.*, symptoms.*')
                          ->from('disease')
                          ->join('relation', 'disease.id_disease = relation.id_disease', 'left')
                          ->join('symptoms', 'relation.id_symptoms = symptoms.id_symptoms', 'left')
                          ->get();

        $formatted_data = [];
        foreach ($query->result() as $row) {
            $disease_id = $row->id_disease;
            
            // Initialize disease entry if it doesn't exist
            if (!isset($formatted_data[$disease_id])) {
                $formatted_data[$disease_id] = [
                    'id_disease' => $row->id_disease,
                    'name' => $row->name,
                    'details' => $row->details,
                    'symptoms' => []
                ];
            }

            // Add symptom only if it exists (skip null results from left join)
            if (!empty($row->id_symptoms)) {
                $formatted_data[$disease_id]['symptoms'][] = [
                    'id_symptoms' => $row->id_symptoms,
                    'name' => $row->name,
                    'details' => $row->details
                ];
            }
        }

        // Convert associative array to indexed array for easier view looping
        return array_values($formatted_data);
    }
}
?>

Next, create or update a controller to load the model, fetch the data, and pass it to your view:

<?php
class Disease extends CI_Controller {

    public function index() {
        $this->load->model('Disease_model');
        
        // Pick one method based on your display needs
        $data['diseases'] = $this->Disease_model->get_diseases_with_symptoms_simple();
        // OR $data['diseases'] = $this->Disease_model->get_diseases_with_symptoms_detailed();
        
        $this->load->view('disease_list', $data);
    }
}
?>

Choose the view that matches the model method you used.

View for Simple Concatenated Symptoms #

<h1>Diseases & Associated Symptoms</h1>
<div class="disease-grid">
    <?php foreach ($diseases as $disease): ?>
        <div class="disease-card">
            <h2><?= htmlspecialchars($disease->disease_name) ?></h2>
            <p><strong>Details:</strong> <?= htmlspecialchars($disease->disease_details) ?></p>
            <p><strong>Symptoms:</strong> <?= htmlspecialchars($disease->symptoms_list ?? 'No symptoms recorded') ?></p>
        </div>
    <?php endforeach; ?>
</div>

View for Detailed Symptom Entries #

<h1>Diseases & Associated Symptoms</h1>
<div class="disease-grid">
    <?php foreach ($diseases as $disease): ?>
        <div class="disease-card">
            <h2><?= htmlspecialchars($disease['name']) ?></h2>
            <p><strong>Details:</strong> <?= htmlspecialchars($disease['details']) ?></p>
            
            <h3>Symptoms:</h3>
            <?php if (!empty($disease['symptoms'])): ?>
                <ul>
                    <?php foreach ($disease['symptoms'] as $symptom): ?>
                        <li>
                            <strong><?= htmlspecialchars($symptom['name']) ?></strong>: 
                            <?= htmlspecialchars($symptom['details']) ?>
                        </li>
                    <?php endforeach; ?>
                </ul>
            <?php else: ?>
                <p>No symptoms recorded for this disease.</p>
            <?php endif; ?>
        </div>
    <?php endforeach; ?>
</div>

Quick Notes: #

• Always use htmlspecialchars() to prevent XSS attacks when outputting user-provided data.
• The left join ensures diseases without any linked symptoms are still displayed (instead of being filtered out).
• The first model method is lighter for basic displays, while the second gives you flexibility to manipulate or display individual symptom data.

内容的提问来源于stack exchange，提问作者Derny