Laravel dropdown list without empty values

Question

I'm creating dropdown list from all possible cities in database, all cities are distincted.

$city = DB::table('contacts')->distinct('city')->lists('id', 'city');

^ Extracts all possible cities, however it also extract empty string ['' => '95].

How I can forbid retrieving empty city ?

I tried using $city = DB::table('contacts')->whereNotNull('city')->distinct('city')->lists('id', 'city'); But didn't helped. My code looks like this now.

 $city = DB::table('contacts')->distinct('city')->lists('id', 'city');
 unset($city['']);
 array_unshift($city, 'Select city');

How can I improve it ?

• Thijs Riezebeek: Can't you just fix your database to remove the corrupted row?
• Jonuux: @ThijsRiezebeek it's not corrupted, city values are not required
• Marco Aurélio Deleu: Did you try ->where('city', '<>', '')? From the looks of it, whereNotNull is not working because the value is '' (empty string) and that is, indeed, not null.

Answer 1

I'm assuming that you are using the whereNotNull() because in addition to allowing empty strings, you allow null values?

That being said, you could always modify your code to use the specific where() clause. This can take an array as input and you can specify both the empty string and NULL.

$city = DB::table('contacts')->distinct('city')->lists('id', 'city')->where(function($query) {
 $query->where('contacts.city', '!=', '')
 ->orWhere('contacts.city', '!=', null);
})

There's a very good SO post here about multiple where clause queries.