• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Multiple table joins

#3
(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
        
        foreach ($tunesMainArr as $tune) {
            echo    "tid: ".$tune->tuneID.", title: ".$tune->title.
                    ", tm1: ".$tune->tm1.", tm2: ".$tune->tm2.", tm3: ".$tune->tm3.", tm4: ".$tune->tm4.
                    ", linkID: ".$tune->linkID."<br>";  
        }
        exit();

 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

Here's the first several lines of the echo:

tid: 2283, title: A Beautiful Life (Stanley Bros), tm1: 9, tm2: 0, tm3: 0, tm4: 4, linkID: 277
tid: 2216, title: Abe's Retreat, tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 241
tid: 2277, title: Acony Bell, tm1: 64, tm2: 0, tm3: 0, tm4: 4096, linkID: 
tid: 2077, title: Adieu False Heart (Jody Stecher and Kate Breslin), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 
tid: 1411, title: Adieu False Heart (Linda Rondstadt and Anne Savoy), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 236
tid: 1411, title: Adieu False Heart (Linda Rondstadt and Anne Savoy), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 134
tid: 1411, title: Adieu False Heart (Linda Rondstadt and Anne Savoy), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 9
tid: 1411, title: Adieu False Heart (Linda Rondstadt and Anne Savoy), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 112
tid: 1408, title: Alabama Bound (Doc Watson), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 105
tid: 1408, title: Alabama Bound (Doc Watson), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 106
tid: 1408, title: Alabama Bound (Doc Watson), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 107
tid: 105, title: All About You (from Lee Stripling), tm1: 4, tm2: 4, tm3: 0, tm4: 0, linkID: 
tid: 2223, title: Anchored in Love Divine (Annie and Mac), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 254
tid: 2009, title: Angeline the Baker, tm1: 0, tm2: 1, tm3: 0, tm4: 0, linkID: 
tid: 2285, title: Another Cup of Joe, tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID:

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.


Attached Files Thumbnail(s)
   
Reply


Messages In This Thread
Multiple table joins - by codeguy - 11-16-2017, 05:12 PM
RE: Multiple table joins - by JayAdra - 11-16-2017, 06:41 PM
RE: Multiple table joins - by codeguy - 11-16-2017, 10:37 PM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.