Ok, I have and unread report table and report table, on click of a button the StaffID and ReportID gets send the read table (Read_Report).
I can retrieve all read reports matching the staffid (query 1)
I can get all reports that are not in both tables but only in reports (query 2).
However in query 2 I need to get reports that are 1 nor read, and 2 related to the session ID.
report
Is there any way i can get this working ?
Read_Report
report
Working (query 1)
function get_read_report()
{
$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
}
Not individual to the user (query 2).
function get_unread_report()
{
$this->db->select('report.Report_Name, report.ReportDate, report.ReportID')
->from('report')
->join('Read_Report', 'report.ReportID = Read_Report.ReportID', 'left')
->where('Read_Report.ReportID IS NULL');
// ->where('Read_Report.StaffID',$this->session->userdata("StaffID"));
$result = $this->db->get();
return $result->result();
}
It looks like there is not enough information in the question to provide any answer. Let me explain why.
If I understand the question correctly you need to get records from the report table which have not been read and thus do not have any related records in the Read_Report table but at the same time have some reference to the $this->session->userdata("StaffID"). Is this correct?
We do not know if the report table has any references to StaffID or if any other table exists which references both ReportID in the report table and StaffID. In short we have no idea how the report is related to the stuff id except the table which tells us that someone read the report.
Most likely there should be a table or some logic which defines if StaffID has access to the report. Using this logic we might list all reports available for given StaffID and thus related to the session ID.
In general the question might be - what does it mean 'reports related to the session ID'? How they are related? If we do not know the answer to this question we can not answer yours.