yii beginner need help

hi everyone …

i’m new in yii development and i need ur help plz …

i’m trying the blog tutorial …but i have the following problem :

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 ‘update time DESC LIMIT 10’ at line 1

and i’m not able to resolve it …

thnx in advance …

can you post the code that you think is causing this error and your database schema?




 if($this->_connection->enableProfiling)

00366:                 Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');

00367: 

00368:             return $result;

00369:         }

00370:         catch(Exception $e)

00371:         {

00372:             if($this->_connection->enableProfiling)

00373:                 Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');

00374:             Yii::log('Error in querying SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');

00375: throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',

00376:                 array('{error}'=>$e->getMessage())));

00377:         }




that’s what the browser said it’s causing the error …and my database schema is the same as blog tutorial …i’m just a beginner …

thnx alot

Well I think that is Yii’s code. Yii not always point to you the cause error’s code directly. Maybe you can provide more detail information by show us the url.

And also you can find yourself the source of problem by looking from url itself. For example if that open index.php?r=xxxx/yyyy causing you error.

Then you may check it at /protected/controllers/xxxx.php

and in the xxxx.php file you may search function called actionYyyy();

maybe you can post that code in here, so somebody might see is there any problem…

Just suggestion ;)

this problem occures when i try to open : http://localhost/blog/index.php?r=post

and that’s the code of postcontroller.php





<?php


class PostController extends Controller

{

	const PAGE_SIZE=10;

public $layout='col2';


	/**

	 * @var CActiveRecord the currently loaded data model instance.

	 */

	private $_model;


	/**

	 * @return array action filters

	 */

	public function filters()

	{

		return array(

			'accessControl', // perform access control for CRUD operations

		);

	}


	/**

	 * Specifies the access control rules.

	 * This method is used by the 'accessControl' filter.

	 * @return array access control rules

	 */

	public function accessRules()

	{

		

			return array(

				array('allow', // allow all users to perform 'list' and 'show' actions

				'actions'=>array('index', 'view'),

				'users'=>array('*'),

				),

				array('allow', // allow authenticated users to perform any action

				'users'=>array('@'),

				),

				array('deny', // deny all users

				'users'=>array('*'),

				),

			);

	}


	/**

	 * Displays a particular model.

	 */

	public function actionView()

	{

		$post=$this->loadModel();

		$comment=$this->newComment($post);

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

		'model'=>$post,

		'comment'=>$comment,));

	}

	

	public function loadModel()

	{

		if($this-> model===null)

			{

		if(isset($_GET['id']))

				{

				if(Yii::app()->user->isGuest)

					$condition='status='.Post::STATUS_PUBLISHED

					.' OR status='.POST::STATUS_ARCHIVED;

					else

					{

					$condition='';

					$this->model=Post::model()->findbyPk($_GET['id'], $condition);

					}

					if($this-> model===null)

					throw new CHttpException(404,'The requested page does not exist.');

				}

				return $this-> model;

			}

	}

	/**

	 * Creates a new model.

	 * If creation is successful, the browser will be redirected to the 'view' page.

	 */

	public function actionCreate()

	{

		$model=new post;

		if(isset($_POST['post']))

		{

			$model->attributes=$_POST['post'];

			if($model->save())

				$this->redirect(array('view','id'=>$model->id));

		}


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

			'model'=>$model,

		));

	}


	/**

	 * Updates a particular model.

	 * If update is successful, the browser will be redirected to the 'view' page.

	 */

	public function actionUpdate()

	{

		$model=$this->loadModel();

		if(isset($_POST['post']))

		{

			$model->attributes=$_POST['post'];

			if($model->save())

				$this->redirect(array('view','id'=>$model->id));

		}


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

			'model'=>$model,

		));

	}

	

	

	protected function newComment($post)

		{

			$comment=new Comment;

			if(isset($_POST['Comment']))

			{

			$comment->attributes=$_POST['Comment'];

			if($post->addComment($comment))

				{

					if($comment->status==Comment::STATUS_PENDING)

					Yii::app()->user->setFlash('commentSubmitted','Thank you...');

					$this->refresh();

				}

			return $comment;

			}

		}


	/**

	 * Deletes a particular model.

	 * If deletion is successful, the browser will be redirected to the 'index' page.

	 */

	public function actionDelete()

	{

		if(Yii::app()->request->isPostRequest)

		{

			// we only allow deletion via POST request

			$this->loadModel()->delete();


			// if AJAX request (triggered by deletion via admin grid view), we should not redirect the browser

			if(!isset($_POST['ajax']))

				$this->redirect(array('index'));

		}

		else

			throw new CHttpException(400,'Invalid request. Please do not repeat this request again.');

	}


	/**

	 * Lists all models.

	 */

	public function actionIndex()

		{

			

			$criteria=new CDbCriteria(array(

			'condition'=>'status='.Post::STATUS_PUBLISHED,'order'=>'update time DESC','with'=>'commentCount',));

			if(isset($_GET['tag']))

			

			$criteria->addSearchCondition('tags',$_GET['tag']);

			

			$dataProvider=new CActiveDataProvider('Post', array(

			'pagination'=>array(

			'pageSize'=>Yii::app()->params['postsPerPage'],

			),

			'criteria'=>$criteria,

			));

			

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

			'dataProvider'=>$dataProvider,

			));

		}


	/**

	 * Manages all models.

	 */

	public function actionAdmin()

	{

		$dataProvider=new CActiveDataProvider('post', array(

			'pagination'=>array(

				'pageSize'=>self::PAGE_SIZE,

			),

		));


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

			'dataProvider'=>$dataProvider,

		));

	}


	/**

	 * Returns the data model based on the primary key given in the GET variable.

	 * If the data model is not found, an HTTP exception will be raised.

	 */

	

}






Did you miss to add an underscore in ‘update_time’?

/Tommy

hi

thnx for help .

but if i add the underscore for the update time …it will gave another error …

so it’s not the solution.

thnx …

I am convinced this is an SQL error, the error message from "1064 You have an error"… comes from MySQL.

Have you written that SQL command yourself? I suggest you install phpmyadmin and test SQL queries there before using them in your application. For me the query "update time desc limit 10" does not seem right. You must explain what you are trying to do with it.

i think your issue is in loadModel(), try rewriting your $condition and use $params in the call to findByPk().

potential issue:


$condition='status='.Post::STATUS_PUBLISHED .' OR status='.POST::STATUS_ARCHIVED;

id run your code and figure it out, but i’m on a business trip on a crappy laptop <_<

as the error says the problem is in the line




$criteria=new CDbCriteria(array(

                        'condition'=>'status='.Post::STATUS_PUBLISHED,

                        'order'=>'update time DESC','with'=>'commentCount',));



the "update time DESC" makes an invalid SQL command… (I did not do the blog tutorial)… check the table fields if you have the field "update" and/or the field "time"… if there are two fields update and time… than it should be "update, time DESC"… if there is only one… than delete the other…

One more thing…

in loadModel()… the model is loaded only for logged in users because findByPk() gets called only in the else part… ie. only for logged in users… (and it’s findByPk not findbyPk)





    if(Yii::app()->user->isGuest)

             $condition='status='.Post::STATUS_PUBLISHED .' OR status='.POST::STATUS_ARCHIVED;

    else

    {

            $condition='';

            $this->model=Post::model()->findbyPk($_GET['id'], $condition);

    }

    if($this-> model===null)

                 throw new CHttpException(404,'The requested page does not exist.');




should be




      if(Yii::app()->user->isGuest) 

      {

           $condition='status='.Post::STATUS_PUBLISHED.' OR status='.POST::STATUS_ARCHIVED;

      }

      else

      {

           $condition='';

      }

      $this->model=Post::model()->findByPk($_GET['id'], $condition);


      if($this-> model===null)

      {

          throw new CHttpException(404,'The requested page does not exist.');

      }



hi …

thnx everyone for helping me …this error is done …but when i fix it a new error appeared …

Property "comment.pendingCommentCount" is not defined.

Source File

C:\wamp\yii\framework\db\ar\CActiveRecord.php(106)

plz help …thnx in advance

hi …i also found out the solution of this problem …thnx all for help …

but when i enter to the post page …there is no posts …:S

i’m also having this error when accessing comments :

Relation "post" is not defined in active record class "comment".

why?

hello …

does someone knows what’s the error …

i can’t find a solution …plz help

maybe you could check the

public function relations() in your /protected/models/comment.php file

see whether are there


'post' => array(self::BELONGS_TO, 'Post', 'post_id'),

or not… Check the yii blog tutorial again see if you’ve missed something

thnx very much junxiong …this solved my problem …

The Blog Tutorial Skips some steps on the Comment Model - If you look at the Comment model in the blog demo that comes with the framework download you will see a the following function


/**

	 * @return integer the number of comments that are pending approval

	 */

	public function getPendingCommentCount()

	{

		return $this->count('status='.self::STATUS_PENDING);

	}

insert this into your comment model and change the line Comment::model()->pendingCommentCount to Comment::model()->getPendingCommentCount