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->db->query($sql);

(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() {
  $result=null;

  // load user_id
  $user = $this->ion_auth->user()->row();

  $sql="SELECT * FROM clienti WHERE id IN (SELECT DISTINCT `firma` FROM `program_saptamanal` WHERE firma !='') AND agent_id='{$user->id}'";

  $q = $this->db->query($sql);
  if($q->num_rows()) {
   $result=$q->result_array();
  }
  return $result;
}

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)
    {
        $result_array = array();

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

        foreach ($query->result_array() as $row) {
            $result_array[] = $row;
        }
        $query->free_result();

        return $result_array ? $result_array : FALSE;
    }

returneaza array si nu obiect

[eluser]robertGM[/eluser]
Ok manage to change it with join :

Code:

$this->db->select('*')
     ->from('clienti')
     ->join('program_saptamanal', 'program_saptamanal.firma = clienti.id', 'INNER');

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 != \\'\')';