Yii Cdbcommand Create Query

Yii CDbCommand create query

Since you don't have access to andWhere, which would make life much simpler, you have to express the parameters with where like this:

$command->where(array(
    array('and', 
          'websiteId=:websiteId',
          array('and', 
                'timeAdded>=:dateStart',
                 array('and',
                       // ...

), $parameters);

This is done so that you can at some point use the proper array('in', 'recipientId', $values) syntax to produce the IN(...) SQL.

However, this is ugly and difficult to manage. As long as all the conditions are simply joined together with AND you can construct the data structure programmatically from a saner data representation like this (in effect this is a workaround for the missing andWhere):

$conditions = array(
    'websiteId=:websiteId',
    'timeAdded>=:dateStart',
    'timeAdded<=:dateEnd',
    array('in', 'recipientId', $broadcasterIds),

);

$where = null;
foreach ($conditions as $condition) {
    if (!$where) {
        $where = $condition;
    }
    else {
        $where = array('and', $where, $condition);
    }
}

$command->where($where, $parameters);

For more information on why this way of expressing things has to be used you can refer to the documentation for CDbCommand::where.

get query results from cdbcommand Yii

public function getQuotes()
        {            
            $data = Yii::app()->db->createCommand('Select fromm from city_fare_final');            
            $data->queryRow();            
            return $data ;
        }

Your getQuotes() return Object of class CDbCommand:

+ You returned $data in the function instead of $data->queryRow(). 

By the way, you cannot use echo for array data.
The below example is used for fetching data from DB to view by using DAO with Yii: I suppose you have Person model and Person controller

In your Person model:

function getData() {
    $sql = "SELECT * from Person";

    $data = Yii::app()->db
        ->createCommand($sql)
        ->queryAll();
    return $data;
}

In your controller:

function index(){
    $data =  Person::model()->getData();

    $this->render('your_view',array(
    'data'=>$data,
    ));
}

In your view: you can foreach your data to echo items in the array data:

<?php foreach($data as $row): ?>
     //show something you want 
     <?php echo $row->name; ?>
<?php endforeach; ?>

Does CDbcommand method queryAll() in yii return indexed entries only?

To make an array of data usable in a dropdown, use the CHtml::listData() method. If I understand the question right, this should get you going. Something like this:

$command = Yii::app()->db->createCommand();
$userArray = $command->select('tid, type')->from('tbl_u_type')->queryAll();
echo CHtml::dropdownlist('my_dropdown','',CHtml::listData($userArray,'tid','type'));

You can also do this with the Model if you have one set up for the tbl_u_type table:

$users = UType::model()->findall();
echo CHtml::dropdownlist('my_dropdown','',CHtml::listData($users ,'tid','type'));

I hope that gets you on the right track. I didn't test my code here, as usual, so watch out for that. ;) Good luck!

What is the colon used for in yii CDbCommand query parameters?

Its safe to apply Leading colon before parameter name, because its required, but if you will miss this leading colon , then PHP internally append colon before parameter name.

So best way is this

where('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2))

Thanks

How to create table in yii with use createcommand or other?

Assuming you've named your database connection string db (in protected/config/main.php) as follows,

'components'=>array(
    'db'=>array(
    ),
),

you should be able to create a table using the following command:

$sqlQuery = "CREATE TABLE IF NOT EXISTS pet (name VARCHAR(20), owner VARCHAR(20))";
$sqlCommand = Yii::app()->db->createCommand($sqlQuery);
$sqlCommand->execute();

You should also be able to replace the first line with any SQL statement and execute it successfully. If you want to query for data, you will need to use queryRow, queryColumn, or queryScalar (as defined in the documentation).

Hope this helps!

---

Related Topics