+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: date difference from db using codeigniter query (/showthread.php?tid=56740)
Pages:
1
2
date difference from db using codeigniter query - El Forum - 01-16-2013
[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);can any one tell me that DATEDIFF() is applicable in codeigniter. or any suggestion to solve this issue
thanks in advance..
date difference from db using codeigniter query - El Forum - 01-16-2013
[eluser]PhilTem[/eluser]
Quote:$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);
date difference from db using codeigniter query - El Forum - 01-16-2013
[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);date difference from db using codeigniter query - El Forum - 01-16-2013
[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.....
date difference from db using codeigniter query - El Forum - 01-17-2013
[eluser]Bigil Michael[/eluser]
can any one solve this.....................
date difference from db using codeigniter query - El Forum - 01-17-2013
[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.
date difference from db using codeigniter query - El Forum - 01-18-2013
[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.
date difference from db using codeigniter query - El Forum - 01-18-2013
[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.
date difference from db using codeigniter query - El Forum - 01-19-2013
[eluser]Aken[/eluser]
You'll likely need to use a combination of SUM() and GROUP BY in your query.
date difference from db using codeigniter query - El Forum - 01-19-2013
[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