11-16-2017, 10:37 PM (This post was last modified: 11-17-2017, 08:24 AM by codeguy.)
(11-16-2017, 06:41 PM)JayAdra Wrote: Your joins are linking A.widgetID to B.tagID, whereas they need to be A.widgetID to B.widgetID (linking to the foreign key). Otherwise you'll be linking data arbitrarily. Try updating your joins to all link to the foreign key, rather than the primary. PS: The reason you may have seen the tags join working (although it is incorrect) may just be due to lack of test data/variety. Your tagID's likely were the same as the widgetID in your test case.
Thanks Jay. The code I showed above was my attempt at abstracting what I thought were the relevant pieces but after so many hrs over the last few days my brain was not seeing what I was typing. Here is the actual model function I have running now, un-abstracted.
Code:
public function getTunesGrid() {
$memID=$this->session->userdata('memID');
$tunesMainArr=$this->db
->select(
'A.tuneID, title, newLyrics,
tm1, tm2, tm3, tm4,
linkID, linkDesc, linkURL, noteID'
)
->from('mztunes A')
->join('mztags B','A.tuneID = B.tuneID', 'left')
->join('mzlinks C','A.tuneID = C.tuneID', 'left')
->join('mznotes D','A.tuneID = D.tuneID', 'left')
->where('A.memID',$memID)
->order_by('A.title','asc')
->get()->result(); // makes tunesMainArr an array of tune objects
I appended that echo so I could see just what the query produced. There are actually 243 $tune objects in the mztunes database that satisfy the '->where('A.memID',$memID)' method.
There should be only one row per tuneID. Tune 1411 actually has 4 links, tune 1408 has 3 but I somehow need to get those onto the same tune row - I think.
What I'm trying to do is have all the links for a tune appear as properties of the one tune array element to which they belong. That seems to be a bridge too far for my brain right now.
This is the view at this stage showing each tune on one panel. When user clicks the links btn that tune for example, the links for that panel are 'toggled' and will appear just below the top line but in the same 'panel'.
Note that the tm1 . . . 4 columns are tagMask1. . 4 stored as INT. They appear as expected in the echo above and also below the top line in the view below when the Tags btn is clicked for that tune.
