Co-k-ine
(Admin Angels)
July 25, 2010, 8:04pm
1
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)),
)
mdomba
(Maurizio Domba Cerin)
July 26, 2010, 10:54am
4
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’)
Co-k-ine
(Admin Angels)
July 26, 2010, 6:47pm
5
mdomba:
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?
mdomba
(Maurizio Domba Cerin)
July 26, 2010, 7:25pm
6
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
Co-k-ine
(Admin Angels)
July 26, 2010, 7:54pm
7
mdomba:
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)
mdomba
(Maurizio Domba Cerin)
July 27, 2010, 6:44am
8
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…
Co-k-ine
(Admin Angels)
July 27, 2010, 10:15am
9
mdomba:
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 ?
mdomba
(Maurizio Domba Cerin)
July 27, 2010, 10:24am
10
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)”…
mdomba
(Maurizio Domba Cerin)
July 27, 2010, 10:26am
11
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…
Co-k-ine
(Admin Angels)
July 27, 2010, 10:29am
12
It’s work !!!
Thank for your help mdomba !
Without you I could never happen. Thank you very much
mdomba
(Maurizio Domba Cerin)
July 27, 2010, 10:41am
13
No problem… I’m glad I can help…