current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 0 down vote favorite

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 ?

share|improve this question
    
Can't you just fix your database to remove the corrupted row? – Thijs Riezebeek Jun 15 at 11:10
    
@ThijsRiezebeek it's not corrupted, city values are not required – Jonuux Jun 15 at 12:22
    
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. – Marco Aurélio Deleu Jun 20 at 22:38
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.