Laravel 5.1 DB:select toArray()

Question

I have a large SQL statement that I am executing like so:

$result = DB::select($sql);

For example

$result = DB::select('select * from users');

I'd like the result to be an array - but at the moment it returns a structure like so, an array with Objects...

Array
(
    0 => stdClass::__set_state(array(
        'id' => 1,
        'first_name' => 'Pavel',
        'created_at' => '2015-02-23 05:46:33',
    )),
    1 => stdClass::__set_state(array(
        'id' => 2,
        'first_name' => 'Eugene',
        'created_at' => '2016-02-23 05:46:34',
    )),
...etc...

)

Jose Mujica · Accepted Answer · 2016-05-30 05:02:35Z

59

Seems like you need to cast the stdClass Objects as Array so you can have the structure you are looking for

$result = array_map(function ($value) {
    return (array)$value;
}, $result);

answered May 30, 2016 at 5:02

Jose Mujica

7281 gold badge6 silver badges10 bronze badges

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

3 Comments

Yevgeniy Afanasyev Over a year ago

Good Idea! Are you sure Eloquent does not provide a function to do it?

huuuk Over a year ago

Eloquent does it, but you are using Query Builder , not Eloquent

Bishal Paudel Over a year ago

$result = array_map(fn($x) => (array)$x, $result);

Marcin Nabiałek · Accepted Answer · 2017-05-24 04:48:03Z

34

The best solutions looking at performance would be changing method how data is grabbed from database:

// get original model
$fetchMode = DB::getFetchMode();
// set mode to custom
DB::setFetchMode(\PDO::FETCH_ASSOC);
// get data
$result = DB::select($sql);
// restore mode the original
DB::setFetchMode($fetchMode);

Obviously if you want to get all the data as array, it will be enough to change in config/database.php from:

'fetch'       => PDO::FETCH_CLASS,

into

'fetch'       => PDO::FETCH_ASSOC,

and then running only:

$result = DB::select($sql);

will return you multidimensional array instead of array of objects

edited May 24, 2017 at 4:48

answered May 30, 2016 at 18:50

Marcin Nabiałek

112k43 gold badges271 silver badges299 bronze badges

6 Comments

Yevgeniy Afanasyev Over a year ago

This is really advanced. Thanks.

Jose Mujica Over a year ago

I've found this answer related to this topic, I think this can improve what @Marcin suggest. stackoverflow.com/a/19369644/2848140

Ryan Over a year ago

I get this error in Laravel 5.4: Call to undefined method Illuminate\Database\MySqlConnection::getFetchMode().

Javed Iqbal Over a year ago

I got this error in Laravel Framework 8.26.1 -- Call to undefined method Illuminate\Database\MySqlConnection::setFetchMode()

Carmageddon Over a year ago

This solution no longer works after Laravel 5.4 apparently.

|

Yevgeniy Afanasyev · Accepted Answer · 2019-12-23 00:03:00Z

13

Here is another approach that is worth to be mentioned:

$resultArray = json_decode(json_encode($result), true);

edited Dec 23, 2019 at 0:03

answered May 30, 2016 at 5:44

Yevgeniy Afanasyev

42k30 gold badges186 silver badges211 bronze badges

1 Comment

Benyamin Limanto Over a year ago

This's only the way around other than listen on even lister laravel,so sad lah.

Alexey Mezenin · Accepted Answer · 2016-05-30 05:34:37Z

11

toArray() method converts collection to an array. Models are converted to arrays too:

The toArray method converts the collection into a plain PHP array. If the collection's values are Eloquent models, the models will also be converted to arrays

So, I guess you're doing something wrong. If you want more help, please update you question with all related code.

Also, only Eloquent collections (models) have toArray() method.

To use this method, use Eloquent:

$result = \App\User::all()->toArray();

edited May 30, 2016 at 5:34

answered May 30, 2016 at 4:51

Alexey Mezenin

164k27 gold badges309 silver badges287 bronze badges

9 Comments

aimme Over a year ago

$result = DB::select($sql)->toArray();

Yevgeniy Afanasyev Over a year ago

exception 'Symfony\Component\Debug\Exception\FatalErrorException' with message 'Call to a member function toArray() on array' in ...

Alexey Mezenin Over a year ago

@YevgeniyAfanasyev, I guess that's the answer for aimee's code. Of course it will not work. Please show your code and I'll show you how you can convert it to an array.

Yevgeniy Afanasyev Over a year ago

I extended the question, I put example there. Thanks.

Alexey Mezenin Over a year ago

@YevgeniyAfanasyev, I've updated my answer with working Eloquent code equivalent to $result = DB::select('select * from users');

|

Tobias Sette · Accepted Answer · 2017-07-13 02:52:43Z

4

For laravel/lumen 5.4 (that uses illuminate/database 5.4):

$pdo = DB::getPdo();
$statement = $pdo->prepare($yourQuery);
$statement->setFetchMode(\PDO::FETCH_ASSOC);
$statement->execute();
$results = $statement->fetchAll();

Or listen to Illuminate\Database\Events\StatementPrepared event: Laravel 5.4 - How to set PDO Fetch Mode?

edited Jul 13, 2017 at 2:52

answered Jul 13, 2017 at 2:44

Tobias Sette

3062 silver badges14 bronze badges

Comments

Mourad MAMASSI · Accepted Answer · 2018-03-07 13:58:40Z

4

you can try this

DB::table('commune')->where('Commune','<>', 'ND')->orderBy('Commune')->get()->pluck('Commune');

answered Mar 7, 2018 at 13:58

Mourad MAMASSI

8851 gold badge12 silver badges15 bronze badges

Comments

Mahmoud Abdelsattar · Accepted Answer · 2020-01-10 16:33:02Z

1

You can make a collection in order to use toArray(), very simple approach:

$result = DB::table('users')->get();
$data = collect($result)->map(function($x){ return (array) $x; })->toArray();

answered Jan 10, 2020 at 16:33

Mahmoud Abdelsattar

1,5471 gold badge20 silver badges36 bronze badges

1 Comment

Yevgeniy Afanasyev Over a year ago

The accepted answer uses the same concept of array_map-ing, but without extra conversion to and out from collection. I'm afraid you have not contributed much.

GuruBob · Accepted Answer · 2022-12-12 20:37:31Z

0

Good to pick through the answers here to understand the reasons. Here's my version that I settled on:

$users = collect($users) // turn the top level array into a collection
    ->map(fn($user) => (array) $user) // cast each object to an array
    ->toArray(); // convert top level collection back to an array

You had an array of objects. Now you will have an array of arrays. Note that this doesn't do a recursive conversion of the internal array, but since it's only one level deep you're fine.

answered Dec 12, 2022 at 20:37

GuruBob

1,0341 gold badge12 silver badges22 bronze badges

Comments

huuuk · Accepted Answer · 2016-05-30 05:17:16Z

-1

Try this

$result = DB::select($sql);
$arr = [];
foreach($result as $row)
{
    $arr[] = (array) $row;
}

edited May 30, 2016 at 5:17

answered May 30, 2016 at 4:59

huuuk

4,8052 gold badges22 silver badges28 bronze badges

4 Comments

Yevgeniy Afanasyev Over a year ago

This is wrong. I got: local.ERROR: exception 'Symfony\Component\Debug\Exception\FatalErrorException' with message 'Call to a member function get() on array'

Yevgeniy Afanasyev Over a year ago

UPDATE exception 'ErrorException' with message 'call_user_func_array() expects parameter 1 to be a valid callback, class 'Illuminate\Database\MySqlConnection' does not have a method 'selectRaw''

Alexey Mezenin Over a year ago

@huuuk, select accepts not only column names: $users = DB::select('select * from users where active = ?', [1]); - Also, your examples doesn't work. To make last one get something, you need to remove ->get()

huuuk Over a year ago

@AlexMezenin I'm sorry, I was wrong. You are right. I'm a bit confused wit Eloquent

Parvez Rahaman · Accepted Answer · 2016-06-01 10:35:23Z

-1

You can do this way..

At the top

use DB;
use PDO;

--------------------

DB::setFetchMode(PDO::FETCH_ASSOC); // Set the fetch mode as array

$result = DB::select('select * from users');

For example, now you can get result like this way.

$first_email = $result[0]['email'];

Now you can change to default fetch mode.

DB::setFetchMode(PDO::FETCH_CLASS);

answered Jun 1, 2016 at 10:35

Parvez Rahaman

4,3973 gold badges24 silver badges39 bronze badges

Comments

Atika · Accepted Answer · 2021-09-25 02:14:02Z

-1

One tricky and simple way :

//Got array in stdClass converting to JSON
$temp1= json_encode($result);


//JSON to Array
$temp2= json_decode($temp1,1);`

May be it will help you

edited Sep 25, 2021 at 2:14

Atika

1,0672 gold badges8 silver badges18 bronze badges

answered Aug 30, 2018 at 9:29

Vishal

1291 gold badge1 silver badge10 bronze badges

1 Comment

Yevgeniy Afanasyev Over a year ago

I answered the same way on May 30 '16 at 5:44. You are a bit late. Please read existing answers before posting yours.

Chandan Sharma · Accepted Answer · 2019-11-29 06:35:34Z

-2

You can use this one. It's worked for me.

$query = DB::table('user')->select(['id','name'])->get();
$userlist = $query->toArray();

answered Nov 29, 2019 at 6:35

Chandan Sharma

2,5551 gold badge26 silver badges23 bronze badges

Comments

Mohsen · Accepted Answer · 2018-02-08 10:43:36Z

-4

Easiest way is using laravel toArray function:

public function toArray()
{
    return array_map(function ($value) {
        return $value instanceof Arrayable ? $value->toArray() : $value;
    }, $this->items);
}

edited Feb 8, 2018 at 10:43

answered Feb 8, 2018 at 10:18

Mohsen

4,2451 gold badge34 silver badges33 bronze badges

1 Comment

Yevgeniy Afanasyev Over a year ago

toArray() is method, not just function. And it converts collection to an array. I don't have a collection for starters, I have an array of objects. Only because DB::select($sql); returns array of objects. So your answer is not help.

Collectives™ on Stack Overflow

Laravel 5.1 DB:select toArray()

13 Answers 13

3 Comments

6 Comments

1 Comment

9 Comments

Comments

Comments

1 Comment

Comments

4 Comments

Comments

1 Comment

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

13 Answers 13

3 Comments

6 Comments

1 Comment

9 Comments

Comments

Comments

1 Comment

Comments

4 Comments

Comments

1 Comment

Comments

1 Comment

Linked

Related