Trying to migrate SQL commands from plain PHP using Yii2

Question

I'm trying to change my PHP statement to Yii2. Here's the original code:

$sql = "select id from users where member_type='d'";
$max = @mysql_num_rows($r=mysql_query($sql));
for($i=0;$i<$max;$i++){
    $demo2=mysql_fetch_assoc($r);
    some_functions($demo2['id'], 'something');
}

I'm new to Yii2 framework and trying to convert it to Yii2 but have no idea how. I'm doing this function under a modal file.

Here's what I can do at best:

$max= Yii::$app->dbFxprimus->createCommand("select count(id) from ". $this->tableName() ." where member_type='d'")->queryAll();
for($i2=0;$i2<$max;$i2++){
    $demo=mysql_fetch_assoc($max); //this is definitely wrong, help me to fix this, I don't know what is the Yii2 function for this.
    some_function($demo['id'], 'something');
}

Can anyone help me to fix this?

arogachev · Accepted Answer · 2015-04-23 08:45:22Z

6

First of all you need to build query and get the results.

1) If you don't have model, you can do it like that:

use yii\db\Query;

...

$users = (new Query())
    ->select('id')
    ->from('users')
    ->where(['member_type' => 'd'])
    ->all()

Alternatively you can build query with Yii::$app->db->createCommand(), but this is better looking.

2) If you have model, and want get results as associative arrays:

use app\models\User;

...

$users = User::find()
    ->select('id')
    ->where(['member_type' => 'd'])
    ->asArray()
    ->all();

Then just loop through and apply your function:

foreach ($users as $user) {
    some_function($user['id'], 'something');
}

And you need replace some_function to call of some method class, because it's OOP.

P.S. Take your time and read the docs. It's covered there.

edited Apr 23, 2015 at 8:45

answered Apr 23, 2015 at 8:33

arogachev

33.6k8 gold badges121 silver badges118 bronze badges

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

Comments

Pavel Bariev · Accepted Answer · 2015-04-23 08:33:19Z

1

$ids = Yii::$app->dbFxprimus->createCommand("select id from ". $this->tableName() ." where member_type='d'")->queryColumn();
foreach($ids as $id){
    some_function($id, 'something');
}

And if '$this' is an ActiveRecord instance:

$ids = self::find()
    ->select(['id'])
    ->where(['member_type' => 'd'])
    ->column();
foreach($ids as $id){
    some_function($id, 'something');
}

edited Apr 23, 2015 at 8:33

answered Apr 23, 2015 at 8:29

Pavel Bariev

2,6351 gold badge20 silver badges22 bronze badges

9 Comments

arogachev Over a year ago

Code only answers are not welcomed on Stack Overflow. Please add some explanation.

nodeffect Over a year ago

May I know if I'm not using the "foreach", I want to use the "for loop", what Yii2 command can I use ?

Pavel Bariev Over a year ago

There is no Yii command for common php functions. You still use for($i=0;$i<(count($ids)-1);$i++) and so on.

arogachev Over a year ago

I recommend spend some time and read the docs. And even for plain PHP your example is not optimum.

Asped Over a year ago

actually the answer from @arogachev is better, as he shows you how to use a Model. In your case this is the Yii way to go. If you want to learn Yii, forget about writing plain SQL

|

Asped · Accepted Answer · 2015-04-23 10:35:28Z

1

The Yii way to go would be to create a DB Model first, and then use it. It is very simple to load all data from a table using models. Forget about writing plain SQL - if not absolutly needed - and use the ORM approach of Yii.

'User' is the model connected to the DB table. It looks similar to this (there may be a few more functions needed)

namespace app\models\db;

class User extends \yii\db\ActiveRecord {

    public static function tableName()
    {
        return 'users'
    }
    ...
}

Check the documentation on how to use models and how to generate them automatically, so you don't have to write everything by yourself. Then you can use the model to retrieve data easily:

$users = User::find()->where(['member_type' => 'd'])->all();

you do not need to concert the result into an array, as you can easily use the OOP way of Yii:

foreach ($users as $user) {
    some_function($user->id, 'something');
}

This is much easier than converting to arrays, and using for instead of foreach, and getting the count of rows... If you for some other reason would need to get the count of objects returned, you can now use only count($users)

As simple as that. Maybe you should also check the Introdcution Guide of Yii2 to get an overview on how to use it.

edited Apr 23, 2015 at 10:35

answered Apr 23, 2015 at 9:18

Asped

3,1134 gold badges34 silver badges54 bronze badges

1 Comment

nodeffect Over a year ago

Actually, I'm lost on the User::find().... mind explaining that ? is 'User' a class inside the model?

Collectives™ on Stack Overflow

Trying to migrate SQL commands from plain PHP using Yii2

3 Answers 3

Comments

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related