Search code examples
phpmysqlcodeigniterlinktable

Selecting View Item returns Null (Checklist Link Table)

I seem to be having a problem with a query showing a specific item if it is in the checklist table. I currently have multiple tables which the main are user, character, series, item & checklist. Character & Series are linked into the item table, whilst User & Item are linked in the checklist table.

The way this is working is if a button is pressed against an item that is not in the checklist table by a user logged in, this item will be added into the checklist table (If in the checklist able, a delete button will be shown).

What I currently have working is the following:

  • If User is not logged in and on the main page: Show all items with a X
  • If User is logged in and on the main page: Show all items not in
    checklist table with a X and all items in checklist table with a
    Tick.
  • If a User is not logged in a clicks view more details on item:
    Show more details on the item with X (individual item page)

What I am trying to achieve now is if a user is logged in and clicks view more details, to show the individual item page with a X or Tick (Depending if in checklist table). This is kinda working as items which are in the checklist table can be selected to view the individual item page when logged in. However if the item is not in the checklist table and the user is logged in, nothing is returned back. I am at a total loss how to resolve this and am trying to avoid copying all the items into the checklist table with an additional field (Y/N).

This is the query I am using to get the specific item information if a user is logged in which I am using codeigniter:

$this->db->select('item.item_id AS item_id, item.item_image AS item_image, line.line_name AS line_name, series.series_name AS series_name, character.character_name AS character_name, checklist.checklist_id AS checklist_id');
$this->db->from('item');
$this->db->join('line', 'item.line_id = line.line_id', 'left');
$this->db->join('series', 'item.series_id = series.series_id', 'left');
$this->db->join('character', 'item.character_id = character.character_id', 'left');
$this->db->join('checklist', 'checklist.item_id = item.item_id', 'left');
$this->db->where('checklist.users_id', $user_id);
$this->db->or_where('checklist.users_id IS NULL'); // Also tried without this line
$this->db->where('item.item_id', $item_id);
$query = $this->db->get();
return $query->row_array();

Any help would be really great as the other three queries are running as intended, which are the same as the above apart from The return ($query->result_array) & an added or_where (checklist.users_id IS NULL).

Solution

  • Seems like I'v found a fix. I moved where item ID under the join, changed the or_where to where, and the where to or_where. So the working code is the following:

    $this->db->select('item.item_id AS item_id, item.item_image AS item_image, line.line_name AS line_name, series.series_name AS series_name, character.character_name AS character_name, checklist.checklist_id AS checklist_id');
$this->db->from('item');
$this->db->join('line', 'item.line_id = line.line_id', 'left');
$this->db->join('series', 'item.series_id = series.series_id', 'left');
$this->db->join('character', 'item.character_id = character.character_id', 'left');
$this->db->join('checklist', 'checklist.item_id = item.item_id', 'left');
$this->db->where('item.item_id', $item_id);    
$this->db->or_where('checklist.users_id', $user_id);
$this->db->where('checklist.users_id IS NULL');    
$query = $this->db->get();
return $query->row_array();

    Not entirely sure if the or_where should be above the where, but its working for what I needed it too.