Codeigniter active record sql error

Question

I am using the get_where() function in codeigniter, and I am getting mysql errors, dependent on what I set the limit and offset too, for example this code,

$this->db->get_where('em_user', $whereArr, 30, 0)->num_rows()

returns a mysql error that looks like this,

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE email = '[email protected]' AND password = 'letmein' LIMIT 1' at line 2

SELECT * WHERE email = '[email protected]' AND password = 'letmein' LIMIT 1

However if I run this code,

$this->db->get_where('em_user', $whereArr, 30, 30)->num_rows()

it seems to run fine, it seems to run fine, it returns no results but I don not get the error(I assume the no results is because there is an offset of 30 and I only have 2 records in my table).

The sql that this code produces looks like this,

SELECT * FROM (`em_user`) WHERE `email` = '[email protected]' AND `password` = 'letmein' LIMIT 30, 30

I dont understand how having a limit of 1 at the end of query can cause so much grief, can anyone enlighten me please?

Answer 1

The line

SELECT * WHERE email = '[email protected]' AND password = 'letmein' LIMIT 1

has no FROM clause. I assume it should be:

SELECT * from em_user WHERE email = '[email protected]' AND password = 'letmein' LIMIT 1

Answer 2

$this->db->get_where('em_user', $whereArr, 30, 30)->num_rows() won't get any results. num_rows() would give the you the number of results.