To make maintenance easier and possibly get better performance I have split a large table 'widgets' with many columns into 4 smaller tables; widgets, tags, links and notes. For each widget row there are a fixed number of tags but a widget row can have any number of links or notes - from none to many.
The widget table has columns: widgetID, name, color and foreign key userID from a 'users' table.
The tags table has columns: tagID, tagName, and foreign key: widgetID.
The links table has columns: linkID, linkDesc, linkURL and foreign key widgetID.
The notes table has columns: notesID, dateEnt, dateLastEdit and foreign key widgetID.
The xID columns are all PRIMARY KEY AUTO_INCREMENT except when they're foreign keys. I'm using mysqli driver.
This is my query to create array of objects that can populate all the variables in a grid view:
Code:
public function getWidgetGridData() {
$userID=$this->session->userdata('userID');
$objArr=$this->db
->select(
'A.widgetID, A.name, A.color,
B.tagBank1, B.tagBank2, B.tagBank3, B.tagBank4,
C.linkID, C.linkDesc, C.linkURL,
D.dateEnt, D.dateLastEdit, D.content'
)
->from('items A')
->join('tags B','A.widgetID= B.tagID', 'left')
->join('links C','A.widgetID= C.linkID', 'left')
->join('notes D','A.widgetID= D.noteID', 'left')
->where('A.userID',$userID)
->order_by('A.title','asc')
->get()->result();
return $objArr // should return array of objects to controller (one for each select col)
It kind of works but never correctly yet. I tried various combos of link types and various sequence of statements. Sometimes it repeats some widget rows a number of times that matches the number of links for that widget. (Should be only one row per widget). Sometimes it doesn't list a widget row at all when there are no links present for that widget. Sometimes it just lists a single widget row - maybe associated with the fact there's only one note for one widget in the note table. When I comment out the links and notes joins, it works fine (i.e with just the tags join).
Any suggestions greatly appreciated.
