Small Question - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21) +--- Thread: Small Question (/showthread.php?tid=59469) Pages:
1
2
|
Small Question - El Forum - 10-09-2013 [eluser]robertGM[/eluser] I jest wanted to ask how can i transform this query in CI : $sql="SELECT * FROM users WHERE id IN (SELECT DISTINCT $coloana FROM $table) ORDER BY username ASC"; $this->db->select() ->from('users') ->where('id' ??? ) ->order_by('username ASC'); Thanks Small Question - El Forum - 10-09-2013 [eluser]Otemu[/eluser] Hi, In CI just use: Code: $query = $this->db->query('SELECT * FROM users WHERE id IN (SELECT DISTINCT '.$coloana.' FROM '.$table.') ORDER BY username ASC'); you can pass $coloana and $table to your model checkout datatbase guide http://ellislab.com/codeigniter/user-guide/database/ Small Question - El Forum - 10-09-2013 [eluser]robertGM[/eluser] I know .. i was asking how can i change the first query in the secound one format like ... $this->db->select() ->from ('") ....... ....... $query->$this->db->get(); Small Question - El Forum - 10-11-2013 [eluser]robertGM[/eluser] Anyone ? i want to make this query $sql=“SELECT * FROM users WHERE id IN (SELECT DISTINCT $coloana FROM $table) ORDER BY username ASC”; into : $this->db->select() ->from(‘users’) ->where(‘id’ ??? ) ->order_by(‘username ASC’); Small Question - El Forum - 10-11-2013 [eluser]CroNiX[/eluser] You can't. AR really only does simple queries. For more advanced things, like a subselect, just use db::query() and use db::escape() to escape any variables/input. Small Question - El Forum - 10-13-2013 [eluser]robertGM[/eluser] Thanks for the input ... i have one more question related to a query. How can add one more where to the subselection .. I need something like : From : $sql='SELECT * FROM clienti WHERE id IN (SELECT DISTINCT `firma` FROM `program_saptamanal` WHERE firma != \\'\')'; to $sql='SELECT * FROM clienti WHERE id IN (SELECT DISTINCT `firma` FROM `program_saptamanal` WHERE firma != \\'\')'; and where('agent_id', $user->id); agent_id - is from program_saptamanal table I want the users to select only there records not all the records .. Thanks for the support. Small Question - El Forum - 10-14-2013 [eluser]scornaky[/eluser] Servus Robert, Ia incearca Code: $sql="SELECT * FROM clienti WHERE id IN (SELECT DISTINCT `firma` FROM `program_saptamanal` WHERE firma !='') AND agent_id='{$user->id}'"; (This can be done also with INNER JOIN) ---- Write a little different query : SELECT * FROM program_saptamanal AS ps ON ... LEFT JOIN clienti AS c ON .... WHERE c.firma!='' ... Small Question - El Forum - 10-14-2013 [eluser]robertGM[/eluser] Thanks for the fast replay but i still have a problem .. here is the full function Code: public function program() { and here is the error i get Code: Fatal error: Call to a member function num_rows() on a non-object in Thanks for the help Small Question - El Forum - 10-14-2013 [eluser]scornaky[/eluser] From guide if ($q->num_rows() > 0) // check if there are any records { foreach ($q->result() as $row) { } And also $str = $this->db->last_query(); echo $str; Or enable_profiler(); to see query that you run ; query`ul de mai sus nu pare sa ruleze corect( sa nu aiba inregistrari) uite o functie ---ajutatoare Code: public function select($sql_query) returneaza array si nu obiect Small Question - El Forum - 10-15-2013 [eluser]robertGM[/eluser] Ok manage to change it with join : Code: $this->db->select('*') one more question now : from the old sql how can i add to the new one the DISTINCT part ? I have tryed with $this->db->distinct(); but with no luck .... Code: $sql='SELECT * FROM clienti WHERE id IN (SELECT DISTINCT `firma` FROM `program_saptamanal` WHERE firma != \\'\')'; |