Beep Beep - 7 months ago 12
PHP Question

get data when session id matches table

I am trying to retrieve all reports from

report
when Session StaffID matches StaffID in
reports
table.

Report_Name, ReportDate and ReportID are part of the report table


this is how i think it should go

Get
ReportID
,
Report_Name
and
ReportDate
from
report
where
ReportID
from
Read_Report
matches
ReportID
from
report

when session
StaffID
=
StaffID
in
Read_Report


and this is my query

function get_read_report()
{
$this->db->select('report.Report_Name, report.ReportDate, report.ReportID')
->from('Read_Report')
->join('Read_Report', 'report.ReportID = Read_Report.ReportID')
->where('StaffID', $this->session->userdata("StaffID"));
return $result = $this->db->get();
}


I get this error

Error Number: 1066

Not unique table/alias: 'Read_Report'

SELECT `report`.`Report_Name`, `report`.`ReportDate`, `report`.`ReportID` FROM `Read_Report` JOIN `Read_Report` ON `report`.`ReportID` = `Read_Report`.`ReportID` WHERE `StaffID` = '3'

Filename: models/report/Report_model.php

Line Number: 91


Controller code

function my_read_reports()
{
$data = array();

if ($query = $this->report_model->get_read_report()) {
$data['reports'] = $query;
}

$this->template['middle'] = $this->load->view($this->middle = 'pages/read_reports_view', $data, true);
$this->layout();
}

Answer

You need to join your table with report table not the same table

Change

 ->join('Read_Report', 'report.ReportID = Read_Report.ReportID')

TO

 ->join('report', 'report.ReportID = Read_Report.ReportID')

You need to change your query to

$this->db->select('report.Report_Name, report.ReportDate, report.ReportID')
            ->from('report')
            ->join('Read_Report', 'report.ReportID = Read_Report.ReportID')
            ->where('Read_Report.StaffID', $this->session->userdata("StaffID"));
         $result = $this->db->get();
         return $result->result();// fetch data then return
Comments