Need Help To Build CodeIgniter SQL Query

[eluser]Ahmed Iqbal[/eluser]
Hello Guys,

I'm going to convert my old site in codeigniter frameework.
So, I need little help in this query...

Code:

SELECT s.*, u.*, c.* FROM sms AS s JOIN users AS u ON(s.user_id = u.user_id) JOIN categories AS c ON(s.cat_id = c.cat_id) WHERE s.cat_id = {$catid}

I want to convert this query in codeignter code...! so I've done little job but vain

Code:

$this->db->select('s.*', 'u.*', 'c.*');
    $this->db->from('sms', 'AS s');
    $this-db->join('users AS u', 's.user_id = u.user_id');
    $this-db->join('categories AS c', 's.cat_id = c.cat_id');
    $this->db->where('s.cat_id', '1');
    $query = $this->db->get();

Please, find the bug in this query and suggest me solution....! i hope you guys reply me...

[eluser]Narkboy[/eluser]
Ok - first, can you get it to work if you drop the alias's?

I know it's a pita typing more, but before you drive yourself nuts, try that. I stopped using alias with active record.

Also, you don't need to specify a SELECT if you want all fields. Try:

Code:

$this->db->from('sms');
$this->db->join('users','sms.user_id = users.user_id');
$this->db->join('categories','sms.cat_id = categories.cat_id');
$this->db->where('sms.cat_id','1');

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

/B

Edited - code type. BAD!

[eluser]Ahmed Iqbal[/eluser]
wow :p
Great man, it's working for me...!

Thanks very much...!

[eluser]Narkboy[/eluser]

[eluser]tonanbarbarian[/eluser]
i know this has fixed the problem
however if you want to know what the problem was it was your select and from commands

rather than

Code:

$this->db->select('s.*', 'u.*', 'c.*');

the select should just take a single parameter

Code:

$this->db->select('s.*, u.*, c.*');

and with the from there should be just a single parameter as well

Code:

$this->db->from('sms', 'AS s');

should be

Code:

$this->db->from('sms AS s');

I also agree that you are better off not using aliases if you can help it
it actually increases readability if you use the full table name

alias were necessary years ago when some database engines has a maximum limit on the lenght of a query string
most databases these days have sufficiently large query lengths that you do not need to worry about aliasing except in the cases when the database engine particularily requires them

[eluser]Ahmed Iqbal[/eluser]
Ohh I see
Really helpful information...! thanks

I love codeigniter and Support Team. really cool stuff...! God bless you.