Small Question |
[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
[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/
[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();
[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’);
[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.
[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.
[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!='' ...
[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
[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
[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 != \\'\')'; |
Welcome Guest, Not a member yet? Register Sign In |