Zend: How to use 'not equal to' in WHERE clause?

Question

I am using following zend code to select all data from a table where verified=1 and it is working for me.

$table = $this->getDbTable();
$select = $table->select();
$select->where('verified = 1');
$rows = $table->fetchAll($select);

No I want to select all data from that table where verified is not equal to '1'. I have tried the following ways but it is not fetching data.

$select->where('verified != 1');
$select->where('verified <> 1');
$select->where('verified != ?', 1);

Data structure for 'verified' column:

Field: verified
type: varchar(45)
Collation: utf8_bin        
NULL: Yes   
Default: NULL

Any idea that how to use 'not equal to' operator in WHERE clause in Zend? Thanks

Answer 1

$select->where('verified != ?', 1);

Real worls query example:

    $query = $this->getDb()->select();
    $query->from('title', array('title_id' => 'id', 'title', 'production_year', 'phonetic_code'))
            ->where('kind_id = 1')
            ->where('title = ?', trim($title))
            ->where('production_year != ?', '2009')
            ->limit(1)
            ;

Selects movies info from IMDB database. Works fine.

Answer 2

Can you show us the table structure for the table you are querying? Is the column verified an int or string? Also try printing the SQL statement that ZEND builds, see the echo line below.

$table = $this->getDbTable();
$select = $table->select();
$select->where('verified = 1');
echo sprintf("sql %s",$select);
$rows = $table->fetchAll($select);

Answer 3

Since your column is a varchar perhaps try where verified != '1' or verified is null

Answer 4

MySQL supports a custom operator <=> which returns true if the operands are equal or both null. It returns false if they are different, or if one operand is null.

$select->where('verified <=> 1');

This operator is non-standard. Standard SQL has syntax: IS NOT DISTINCT FROM that works just like MySQL's <=>.