Yii Framework Forum: Mysql Request Help - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Mysql Request Help Rate Topic: -----

#1 User is offline   FlyingAngel 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 16-December 11

Posted 10 October 2013 - 02:55 PM

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:

  • 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?

0

#2 User is offline   nineinchnick 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 622
  • Joined: 12-September 11
  • Location:Bialystok, Poland

Posted 10 October 2013 - 03:25 PM

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.
Don't be a dick.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users