CODEWITHSUNDEEP
zend-frameworkmysqlwhere-clause
Naveed
Naveed

Reputation: 42093

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

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

Upvotes: 3

Views: 15674

Answers (5)

user552849
user552849

Reputation: 1

Try :

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

put quotation marks around the value. It is working for me.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562358

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 <=>.

Upvotes: 4

Matt Ellen
Matt Ellen

Reputation: 11592

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

Upvotes: 2

SMka
SMka

Reputation: 3021

$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.

Upvotes: 6

emeraldjava
emeraldjava

Reputation: 11202

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);

Upvotes: 1

Related Questions