How to write database/SQL query in Yii2, with and without a model

Question

I have a table with multiple column and I want to return a column name using another column name as search criteria. How do I achieve this in yii2?

Below is sample code, normal sql should be:

$name = SELECT type_name FROM ProductTable WHERE type_id = 1;
echo $name;

This should return the value of the column type_name where the value of the column type_id equals 1. I tried this, but it doesn't work

$type_name = ProductTable::find()->where(['type_id' =>$model->type_id]);

$type_name = Product::find(['type_name'])->where(['type_id' =>$model->type_id]);

I also tried this, but I guess it was wrong

I hope my question is clear enough and any help will he appreciated

rakhmatov · Accepted Answer · 2016-04-15 17:29:33Z

3

and u could also use createCommand!

$name = \Yii::$app->getDb()->createCommand("SELECT type_name FROM ProductTable WHERE type_id=:typeId", ['typeId'=>$model->type_id])->queryAll();

answered Apr 15, 2016 at 17:29

rakhmatov

3874 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

particleflux · Accepted Answer · 2016-04-14 19:13:26Z

For a general introduction to Yii2's ActiveRecord, see the guide: http://www.yiiframework.com/doc-2.0/guide-db-active-record.html

If you want the complete row and have a model, you're just missing a one():

Product::find()->where(['type_id' =>$model->type_id])->one();

If you do have a Model defined and just want a single value, try:

Product::find()->select('type_name')->where(['type_id' =>$model->type_id])->scalar();

Which basically generates an ActiveQuery via the model, and changes it to return only the first column in the first row of matched results.

If you do NOT have a model, you could also generate a normal query without ActiveRecord usage (http://www.yiiframework.com/doc-2.0/yii-db-query.html)

$name = (new Query())->select('type_name')
    ->from('ProductTable')
    ->where(['type_id' =>$model->type_id])
    ->scalar();

wogsland · Accepted Answer · 2016-04-14 20:53:43Z

1

I assume you generated ProductTable by using Gii module.

Also, if type_id column is a primary key:

$product = ProductTable::findOne($model->type_id);
if($product !== null) { $product->typeName /*... read value ...*/}

or to get all records

$products = ProductTable::findAll($model->type_id); //match all records.

for any other column use the following syntax instead:

$product = ProductTable::findOne(['type_id' => $model->type_id]);

edited Apr 14, 2016 at 20:53

wogsland

9,54121 gold badges63 silver badges99 bronze badges

answered Apr 14, 2016 at 20:51

Viktar Barysevich

361 bronze badge

Comments

Naisa purushotham · Accepted Answer · 2016-04-15 12:32:07Z

0

Use following code to get type_name

 $PTable=ProductTable::find()->select('type_name')->where(['type_id' =>$model->type_id])->one();
    echo $PTable->type_name;

answered Apr 15, 2016 at 12:32

Naisa purushotham

91310 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to write database/SQL query in Yii2, with and without a model

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related