createCommand UNIX_TIMESTAMP with CDbExpression

Yii 1.1 General Discussion
1

Hello,

i’m trying to get items from a database that r most current.




		$v = Yii::app()->db->createCommand()

					->select('COUNT(id)')

					->from('rating')

					->where('UNIX_TIMESTAMP(timestamp) >= :a', array(":a" => new CDbExpression("UNIX_TIMESTAMP(NOW()-3600)")))

					->queryScalar();

this query somehow gets executed wrongly since results r returned although there shouldn’t be.

This works




		$v = Yii::app()->db->createCommand()

					->select('COUNT(id)')

					->from('rating')

					->where('UNIX_TIMESTAMP(timestamp) >= UNIX_TIMESTAMP(NOW())-3600))

					->queryScalar();

sadly the logfile is somehow misleading as well.




SELECT COUNT(id) FROM `rating`

WHERE UNIX_TIMESTAMP(timestamp) >= :a. Bound with :a=UNIX_TIMESTAMP(NOW()-3600))

if i insert this manually into my mysql, the correct results r returned. sadly yii doesn’t return the full mysql query

best regards

2

Hi, welcome to the forum!

You should not use parameters for pass mysql expression, because they will be sanitized.

The query you are getting is:




SELECT COUNT(id) FROM `rating`

WHERE UNIX_TIMESTAMP(timestamp) >= 'UNIX_TIMESTAMP(NOW()-3600))'

Because parameters exists for being escaped and enclosed in ‘’.

If you don’t want it, just write your sql.