CodeIgniter Forums
ActiveRecord, MySQL with DATE_SUB ... how? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: ActiveRecord, MySQL with DATE_SUB ... how? (/thread-23233.html)



ActiveRecord, MySQL with DATE_SUB ... how? - El Forum - 10-04-2009

[eluser]hugle[/eluser]
Hello Everyone,

I'm trying to grab data, from database, which is older than 30 days:

I'll paste the live example, so it would be easier to understand...
Code:
$this->db->select('`favorites`.*, `razrabotki`.`name` AS `name`, `razrabotki`.`text` AS `text`, `razrabotki`.`created` AS `created2`');
$this->db->where(array('user_id' => $this->user_id, 'class_id' => '3'));
//$this->db->where('DATE_SUB(CURDATE(),INTERVAL 30 DAY) > `created2`');
$this->db->join('users', 'users.id = favorites.user_id', 'left');
$this->db->join('razrabotki', 'razrabotki.id = favorites.item_id', 'left');
$this->db->order_by("favorites.id", "desc");
$out = $this->db->get($this->_table)->result_array();

as you noted, the line #3 is commented out, since if I activate it, I get MySQL errors...

maybe this line $this->db->where('DATE_SUB(CURDATE(),INTERVAL 30 DAY) > `created2`')
should be described somehow in other way? I've already tried differend approaches... but with no luckSad

Maybe you guys could point me on direction?
I like ActiveRecord style so much!Smile

THank you!


ActiveRecord, MySQL with DATE_SUB ... how? - El Forum - 10-04-2009

[eluser]InsiteFX[/eluser]
Code:
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

Enjoy
InsiteFX


ActiveRecord, MySQL with DATE_SUB ... how? - El Forum - 10-04-2009

[eluser]cahva[/eluser]
You dont actually have to use DATE_SUB at all. This is perfectly valid query:
Code:
SELECT CURDATE() - INTERVAL 31 DAY;

For the AR this should work:
Code:
$this->db->where('(CURDATE() - INTERVAL 31 day) > created2');

If the query fails, you can check the last query with
Code:
echo $this->db->last_query();



ActiveRecord, MySQL with DATE_SUB ... how? - El Forum - 10-04-2009

[eluser]hugle[/eluser]
[quote author="InsiteFX" date="1254710020"]
Code:
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

Enjoy
InsiteFX[/quote]

actually I did try you approach in many ways.. but didn't realize how it should be doneSad

I just found out the solution, I just needed to replace the order like:
Code:
$this->db->where('`razrabotki`.`created` > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');

Thanks ! Smile
cheers!


ActiveRecord, MySQL with DATE_SUB ... how? - El Forum - 10-04-2009

[eluser]hugle[/eluser]
[quote author="cahva" date="1254712837"]You dont actually have to use DATE_SUB at all. This is perfectly valid query:
Code:
SELECT CURDATE() - INTERVAL 31 DAY;

For the AR this should work:
Code:
$this->db->where('(CURDATE() - INTERVAL 31 day) > created2');

If the query fails, you can check the last query with
Code:
echo $this->db->last_query();
[/quote]

Thanks for your suggestions cahva!
Code:
$this->db->where('(CURDATE() - INTERVAL 31 day) > razrabotki.created');
this one works tooSmile so it is better to avoid DATE_SUB? thanks!

but this one didn't work:
[code
$this->db->where('(CURDATE() - INTERVAL 31 day) > created2');
[/code]

O was getting error Unknown column 'created2' in 'where clause' ...
thanks guys for helping me !


ActiveRecord, MySQL with DATE_SUB ... how? - El Forum - 10-06-2009

[eluser]cahva[/eluser]
Theres nothing wrong using DATE_SUB I think but I think its cleaner not to use it if you really dont need to use it Smile

The created2 column I got straight from your first post so dont blame me Big Grin I see that you created an alias for that in the earlier select so thats something to investigate why it wouldnt work. In common sense it should have worked. Maybe if you put generated query we could see whats that all about.