Comparing timestamps properly in AR class

Archives Yii 1.0.x Discussions
1

Hello!

I must check the current value of a timestamp field and do the following:

a) if the value < NOW(), then I must perform DATE_ADD(NOW(), INTERVAL 30 DAY)

B) but if the field is > NOW(), then I must perform DATE_ADD(VIP_UNTIL, INTERVAL 30 DAY), where VIP_UNTIL is the timestamp fields I am talking about.

I've developed the following code so far, but it doesn't work properly without any errors appearing:



	$users=users::model()->findByPK(Yii::app()->user->getState("USER_ID"));


	if($users->VIP_UNTIL < new CDbExp​ression("NOW()")){


				$users->VIP_UNTIL = new CDbExp​ression("DATE_ADD(NOW(), INTERVAL 30 DAY)");


				}


				else{


						$users->VIP_UNTIL = new CDbExp​ression("DATE_ADD(VIP_UNTIL, INTERVAL 30 DAY)");	


				}

The problem is that I am not sure this compare method is proper, as well as the exp​ression for the else case. Could you remove my doubts, by referring to a hidden magic Yii feature :P)?

I could do it with a simple CdbCommand, but I don't want to destroy the nice looking AR code style, without being sure that this is the only approach.

Penko

2

You can't do that comparison in the if-statement.

CDbExp​ression is essentially a marked string that informs AR not to escape it when being inserted/updated to database.

3

Ok, what should I do then?

4

I believe your DBMS should support SWITCH or similar syntax so that you don't need to write if-statement in PHP.

5

I am using MySQL. I have never used such approach in MySQL, so I'd try to search about something like that. Anyway, if you have any suggestion on this, please share. I will provide my feedback on the implementation with switch.

6

Check this: http://dev.mysql.com…-functions.html

7

Qiang, you are the man!

The final implementation(in case somebody else needs it) is:



	$users=users::model()->findByPK(Yii::app()->user->getState("USER_ID"));


				


					$users->VIP_UNTIL = new CDbExp​ression("IF(VIP_UNTIL < NOW(), 


					DATE_ADD(NOW(), INTERVAL 30 DAY), DATE_ADD(VIP_UNTIL, INTERVAL 30 DAY))");

I find IF on SQL level much better than on PHP level.