Get specific column value from MySql in Laravel DB select function

Question

How to I get a specific column value after fetching the results from MySql in Laravel DB select function

Below is my php code.

$list = DB::select('SELECT DISTINCT a.pincode FROM table_name nvs 
        JOIN areas a on nvs.area_id = a.id 
        where nvs.city_id = ?;', [$area_id]);

Below is the results for above query.

[
  {
    "pincode": "560005"
  },
  {
    "pincode": "560006"
  }
]

I need only pincode value not as key value pair. So i tried with the below code.

return array_column($list->toArray(), 'pincode');

It gives Call to a member function toArray() on array.

How can I get only values without using foreach loop.

Answer

I used the below code

$pincode_list = DB::table('table_name as nvs')
            ->join('areas as a', 'nvs.area_id', '=', 'a.id')
            ->select('a.pincode')
            ->where('nvs.city_id', '=', $area_id)
            ->distinct('a.pincode')->pluck('pincode')->toArray();

Result was :

[
    "560067",
    "560035"
]

use pluck for single values

Bugfixer
– Bugfixer

2017-01-03 12:02:34 +00:00
Commented Jan 3, 2017 at 12:02 — Bugfixer
– Bugfixer, Commented Jan 3, 2017 at 12:02

Alexey Mezenin · Accepted Answer · 2017-01-03 11:53:36Z

5

Best way is to use Query Builder or Eloquent and use this instead of get():

->pluck('pincode')->toArray();

answered Jan 3, 2017 at 11:53

Alexey Mezenin

164k27 gold badges309 silver badges287 bronze badges

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

Comments

Prafulla Kumar Sahu · Accepted Answer · 2019-04-28 13:55:31Z

1

I think the query should be like

 $area_ids = DB::table('table_name')
        ->join('areas', 'nvs.area_id', '=', 'table_name.id')
        ->select('table_name.pincode')
        ->where('nvs.city_id', '=', $area_id)
        ->distinct()->get();

and if you are getting it as object and want it to be array you can use toArray() here like $area_ids = $area_ids->toArray(); can you please test it and let me know the new status.

edited Apr 28, 2019 at 13:55

answered Jan 3, 2017 at 11:49

Prafulla Kumar Sahu

9,75911 gold badges78 silver badges116 bronze badges

3 Comments

Prafulla Kumar Sahu Over a year ago

@Sharath check laravel.com/docs/5.3/queries#joins if you are writing core PHP PDO query why you are usiing Laravel ?

Sharath Over a year ago

Now i am using laravel functions instead of PDO query

Prafulla Kumar Sahu Over a year ago

@Sharath if you found my answer helpful, you should have upvoted it .

Abhay Maurya · Accepted Answer · 2017-01-03 11:45:24Z

0

$all_pincodes = array_values ($list);

But I suggest using Laravel Eloquent as you are anyway using Laravel so it's better to use in-build functionalities to ease the process.

answered Jan 3, 2017 at 11:45

Abhay Maurya

12.3k9 gold badges50 silver badges67 bronze badges

Comments

xAoc · Accepted Answer · 2017-01-03 11:54:04Z

0

Using your variant:

\DB::setFetchMode(\PDO::FETCH_ASSOC);
$data = \DB::select($yourQuery);
$pincodes = array_pluck($data, 'pincode')
\DB::setFetchMode(config('database.fetch')); //return fetch to default

Using more fluent way:

  $pincodes = \DB::table('table')->pluck('pincode')->toArray(); //do not forget about wheres and joins in this query.

I would recommend you second variant because this is more clear way to get result what you need.

answered Jan 3, 2017 at 11:54

xAoc

3,6183 gold badges25 silver badges36 bronze badges

Collectives™ on Stack Overflow

Get specific column value from MySql in Laravel DB select function

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related