date difference from db using codeigniter query

[eluser]Bigil Michael[/eluser]
I would like to do a candidates search according to their age. my table structure is like this

id name dob ....................
1 a 1947-10-25
2 b 1950-08-22
3 c 1960-11-07
............................................


my requirement is to select candidates who have age > 1 or 20 like that

I write the where condition like this

Code:

$this->db->where('DATEDIFF(date('Y-m-d'), c.dob) >=', 365);

but it stops execution of query.

can any one tell me that DATEDIFF() is applicable in codeigniter. or any suggestion to solve this issue

thanks in advance..

[eluser]PhilTem[/eluser]

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.

Code:

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

[eluser]Bigil Michael[/eluser]
Thanks for your reply

problem is due to syntax.


solved like this

Code:

$mydate = date('Y-m-d');
$this->db->where("DATEDIFF('$mydate', c.dob) >=", 365);

[eluser]Bigil Michael[/eluser]
one more problem here

Here i would like to calculate the experience. table structure shown below

id candidate_id startdate enddate

1 1000 2001-01-01 2001-12-31

2 1000 2002- 01-01 2004- 11- 20

3 1000 2004-11-25 2010-12-31

my query is like this

Code:

$this->db->where('DATEDIFF(end_date, start_date) >=',  365);
$this->db->group_by('candidate_id');

here 365 is a demo value

it run successfully if i search for 1 year, 2 year ...

but if i search for 8 or 9 years no result is the answer.

can any one help me to combine these dates according to the cndidate_id.

thanks.....

[eluser]Bigil Michael[/eluser]
can any one solve this.....................

[eluser]CroNiX[/eluser]
I'd start a new post. You already marked this thread as solved, so most people won't look back at it as they figure your issue is already solved.

[eluser]Aken[/eluser]
What exactly do you want your result to look like? If you use GROUP BY, you'll only have one row returned per candidate ID.

[eluser]Bigil Michael[/eluser]
Thanks for your reply.

my exact requirement is to combine the experience

that is the experience of the candidate with candidate_id 1000 is from 2001-01-01 to 2010-12-31.

[eluser]Aken[/eluser]
You'll likely need to use a combination of SUM() and GROUP BY in your query.

[eluser]Bigil Michael[/eluser]
changed query like this

Code:

$this->db->where('SUM(DATEDIFF(end_date, start_date)) >=', 365);
$this->db->group_by('candidate_id');

but it shows error like this : Invalid use of group function