Well, in my class i have this :
<?php
class UserModule extends CWebModule
{
public static function userSubscription(){
$userSub = Yii::app()->db->createCommand()
->select('u.id as id,username,password,email,activkey,createtime,lastvisit,superuser,status,subscriptionstart,subscriptionend,amount')
->from('tbl_users u')
->leftJoin('tbl_subscription s', 'u.id=s.id_users')
->queryRow();
return new CSqlDataProvider($userSub);
}
}
and i wonders to render this in a widget (index.php)
<?php
$this->breadcrumbs=array(
UserModule::t('Users')=>array('admin'),
UserModule::t('Manage'),
);
?>
<h1><?php echo UserModule::t("Manage Users"); ?></h1>
<?php echo $this->renderPartial('_menu', array(
'list'=> array(
CHtml::link(UserModule::t('Create User'),array('create')),
),
));
?>
<?php $this->widget('zii.widgets.grid.CGridView', array(
'dataProvider'=>usermodule::userSubscription(),
'columns'=>array(
array(
'name' => 'id',
'type'=>'raw',
'value' => 'CHtml::link(CHtml::encode($data->id),array("admin/update","id"=>$data->id))',
),
array(
'name' => 'username',
'type'=>'raw',
'value' => 'CHtml::link(CHtml::encode($data->username),array("admin/view","id"=>$data->id))',
),
array(
'name'=>'email',
'type'=>'raw',
'value'=>'CHtml::link(CHtml::encode($data->email), "mailto:".$data->email)',
),
array(
'name' => 'createtime',
'value' => 'date("d.m.Y H:i:s",$data->createtime)',
),
array(
'name' => 'lastvisit',
'value' => '(($data->lastvisit)?date("d.m.Y H:i:s",$data->lastvisit):UserModule::t("Not visited"))',
),
array(
'name'=>'status',
'value'=>'User::itemAlias("UserStatus",$data->status)',
),
array(
'name'=>'superuser',
'value'=>'User::itemAlias("AdminStatus",$data->superuser)',
),
array(
'name' => 'subscriptionstart',
'value' => 'date("d.m.Y H:i:s",$data->subscriptionstart)',
),
array(
'name' => 'subscriptionend',
'value' => 'date("d.m.Y H:i:s",$data->subscriptionend)',
),
array(
'name' => 'amount',
'value' => '$data->amount',
),
array(
'class'=>'CButtonColumn',
),
),
)); ?>
But when i execute this i have :
E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\framework\db\CDbCommand.php(471)
459
460 if($this->_connection->enableProfiling)
461 Yii::endProfile('system.db.CDbCommand.query('.$this->getText().$par.')','system.db.CDbCommand.query');
462
463 return $result;
464 }
465 catch(Exception $e)
466 {
467 if($this->_connection->enableProfiling)
468 Yii::endProfile('system.db.CDbCommand.query('.$this->getText().$par.')','system.db.CDbCommand.query');
469 Yii::log('Error in querying SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');
470 $errorInfo = $e instanceof PDOException ? $e->errorInfo : null;
471 throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
472 array('{error}'=>$e->getMessage())),(int)$e->getCode(),$errorInfo);
473 }
474 }
475
476 /**
477 * Builds a SQL SELECT statement from the given query specification.
478 * @param array $query the query specification in name-value pairs. The following
479 * query options are supported: {@link select}, {@link distinct}, {@link from},
480 * {@link where}, {@link join}, {@link group}, {@link having}, {@link order},
481 * {@link limit}, {@link offset} and {@link union}.
482 * @return string the SQL statement
483 * @since 1.1.6
The sql generated is good :
SELECT `u`.`id` AS `id`, `username`, `password`, `email`, `activkey`, `createtime`, `lastvisit`, `superuser`, `status`, `subscriptionstart`, `subscriptionend`, `amount`
FROM `tbl_users` `u`
LEFT JOIN `tbl_subscription` `s` ON u.id=s.id_users
I find one things : this is my traces :
2011/01/18 17:23:07 [trace] [system.db.CDbCommand] Querying SQL: SELECT `u`.`id` AS `id`, `username`, `password`, `email`, `activkey`, `createtime`, `lastvisit`, `superuser`, `status`, `subscriptionstart`, `subscriptionend`, `amount`
FROM `tbl_users` `u`
LEFT JOIN `tbl_subscription` `s` ON u.id=s.id_users
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\controllers\AdminController.php (45)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\index.php (14)
2011/01/18 17:23:07 [trace] [system.CModule] Loading "messages" application component
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\UserModule.php (119)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\views\admin\index.php (3)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\controllers\AdminController.php (56)
2011/01/18 17:23:07 [trace] [system.CModule] Loading "widgetFactory" application component
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\views\admin\index.php (54)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\controllers\AdminController.php (56)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\index.php (14)
2011/01/18 17:23:07 [trace] [system.db.CDbCommand] Querying SQL: Array LIMIT 10
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\views\admin\index.php (54)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\controllers\AdminController.php (56)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\index.php (14)
2011/01/18 17:23:07 [error] [system.db.CDbCommand] Error in querying SQL: Array LIMIT 10
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\views\admin\index.php (54)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\controllers\AdminController.php (56)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\index.php (14)
2011/01/18 17:23:07 [trace] [system.CModule] Loading "coreMessages" application component
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\views\admin\index.php (54)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\protected\modules\user\controllers\AdminController.php (56)
in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\winetracker\index.php (14)
2011/01/18 17:23:07 [error] [exception.CDbException] exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 Erreur de syntaxe pr�s de 'Array LIMIT 10' � la ligne 1' in E:\PFSF7319\EasyPHP-5.3.4.0\www\yii\framework\db\CDbCommand.php:471
Stack trace:
‘Array LIMIT 10’ where is it generated ?
Thanks