Query statement question - NOT EQUAL - Databases forum at WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

Query statement question - NOT EQUAL

Modern Merlin

2:53 am on Sep 15, 2008 (gmt 0)

Not sure if this is where i post this or not but seeing as how I am doing this from PHP here I am :)

Basically I have your run of them mill $query statement and I need to know how to modify it to pull only the data I want from the database. I have looked for this in Google and on other various PHP sites. Though to be honest I don't even know what category it would fall under in the PHP SQL Syntax.

$query = "SELECT chr_name FROM table " or die ("This is the query error: ".mysql_error());

What I really want it to do is SELECT chr_name FROM table WHERE some_field NOT EQUAL TO '2'

The part I cant find is the NOT EQUAL part. Is it != ?

Thanks for any help!

dhruvasagar

6:53 am on Sep 15, 2008 (gmt 0)

SELECT chr_name FROM table WHERE NOT chr_name='2'

ZydoSEO

12:47 pm on Sep 15, 2008 (gmt 0)

Or with most DBs you can also use:

SELECT chr_name FROM table WHERE chr_name <> '2'

I think this reads a little easier than NOT chr_name='2'.

rocknbil

8:45 pm on Sep 15, 2008 (gmt 0)

Both of those should work, but since it's a numeric field, != should also work with mysql. That is, if you're storing numeric data in a varchar or text field, it might not work because applying = or != to those field types would evaluate as zero UNLESS that field contains numbers. If it's an int, tinyint, or decimal field, != should work.

Some examples: title is varchar with text values, both with and without numbers, num.fld is int, dec.fld is decimal:

id ¦ title ¦ numeric_field ¦ decimal_field
.....

select count(*) from table;
-> 468 rows

select count(*) from table where title = 0;
-> 454 rows

select count(*) from table where title != 0;
-> 14 rows
(Example title from these rows: "1000 ways to Wok Your Dog")

select count(*) from table where numeric_field != 0;
-> 468

select count(*) from table where decimal_field != 0;
-> 466

select count(*) from table where decimal_field = 0;
-> 2
(0.00 evaluates as zero)

I've always used <>, it works for pretty much all field types, which is probably the best reason to stick with it.

Another thing to be cautious of is if you create fields without setting not null:

create table . . . . field_name int(11) not null . . ..

. . . because now you also need to test for null, <> 0 won't always give expected results.

Modern Merlin

9:11 pm on Sep 15, 2008 (gmt 0)

Thank you everyone who responded!

!= worked just fine!

Thanks again!