Codeigniter:查询数据库返回多个结果

Question

我正试图查询在两个表之间建立联接的数据库。

特别是，我有一个讲解员列表，每个讲解员都有一个议程，里面有数据，时间中间穿插着一个小时。例如在表中

议程

id | docent_id | date       | start | end   |
1  |      1    | 2020-05-29 | 09:00 | 10:00 |
2  |      1    | 2020-05-29 | 10:00 | 11:00 |
3  |      2    | 2020-05-29 | 09:00 | 10:00 |

所以我的控制器里有：

public function docent_get()
{
$this->load->model('Docent_model');
$result = $this->docent_model->getDocent();
if (is_null($result)) {
$this->response(null, 404);
return;
}
$this->response($result, 200);
}

在我的模型中：

public function getDocent()
{
$roles = 'Docent';
$query = $this->db->select('Persons.id, Persons.name, Persons.surname, Agenda.date, Agenda.start_at, Agenda.end_at')
->from('Persons')
->join('Agenda', 'Persons.id = Agenda.docent_id', 'INNER')
->where('Agenda.free', 'true')
->where('roles', $roles)
->get();
return $query->result();

现在这个查询可以工作，但例如，这个查询的结果是一个三个数组，其中具有相同id但不同小时的文档重复两次。你认为有可能改变答案吗那么，如果老师有多个日期，那么要得到一个两个的数组，里面有另一个数组吗？

因为目前我有：

(3) [{…}, {…}, {…}]
0: {id: "1", name: "name1", surname: "surname1", date: "2020-05-29", start: "09:00:00", end: "10:00:00"}
1: {id: "1", name: "name1", surname: "surname1", date: "2020-05-29", start: "10:00:00", end: "11:00:00"}
2: {id: "2", name: "name2", surname: "surname2", date: "2020-05-29", start: "09:00:00", end: "10:00:00"}
length: 3
__proto__: Array(0)

Answer 1

您可以使用以下函数来获得预期的答案：

public function getDocent()
{
$roles = 'Docent';
$arr_return = array();
$query = $this->db->select('Persons.id, Persons.name, Persons.surname, Agenda.date, Agenda.start, Agenda.end')
->from('Persons')
->join('Agenda', 'Persons.id = Agenda.docent_id', 'INNER')
->where('Agenda.free', 'true')
->where('roles', $roles)    
->get();
$arr_result = $query->result_array();
foreach($arr_result as $key=>$result)
{
if(!isset($arr_return[$result['id']]))
$arr_return[$result['id']] = array('name'=> $result['name'], 'surname' => $result['surname']);
$arr_return[$result['id']]['docent'][] = array('start'=> $result['start'], 'end'=> $result['end']);
}
//print_r($arr_return);
return $arr_return;     
}

输出将类似于：

Array
(
[1] => Array
(
[name] => Sanjay
[surname] => Surve
[docent] => Array
(
[0] => Array
(
[start] => 09:00
[end] => 10:00
)
[1] => Array
(
[start] => 10:00
[end] => 11:00
)
)
)
[2] => Array
(
[name] => John
[surname] => Doe
[docent] => Array
(
[0] => Array
(
[start] => 09:00
[end] => 10:00
)
)
)
)

注意：您必须根据数据库修改字段名(比如start应该是start_at(

希望这对你有用。

Answer 2

您可以删除重复的元素，如下

$done = array(); //checked ids
$output = array(); //final result
$n = 0;
foreach($result as $key){
$key->start = array($key->start);
$key->end = array($key->end);
if(!in_array($key->id, $done){
foreach(array_slice($n+1,$result) as $val){ //getting next part of the array
if($val->id == $key->id){
$key->start[] = $val->start;
$key->end[] = $val->end;
}
}
$output[] = $key;
}
$n++;
}
print_r($output)

输出应为

(2) [{…}, {…}]
0: {id: "1", name: "name1", surname: "surname1", date: "2020-05-29", start: ["09:00:00", "10:00:00"], end: ["10:00:00","11:00:00"]}
1: {id: "2", name: "name2", surname: "surname2", date: "2020-05-29", start: ["09:00:00"], end: ["10:00:00"]}
length: 2
__proto__: Array(0)