我在phpmyadmin中有3个表。疾病表,药物表,疾病表。

案例:该疾病可以有多种药物,这就是为什么我制作了第三个表,命名为disease_medicine表,该表链接了疾病表和药物表。

我的问题是如何获取该疾病的所有记录。

我正在使用Codeigniter框架

我想要的结果是:


id-disease_name-药品
1-牙痛-med1,med2,med3,med4
2-头痛-med4,med8,med2,med5


还是您有一个更好的主意来解决我的问题?

这是我的疾病表


id(int11)
disease_name(varchar255)


这是我的药桌


id(int11)
med_name(varchar255)
剂量强度(varchar255)
价格(十进制65,2)


这是我的病


id(int11)
disease_id(int11)
medicine_id(int11)
剂量(varchar255)
数量(int64)


我将在DataTable服务器端显示数据,这是我的代码
Ajax控制器

public function get_diseases_for_table() {
$data = [];

if(($ajax_data = $this->input->get()) && $this->input->is_ajax_request()) {
    extract($ajax_data);
    $data = [
        'draw' => $draw,
        'recordsTotal' => $this->diseases->get_diseases_records_total(),
        'recordsFiltered' => $this->diseases->get_diseases_records_filtered($ajax_data)
    ];

    $data['data'] = $this->diseases->get_diseases($ajax_data);
}
$this->view = FALSE;
echo json_encode($data);


}

模型

public function get_diseases_records_total() {
    $sql = 'SELECT d.id, d.disease_name, m.med_name, d.created_at ';
    $sql .= 'FROM diseases d ';
    $sql .= 'INNER JOIN diseases_medicines dm ON d.id = dm.disease_id ';
    $sql .= 'INNER JOIN medicines m ON dm.medicine_id = m.id ';

    $query = $this->db->query($sql);

    return $query->num_rows();
}

public function get_diseases_records_filtered($data) {
    extract($data);
    $params = [];

    $sql = 'SELECT d.id, d.disease_name, m.med_name, d.created_at ';
    $sql .= 'FROM diseases d ';
    $sql .= 'INNER JOIN diseases_medicines dm ON d.id = dm.disease_id ';
    $sql .= 'INNER JOIN medicines m ON dm.medicine_id = m.id ';
    $sql .= 'WHERE 1 ';

    if(!empty($search['value'])){
        $sql .= 'AND (d.id = ? ';
        $sql .= 'OR d.disease_name LIKE ? ';
        $sql .= 'OR m.med_name LIKE ? ';
        $sql .= 'OR d.created_at LIKE ?) ';
    }

    if(!empty($search['value'])){
        $params[] = $search['value'];
        $params[] = $search['value'];
        $params[] = $search['value'];
        $params[] = $search['value'];
    }

    $query = $this->db->query($sql, $params);

    return $query->num_rows();
}

public function get_diseases($data, $details = false) {
    extract($data);
    $params = [];

    $sql = 'SELECT d.id, d.disease_name, m.med_name, d.created_at ';
    $sql .= 'FROM diseases d ';
    $sql .= 'INNER JOIN diseases_medicines dm ON d.id = dm.disease_id ';
    $sql .= 'INNER JOIN medicines m ON dm.medicine_id = m.id ';
    $sql .= 'WHERE 1 ';

    if(!empty($search['value'])){
        $sql .= 'AND (d.id = ? ';
        $sql .= 'OR d.disease_name LIKE ? ';
        $sql .= 'OR m.med_name LIKE ? ';
        $sql .= 'OR d.created_at LIKE ?) ';
    }

    if(isset($order)){
        $sql .= 'ORDER BY ' . $columns[$order[0]['column']]['data'] . ' ' . strtoupper($order[0]['dir']) . ' ';//$order[0]['column']
    }
    $sql .= 'LIMIT ?, ?';

    if(!empty($search['value'])){
        $params[] = $search['value'];
        $params[] = '%' . $search['value'] . '%';
        $params[] = '%' . $search['value'] . '%';
        $params[] = '%' . $search['value'] . '%';
    }

    $params[] = (int)$start;
    $params[] = (int)$length;


    $query = $this->db->query($sql, $params);

    return ( $query->num_rows() ) ? $query->result() : FALSE;
}


提前致谢

最佳答案

运行此查询:

SELECT d.id, d.disease_name, m.med_name
FROM disease d
JOIN disease_medicine dm ON d.id=dm.disease_id
JOIN medicine m on m.id=dm.medicine_id


结果将是:

1    disA    med1
1    disA    med2
2    disB    med3
2    disB    med4
...


然后,您可以根据需要遍历结果以进行编译。例如,如果您将所有结果放入$rows

$results = [];
foreach($rows as $row){
    if(!isset($results[$row['disease_name']]) $results[$row['disease_name']]='';
    $results[$row['disease_name']] .= ',' $row['med_name'];
}


您的$results将如下所示:

[
    'disA'=>'med1,med2,',
    'disB'=>'med3,med4,',
    ...
]


如果需要,可以使用rtrim()删除结尾的逗号。

09-15 17:39