Codeigniter SQL Query - DATE_ADD

Question

i'm using CodeIgniter (http://codeigniter.com/) and have a problem with an query:

select *
from mb_login_attempts
where ip_adress_hash = ?
and DATE_ADD(attempt_date,INTERVAL 30 MINUTE) > NOW()

i would like to use the following syntax:

$this->db->where('ip_adress_hash', $this->encrypt->sha1($this->input->ip_address()));
$this->db->where('DATE_ADD(attempt_date,INTERVAL 30 MINUTE) >','NOW()',TRUE);

if($this->db->count_all_results('mb_login_attempts') >= 3) {

 return true;    

}

If i use this code:

$sql = "select *
from mb_login_attempts
where ip_adress_hash = ?
and DATE_ADD(attempt_date,INTERVAL 30 MINUTE) > NOW()";

$val = $this->db->query($sql,$this->encrypt->sha1($this->input->ip_address()));

if($val->num_rows() >= 3) {

 return true;    

}

Does anyone have an idea how i get the first code get working correct? Edit: i have change some code to the comments - but it still doesn't work ...

regards ...

spotlr · Accepted Answer · 2012-06-02 13:45:44Z

1

Modify the last line with the ~~TRUE~~ FALSE value so it won't escape the NOW() function:

$this->db->where('DATE_ADD(attempt_date,INTERVAL 30 MINUTE) >','NOW()', FALSE);

EDIT: Run this query:

$this->db->where('ip_adress_hash', $this->encrypt->sha1($this->input->ip_address()));
$this->db->where('DATE_ADD(attempt_date,INTERVAL 30 MINUTE) >','NOW()', FALSE);
$val = $this->db->get('mb_login_attempts');

EDIT2: It Is still okay to use this form:

...

$this->db->where('ip_adress_hash', $this->encrypt->sha1($this->input->ip_address()));
$this->db->where('DATE_ADD(attempt_date,INTERVAL 30 MINUTE) >','NOW()', FALSE);

if($this->db->count_all_results('mb_login_attempts') >= 3) {

 return true;    

}

edited Jun 2, 2012 at 13:45

spotlr

3195 silver badges15 bronze badges

answered Jun 2, 2012 at 12:14

Yan Berk

14.5k9 gold badges58 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

spotlr Over a year ago

well if i change tHe line to the code above and use "if($this->db->count_all_results('mb_login_attempts') >= 3) { ..." it still does't work ?!

Yan Berk Over a year ago

Please edit your post and add the count_all_results line. In addition, count_all_results is not equal to select * like in your other queries. It is equal to COUNT (*)

Yan Berk Over a year ago

please add the code var_dump($this->db->last_query()); after the line if($this->db->count_all_results('mb_login_attempts') >= 3) and post here the result.

spotlr Over a year ago

string(...) "SELECT COUNT(*) AS numrows FROM (mb_login_attempts) WHERE ip_adress_hash = 'e80a1a342e3540ef0e1...' AND DATE_ADD(attempt_date,INTERVAL 30 MINUTE) > 'NOW()'" ... the hash is edited

Yan Berk Over a year ago

I have edited my post. Please run the new query. I have made a mistake: add FALSE instead of TRUE.

Collectives™ on Stack Overflow

Codeigniter SQL Query - DATE_ADD

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related