• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Active Records SQL join 4 tables help revise code

#1
[eluser]kreynol7[/eluser]
Hi,

Im creating a dating application, and need to join 4 tables and display members that have a date coming up the soonest.

Here is my regular sql code that works BUT WHEN CONVERTED CODE BELOW TO ACTIVE RECORDS...KEEP READING
Code:
select me.member_id
      ,me.image
      ,me.firstname
      ,me.lastname
      ,me.age
      ,me.gender
      ,me.desire
      ,me.seeking
      ,me.major
      ,me.about
      ,mwn.weekday
      ,mwn.month
      ,mwn.day
      ,mwn.time
      ,mwr.place_image
      ,mwr.place_name
      ,mwr.address
      ,mwr.city
      ,mwr.zip
From member as me
     ,meetingwhen as mwn
     ,meetingwhere as mwr
Where me.member_id=mwn.member_id
And me.member_id=mwr.member_id
Order by me.member_id desc


HERE IS MY ACTIVE RECORD ATTEMPTS IN CI
Code:
$this->db->select('me.member_id
                          ,me.image
                          ,me.firstname
                          ,me.lastname
                          ,me.age
                          ,me.gender
                          ,me.desire
                          ,me.seeking
                          ,me.major
                          ,me.about
                          ,mwn.weekday
                          ,mwn.month
                          ,mwn.day
                          ,mwr.place_image
                          ,mwr.place_image
                          ,mwr.address
                          ,mwr.city
                          ,mwr.zip
                          ');
        $this->db->from('member as me, meetingwhen as mwn, meetingwhere as mwr');
        $this->db->where('me.member_id', 'mwn.member_id');
        $this->db->where('me.member_id', 'mwr.member_id');
        $this->db->order_by('me.member_id', 'desc');
        $this->db->limit(1);

THE CODE DOES NOT RETURN AN ARRAY OF DATA, IN FACT THERE ARE NO ERRORS RETURNED EITHER, NO DATA AND NO ERRORS?

CAN ANYONE FIX THIS CODE, ANY SYNTAX ERRORS? THANKS

#2
[eluser]bubbafoley[/eluser]
just to be sure. are you calling db->get() at the end?

Code:
$this->db->select('me.member_id
                          ,me.image
                          ,me.firstname
                          ,me.lastname
                          ,me.age
                          ,me.gender
                          ,me.desire
                          ,me.seeking
                          ,me.major
                          ,me.about
                          ,mwn.weekday
                          ,mwn.month
                          ,mwn.day
                          ,mwr.place_image
                          ,mwr.place_image
                          ,mwr.address
                          ,mwr.city
                          ,mwr.zip
                          ');
        $this->db->from('member as me, meetingwhen as mwn, meetingwhere as mwr');
        $this->db->where('me.member_id', 'mwn.member_id');
        $this->db->where('me.member_id', 'mwr.member_id');
        $this->db->order_by('me.member_id', 'desc');
        $this->db->limit(1);

        $query = $this->db->get();

        print_r($query->result()); // verify output

#3
[eluser]kreynol7[/eluser]
hey thanks for such a fast reply, the output is just - array ()
so it returns an empty array

#4
[eluser]bubbafoley[/eluser]
hmm I seem to remember having trouble with active record stuff and aliasing table names.

try changing the wheres to this:

Code:
$this->db->where('me.member_id = mwn.member_id');
$this->db->where('me.member_id = mwr.member_id');

you can also use joins

Code:
$this->db->select('me.member_id
                          ,me.image
                          ,me.firstname
                          ,me.lastname
                          ,me.age
                          ,me.gender
                          ,me.desire
                          ,me.seeking
                          ,me.major
                          ,me.about
                          ,mwn.weekday
                          ,mwn.month
                          ,mwn.day
                          ,mwr.place_image
                          ,mwr.place_image
                          ,mwr.address
                          ,mwr.city
                          ,mwr.zip
                          ')
        ->from('member as me')
        ->join('meetingwhen as mwn', 'me.member_id = mwn.member_id', 'left')
        ->join('meetingwhere as mwr', 'me.member_id = mwr.member_id', 'left')
        ->order_by('me.member_id', 'desc')
        ->limit(1);

#5
[eluser]toopay[/eluser]
Code:
$query = $this->db->select('me.* , mwn.* , mwr.*')
                  ->from('member AS me, meetingwhen as mwn, meetingwhere as mwr')
                  ->where('me.member_id = mwn.member_id AND me.member_id = mwr.member_id')
                  ->order_by('me.member_id', 'desc')
                  ->limit(1)
                  ->get();
return $query->result_array();


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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