Join Logic - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Join Logic (/showthread.php?tid=51936) |
Join Logic - El Forum - 05-23-2012 [eluser]ibnclaudius[/eluser] I`m having problems with a join. Check out the attached image. I want to select all events from the colleges that a certain user id has. The field user_id on events table is the author of the event, not the current user. I want also get the author name and the college username of the event. http://img406.imageshack.us/img406/7195/codeigniter.png How is that possible? Join Logic - El Forum - 05-23-2012 [eluser]ibnclaudius[/eluser] I can get the events info, but don`t know how to get only the events from the colleges the user participate... Code: public function get_events($user_id) Join Logic - El Forum - 05-23-2012 [eluser]usefulidiot[/eluser] Code: SELECT * Join Logic - El Forum - 05-23-2012 [eluser]ibnclaudius[/eluser] I don`t want to get the events from the same user_id... Here what I have: Code: $query = $this->db->select($this->_table['events'] . '.id, ' . $this->_table['events'] . '.title, ' . $this->_table['events'] . '.date, ' . $this->_table['users'] . '.username AS user_username, ' . $this->_table['colleges'] . '.username AS college_username') Return this error: Quote:Unknown column '1' in 'on clause' The problem is with the quotes in 1, without it works... But I don`t know how to remove this '' Join Logic - El Forum - 05-23-2012 [eluser]ibnclaudius[/eluser] I tried putting a false param in select, but the problem persists... Join Logic - El Forum - 05-23-2012 [eluser]weboap[/eluser] looks to me like you will be doing like Code: public function get_events($user_id) change events.user_id to events.author_id Join Logic - El Forum - 05-23-2012 [eluser]ibnclaudius[/eluser] Thanks weboap, helped a lot, could you please explain you code for me? Here is the final, working: Code: $query = $this->db->select($this->_table['events'] . '.id, ' . $this->_table['events'] . '.title, ' . $this->_table['events'] . '.date, ' . $this->_table['users'] . '.username AS user_username, ' . $this->_table['colleges'] . '.username AS college_username', FALSE) Join Logic - El Forum - 05-23-2012 [eluser]weboap[/eluser] what do you mean with? Code: ->join($this->_table['events'], $this->_table['events'] . '.college_id = ' . $this->_table['events'] . '.college_id') read : http://mysqljoin.com/ Join Logic - El Forum - 05-23-2012 [eluser]ibnclaudius[/eluser] Ops, fixed: Code: $query = $this->db->select($this->_table['events'] . '.id, ' . $this->_table['events'] . '.title, ' . $this->_table['events'] . '.date, ' . $this->_table['users'] . '.username AS user_username, ' . $this->_table['colleges'] . '.username AS college_username', FALSE) |