DB Help: db->query works but not equivilent db->get

[eluser]otherjohn[/eluser]
Can you all help me with this:
this Does not work

Code:

$this->db->select('Options.name, Agents_Options.price');
                $this->db->from('Options, Agents_Options, Products_Options');
                $this->db->where('Agents_Options.agent_id', $this->agent->id);
                $this->db->where('Agents_Options.state', $this->state);
                $this->db->where('Agents_Options.options_id', 'Options.id');
                $this->db->where_in('Products_Options.product_id', $this->products); //products is array
                $this->db->where('Products_Options.options_id', 'Options.id');

but this works?:

Code:

$sql = 'SELECT o.name, ao.price
                              FROM Options o, Agents_Options ao, Products_Options po
                              WHERE ao.agent_id = ' . $this->agent->id
                           . ' AND ao.state = "' . $this->state . '"
                               AND ao.options_id = o.id
                               AND po.product_id IN (' . implode(", ", $this->products) . ') AND po.options_id = o.id';
                        $query = $this->db->query($sql);

Can someone help me figure out why the earlier doesnt work.
john

[eluser]jedd[/eluser]
Eeek! Apart from de-pluralising all those table names .. I'd suggest that you employ the services of the [url="http://ellislab.com/codeigniter/user-guide/general/profiling.html"]CI Profiling Class[/url] - this will show you, very easily and painlessly, the exact SQL queries being run, so you can get a clear idea of the actual differences once the thing hits the database.

Gut feel is possibly some quote / apostrophe characters causing some confusion .. but don't quote me on that. Haha.

[eluser]Thorpe Obazee[/eluser]
Use

Code:

$this->db->last_query();

To determine what query was last executed.

or you can use jedd's option. ;p

[eluser]xwero[/eluser]
or if the AR class is activated $this->db->_compile_select(), then the query isn't executed yet.

[eluser]otherjohn[/eluser]
Excellent! I will jump on these right now. thanks

[eluser]otherjohn[/eluser]
Ok I found the problem.
$this->db->where('Agents_Options.options_id', 'Options.id');
was generating Agents_Options.options_id='Options.id'
I switched it to $this->db->where('Agents_Options.options_id = Options.id'); and it worked
generates Agents_Options.options_id=Options.id
Thanks!

[eluser]boony[/eluser]
ARRRRRGGGGGHHHHHHHHH, Somehow I had missed this about the Profiler Class :gulp: What a handy little option that will be invaluable in the future. I could've used this yesterday to diagnose some databasing issues that I was having and rather than going blind checking one lot of syntax after another this would have helped resolve the issue in half the time.