473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to use NULL in WHERE clause

I am trying to select rows from my database where the app field is either NULL, n, or empty. I know that the NULL and the empty is redundant, but it is just in case I change the field properties it won't break the code. Why does app=NULL not bring up all of the rows with that property?

Expand|Select|Wrap|Line Numbers
  1.    function pickUpApprove(){
  2.  
  3.       $q = "SELECT * FROM ".TBL_PICKUP." 
  4.             WHERE app = NULL OR app = ''
  5.             OR app = 'n'";
  6.       $result = mysql_query($q, $this->connection);
  7.       return $result;
  8.    }
Jun 5 '07 #1
3 6827
bartonc
6,596 Expert 4TB
I am trying to select rows from my database where the app field is either NULL, n, or empty. I know that the NULL and the empty is redundant, but it is just in case I change the field properties it won't break the code. Why does app=NULL not bring up all of the rows with that property?

Expand|Select|Wrap|Line Numbers
  1.    function pickUpApprove(){
  2.  
  3.       $q = "SELECT * FROM ".TBL_PICKUP." 
  4.             WHERE app = NULL OR app = ''
  5.             OR app = 'n'";
  6.       $result = mysql_query($q, $this->connection);
  7.       return $result;
  8.    }
NULL != "". If you are expecting the test app=NULL to match rows with empty string, you are mistaken. NULL is a special value that means that the value is not defined. An empty string (often used as a default value) is a defined value.
Jun 6 '07 #2
pradeep kaltari
102 Expert 100+
I am trying to select rows from my database where the app field is either NULL, n, or empty. I know that the NULL and the empty is redundant, but it is just in case I change the field properties it won't break the code. Why does app=NULL not bring up all of the rows with that property?

Expand|Select|Wrap|Line Numbers
  1.    function pickUpApprove(){
  2.  
  3.       $q = "SELECT * FROM ".TBL_PICKUP." 
  4.             WHERE app = NULL OR app = ''
  5.             OR app = 'n'";
  6.       $result = mysql_query($q, $this->connection);
  7.       return $result;
  8.    }
Hi,
You cannot equate any column to NULL. Instead, you need to use the IS operator. If you are looking for rows which have NULL for "app" column then you can use the following:
Expand|Select|Wrap|Line Numbers
  1. ...
  2. WHERE app IS NULL ...
  3.  
I hope this helps you.

Regards,
Pradeep
Jun 6 '07 #3
Atli
5,058 Expert 4TB
I've edited the thread's title to better describe it's contents.

Moderator
Jun 6 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Kevin Frey | last post by:
Hello, I have a table which contains some nullable columns. I want to write a single query, which can be prepared (ie. prepared statement), that can handle null or non-null values for the where...
5
by: Geremy | last post by:
Hi Consider two tables id1 code1 ----------- ----- 1 a 2 b 3 c id2 code2 value
3
by: olanorm | last post by:
I have a query where one or more of the columns returned is a result from a subquery. These columns get their own alias. I want to filter out the rows containing NULL from the subqueries but it...
5
by: Shridhar Daithankar | last post by:
Hello All, I was just updating a table in oracle9.2 by hand and bumped into this. Following seems to be the valid syntax in oracle. Update foo set somefield=NULL where somefield >9; Now I...
17
by: John | last post by:
Hi I have a datadapter with the following SQL; SELECT ID, Company, Status, CompanyType FROM Companies WHERE (@Status IS NULL or @Status = Status) When I try to fill like...
9
by: John Sidney-Woollett | last post by:
Hi I'm building a web app where changes to customer orders are logged in the following table, and I worried about the time that it will take to locate records that need further...
8
by: A. Anderson | last post by:
Howdy everyone, I'm experiencing a problem with a program that I'm developing. Take a look at this stack report from GDB - #0 0xb7d782a3 in strlen () from /lib/tls/i686/cmov/libc.so.6 #1 ...
9
by: Yitzak | last post by:
Hi spent a few hours on this one wrote a query that joined on results of 2 other queries. Qry3 using Qry1 and Qry2 When I used Qry1.FasText <cstr(Qry2.FasInteger) in the where clause - got...
4
by: nicklpayne | last post by:
I have a search form with a subform that holds the results. Three text boxes are search criteria: First Name Last Name Address The criteria in the subform parameter query are like below: Like...
9
by: Jonathan Wood | last post by:
I've written a stored procedure and would like to filter the results returned based on the argument. It works so far, but I'd also like to allow this argument to be null. I know I can rewrite my...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.