CodeIgniter Forums
Database Helper - Between clause - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forum-22.html)
+--- Thread: Database Helper - Between clause (/thread-51613.html)



Database Helper - Between clause - El Forum - 05-10-2012

[eluser]The Revel[/eluser]
I was wondering is there was some sort of between clause for active record, for instance:
Code:
$query = $this->db->from('table')
                  ->where('date')
                  ->between('2012-04-01','2012-05-01')
                  ->get();

This would produce
Code:
SELECT * FROM table WHERE date BETWEEN '2012-04-01' AND '2012-05-01'

I am surprised this does not exist as a clause, and if it does I am surprised its not in the user manual.



Database Helper - Between clause - El Forum - 05-10-2012

[eluser]CroNiX[/eluser]
There is much that isn't in AR. It could also be that other databases that CI supports doesn't have equivalent commands.
For those I just use a regular where.
Code:
->where("field BETWEEN '2012-04-01' AND '2012-05-01'")
Which is about the same amount of text
as
Code:
->where(field)->between('2012-04-01', '2012-05-01')



Database Helper - Between clause - El Forum - 05-10-2012

[eluser]The Revel[/eluser]
Thank you, I will try that out.



Database Helper - Between clause - El Forum - 05-10-2012

[eluser]The Revel[/eluser]
[quote author="CroNiX" date="1336707191"]There is much that isn't in AR. It could also be that other databases that CI supports doesn't have equivalent commands.
For those I just use a regular where.
Code:
->where("field BETWEEN '2012-04-01' AND '2012-05-01'")
Which is about the same amount of text
as
Code:
->where(field)->between('2012-04-01', '2012-05-01')
[/quote]

Thanks for the help, worked like a charm, minus some spelling errors on my part, but worked none the less. Thank you again.

ALso the reason I think that ->between() should be added is because its a pain in the but to type in what I did:

Code:
->where('date BETWEEN "' . $date1 . '" AND "' . $date2 .'"')

would have been nicer and LESS typing is there was a
Code:
->between($date1, $date2)
Or even a
Code:
->where('date', between($date1, $date2))
It would be more efficient IMHO.


Database Helper - Between clause - El Forum - 05-11-2012

[eluser]jmadsen[/eluser]
Well, you're in the Ignited Code section, so go ahead and write one and post it up.

But to explain what CronNix was saying, the Active Record syntax must support only language that is valid for ALL databases that CI supports, so if any of them don't have a BETWEEN keyword, it has to be left out of the code base

You can also just write:

Code:
->where('date >', $date1)->where('date <', $date2);

Which is safer, IMO