Codeigniter active record sql error
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 = 'your@emailaddress.com' AND password = 'letmein' LIMIT 1' at line 2
SELECT * WHERE email = 'your@emailaddress.com' 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` = 'your@emailaddress.com' 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?
The line
SELECT * WHERE email = 'your@emailaddress.com' AND password = 'letmein' LIMIT 1
has no FROM clause. I assume it should be:
SELECT * from em_user WHERE email = 'your@emailaddress.com' AND password = 'letmein' LIMIT 1
$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.
I think this error not related to your line of code
$this->db->get_where('em_user', $whereArr, 30, 0)->num_rows()
because in the error message it's appear LIMIT 1 but in your code you limit 30
anyway you can try this line instead of get_where
$this->db->where($whereArr)->limit(30,0)->get('em_user');
and note this line will return num rows not records
Also you can view the query to be sure if it's right or not by add this line after your get_where or query
die($this->db->last_query());
下一篇: Codeigniter主动记录sql错误