Michael Amaleona Michael Amaleona - 4 months ago 12
SQL Question

How to store multiple row base on the ID in SQL into variable in php

I have 3 tables in phpmyadmin. disease_table, medicine_table, disease_medicine_table.

Case: The disease can have multiple medicines that's why I made the third table named disease_medicine table that linked the disease table and medicine table.

My problem is how to get all the records of the disease.

I'm using codeigniter framework

My desired result is:


  • id - disease_name - medicines

  • 1 - toothache - med1,med2,med3,med4

  • 2 - headache - med4,med8,med2,med5



Or do you have a better idea to fix my problem?

This is my disease table


  • id(int11)

  • disease_name(varchar255)



This my medicine table


  • id(int11)

  • med_name(varchar255)

  • dosage_strength(varchar255)

  • price(decimal65,2)



This my disease_medicine table


  • id(int11)

  • disease_id(int11)

  • medicine_id(int11)

  • dosage(varchar255)

  • quantity(int64)



Im going to display the data in DataTable server side and this is my code
Ajax controller

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);


}

Model

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;
}


Thanks in advance

Answer

Run this query:

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

The result will be:

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

You can then work through the results to compile things as you wish. For instance if you put all results in $rows

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

Your $results will then look like:

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

If you care, you can use rtrim() to remove the trailing commas.

Comments