我在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()
删除结尾的逗号。