saleena saleena - 1 month ago 6
MySQL Question

I want some correction on my result using codeigniter

now the output is getting like this. i marked what i needed

some rows have same date. i want to make that as one. for eg: 2016-11-02,afternoon English 1 and for noon English 11.now getting as two entries.please help me to make it as one row. I am sharing my model function here

Model

public function select_data() {
$this->db->distinct();
$this->db->select('extt_std as std');
$this->db->from('exam_time_table');
$query = $this->db->get();
if ($query->num_rows() > 0) {
foreach ($query->result() as $row) {
$row->standard = $row->std;
$data[] = $row;

$this->db->select('*');
$this->db->from('exam_time_table');
$this->db->where('exam_time_table.extt_std', $row->std);
$query = $this->db->get();
$get = $query->result();

foreach ($get as $row) {
if ($row->extt_sess == 'FN') {
$row->fornoon = $row->extt_sub;
$row->afternoon = '';
}
if ($row->extt_sess == 'AN') {
$row->fornoon = '';
$row->afternoon = $row->extt_sub;
}
$data[]=$row;
}
}
return $data;
}
return false;
}


my table

CREATE TABLE `exam_time_table` (
`extt_id` int(10) NOT NULL,
`extt_date` date NOT NULL,
`extt_exam` varchar(20) NOT NULL,
`extt_std` varchar(10) NOT NULL,
`extt_sub` varchar(15) NOT NULL,
`extt_sess` varchar(10) NOT NULL,
`extt_year` varchar(10) NOT NULL
)

INSERT INTO `exam_time_table` (`extt_id`, `extt_date`, `extt_exam`, `extt_std`, `extt_sub`, `extt_sess`, `extt_year`) VALUES
(1, '2016-11-01', 'Half Yearly', 'I-STD', 'Tamil', 'FN', '16-17'),
(2, '2016-11-01', 'Half Yearly', 'II-STD', 'Tamil', 'FN', '16-17'),
(3, '2016-11-01', 'Half Yearly', 'III-STD', 'Tamil', 'FN', '16-17'),
(4, '2016-11-01', 'Half Yearly', 'IV-STD', 'Tamil', 'FN', '16-17'),
(5, '2016-11-01', 'Half Yearly', 'XI-STD', 'Tamil-I', 'FN', '16-17'),
(6, '2016-11-01', 'Half Yearly', 'X-STD', 'Tamil-I', 'FN', '16-17'),
(7, '2016-11-01', 'Half Yearly', 'V-STD', 'Tamil', 'FN', '16-17'),
(8, '2016-11-01', 'Half Yearly', 'VII-STD', 'Tamil', 'FN', '16-17'),
(9, '2016-11-01', 'Half Yearly', 'VII-STD', 'Tamil', 'FN', '16-17'),
(10, '2016-11-01', 'Half Yearly', 'VIII-STD', 'Tamil', 'FN', '16-17'),
(11, '2016-11-01', 'Half Yearly', 'IX-STD', 'Tamil-II', 'AN', '16-17'),
(12, '2016-11-01', 'Half Yearly', 'X-STD', 'Tamil-II', 'AN', '16-17'),
(13, '2016-11-02', 'Half Yearly', 'I-STD', 'English', 'AN', '16-17'),
(14, '2016-11-02', 'Half Yearly', 'II-STD', 'English', 'FN', '16-17'),
(15, '2016-11-02', 'Half Yearly', 'IX-STD', 'English-I', 'FN', '16-17'),
(16, '2016-11-02', 'Half Yearly', 'IX-STD', 'English-II', 'AN', '16-17'),
(17, '2016-11-02', 'Half Yearly', 'X-STD', 'English-I', 'FN', '16-17'),
(18, '2016-11-02', 'Half Yearly', 'X-STD', 'English-II', 'AN', '16-17'),
(19, '2016-11-02', 'Half Yearly', 'III-STD', 'English', 'FN', '16-17');

Answer

It is bit complex but solve your issue. If anyone else can give better then this solution. I'll be appreciated.

public function select_data() {
$this->db->distinct();
$this->db->select('extt_std as std');
$this->db->from('exam_time_table');
$query = $this->db->get();
if ($query->num_rows() > 0) {
    foreach ($query->result() as $row) {
        $row->standard = $row->std;
        $data[] = $row;

        $this->db->select('*');
        $this->db->from('exam_time_table');
        $this->db->where('exam_time_table.extt_std', $row->std);
        $this->db->where('exam_time_table.extt_sess', 'AN');
        $subquery = $this->db->get_compiled_select();

        $this->db->select('*');
        $this->db->from('exam_time_table');
        $this->db->where('exam_time_table.extt_std', $row->std);
        $this->db->where('exam_time_table.extt_sess', 'FN');
        $subquery2 = $this->db->get_compiled_select();

        $this->db->select('a.*,b.*');
        $this->db->from('('.$subquery.') a');
        $this->db->join('('.$subquery2.') b','a.extt_std=b.extt_std','');
        $get = $this->db->result();

        foreach ($get as $row) {
            if ($row->extt_sess == 'FN') {
                $row->fornoon = $row->extt_sub;
                $row->afternoon = '';
            }
            if ($row->extt_sess == 'AN') {
                $row->fornoon = '';
                $row->afternoon = $row->extt_sub;
            }
            $data[]=$row;
        }
    }
    return $data;
}
return false;
}
Comments