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()
->select('*')
->from('user')
->where('name=:name', array(':name'=>$name))
->queryAll();
Questions:
[list=1]
[*]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.name, user.firstname FROM user
JOIN location ON user.location = location.id
WHERE date > '2012-01-01' AND name LIKE '%toto%'
GROUP BY job
ORDER BY date DESC LIMIT 100
Which method is the easy + best to do?
[/list]