Mysql Request Help

I’m new with creating mysql request in Yii. So here is some few examples, I’d like to know all the possible ways to write a request:

Ex: SELECT * FROM user WHERE name=‘toto’

$sql = "SELECT * FROM user WHERE name=:name";

$command = Yii::app()->db->createCommand($sql);

$command->bindParam(":name", 'toto',PDO::PARAM_STR);

$result = $command->queryAll();

$result = User::model()->findAll(array(

   'condition' => "name='toto'"


$criteria = new CDbCriteria;

$criteria->condition = 'name=:name';

$criteria->params = array(':name'=>'toto');

$result = User::model()->findAll($criteria);

$criteria = new CDbCriteria;

$criteria->compare('name', $name);

$result = User::model()->findAll($criteria);

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



    ->where('name=:name', array(':name'=>$name))




[*]Sometime I see people use ->prepare. Is it necessary?

[*]Do I miss any possible methods to create a query?

[*]In the 2nd and 4th methods, do I need to escape the query with mysql_real_escape or is there a yii style way to escape it?

[*]Which is the best way to create a sql query and when to use it?

[*]For a complex query such as

SELECT DISTINCT, user.firstname FROM user

JOIN location ON user.location =

WHERE date > '2012-01-01' AND name LIKE '%toto%'



Which method is the easy + best to do?


The best method is just the one you feel comfortable with.

You don’t need to use prepare explicitly, it is done before a query is executed and results are returned.

You also don’t need to escape values when binding them as params, since the query contains only a placeholder, not the real value. The value goes straight through the PDO to the database.