[SOLVED] I can not use CActiveDataProvider

Hello every body

I want create a Private Messaging, but I can not use CActiveDataProvider !

in my controller i have :


<?php


public function actionIndex()

	{

		$uname = strtolower(Yii::app()->user->name);

		$this->render('index',array(

			'dataProvider'=>new CActiveDataProvider('message', array(

				'criteria' => array('condition' => 'LOWER(to) = '.$uname

		)))));


	

	}

And yii returns this error message :

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘to) = root’ at line 1

Where is a problem ?

Try:


array(

        'condition' => 'LOWER(to) = :to',

        'params' => array(':to' => strtolower($uname)),

)

It don’t work :

You get this error because the filed name "to" is a reserved word in mysql

reserved words in mysql - http://dev.mysql.com…rved-words.html

for reserved words you have to quote them… so instead of using lower(to) use lower(‘to’)

Thanks for your help, I doesn’t know this rules of mysql.

But I have yet a problem.

In my pm tables i have :

And in my controller :


<?php

public function actionIndex()

	{

		$uname = strtolower(Yii::app()->user->name);

		$this->render('index',array(

			'dataProvider'=>new CActiveDataProvider('message', array(

				'criteria' => array(

        'condition' => 'LOWER(\'to\') = :to',

        'params' => array(':to' => $uname),

		)

		))));


	

	}



$uname = ‘root’;

I should have 2 results but i have "No results found."

Am I doing something wrong?

Try to use




'condition'=>"LOWER('to') = :to",



if it does not work… then I would suggest to rename that field so that you don’t use a reserved word

It does not work …

I have genered my db shemas whith "mysql workbench". How to make the field is not defined as: reserved words ? (sorry for my stupid question)

I don’t use workbech but there should be an option to rename a field and just rename fields

from => fromuser

to => touser

also… as the fields in the table are all lowercase… try to use without the function "lower()" just to see if you get any result…

I have rename fields and when i use :




<?php


public function actionIndex()

	{

		$uname = strtolower(Yii::app()->user->name);

		$this->render('index',array(

			'dataProvider'=>new CActiveDataProvider('message', array(

				'criteria' => array(

        'condition' => 'touser = :touser',

        'params' => array(':touser' => $uname),

		)

		))));


	

	}

it’s work. But when i use lower it does not work :




<?php


	public function actionIndex()

	{

		$uname = strtolower(Yii::app()->user->name);

		$this->render('index',array(

			'dataProvider'=>new CActiveDataProvider('message', array(

				'criteria' => array(

        'condition' => "LOWER('touser') = :touser",

        'params' => array(':touser' => $uname),

		)

		))));


	

	}




i have: "No results found." why ?

I think it does not work because somewhere the quotes got prefixed with a backslash like “lower(\‘touser\’)”

but now that you are not using a reserved word you can use “lower(‘touser’)” without quotes as “lower(touser)”…

On the other side… if that is your table… and you are the only one inserting records in it… and you use only lowercase… why don’t you insert lowercase usernames (convert to lowercase before insert)… so that you don’t have to use the lower() function in the select…

It’s work !!!

Thank for your help mdomba !

Without you I could never happen. Thank you very much

No problem… I’m glad I can help…