Model Query to pass as array to Controller

Yii 2.0 General Discussions
1

Say I have a model function such as

public function getUserListing()
{
    \Yii::$app->response->format = \yii\web\Response::FORMAT_RAW;
    $SQL = "SELECT u.id, u.FirstName, u.LastName
        FROM user u
        WHERE u.status = 10";
    $connection = Yii::$app->getDb();
    $command = $connection->createCommand($SQL);
    // \Yii::$app->response->format = \yii\web\Response::FORMAT_RAW;
    // return $command->rawSql; //use FORMAT_RAW!!!
    $queryResultsArray = $command->queryAll();
    
    return $queryResultsArray;
}

In my Controller, what is the proper way to retrieve this array to then be able to work with it?

I tried unsucessfully to do something like:

$LegsCountByStatus_Daily[] = new DashboardOperationsUsers();
$LegsCountByStatus_Daily[‘LegsBooked_day’] = $LegsCountByStatus_Daily[0]->getUserListing();
echo $LegsCountByStatus_Daily[‘LegsBooked_day’][0][49].’
’;

What I am looking for is to push the full array of ids and names to an array where the id is the key and the names are stored as an array. Then in the controller, I could simply pull up the data for a user based on their id (above I was trying to get the info for user id 49).

2

What you receive from $command->queryAll() is array of arrays.

[
  0=>['id'=>1, 'username'='peter'],
  1=>['id'=>2, 'username'='carol'],
  // ...
]

And you can receive it like this:

$listOfUsers = User::getUserListing();

Note that I preffer to have it static

Then you can easily loop it and modify at your will

$result = [];
$result[1]='abc'; // some faked previous values

foreach ($listOfUsers  as $user) {
  $result[$user['id']] = $user['username'];
}

I believe there are some built-in functions in PHP that could do this using one command.

PS: Many snippets and prepared solutions can be found on my Yii page: https://www.yiiframework.com/wiki/2552/yii-v2-snippet-guide

1 Like
3

Thank you.

So I had to build the array myself and then it worked just fine.