What is wrong with my SQL statement?

[eluser]mojitoo[/eluser]
Hey!

This SQL statement works fine in phpmyadmin

Code:

SELECT * FROM lists, your_lists WHERE your_lists.list_id = lists.id

But it doesn't work when I try to do the same thing in codeigniter

Code:

$this->db->where('your_lists.list_id', 'lists.id');
$query = $this->db->get('lists, your_lists');
return $query->result();

It doesn't give me anything as a result in codeigniter.

[eluser]vrencianz[/eluser]
Use join, as described here: http://ellislab.com/codeigniter/user-gui...tml#select

Code:

$this->db->select('*');
$this->db->from('lists');
$this->db->join('your_lists', 'your_lists.list_id = lists.id');

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

[eluser]vitoco[/eluser]
I don't get the need of transform the sql query to the "CI way", why not just run the query??

Code:

$sql = "SELECT * FROM lists, your_lists WHERE your_lists.list_id = lists.id ";
$query = $this->db->query( $sql );

foreach( $query->result_array() as $row )
{
// DO SOMETHING WITH $row
}
  
$query->free_result();

Saludos

[eluser]CroNiX[/eluser]
Some people want their code to be able to be portable and work with other DB's without having to rewrite their queries, not just work with mysql.

[eluser]Samus[/eluser]
Plus active record automatically cleans queries to prevents sql injection attacks.