Solved

Why does this query not work for NULL?

Posted on 2014-04-16
7
231 Views
Last Modified: 2014-04-17
I have a recordset that looks like this in SQL Server Studio:

defaultname                                                       overridename

Chief Financial Officer
Group Vice President
Regional Vice President
Area Vice President                                            NULL
Chief Financial Officer                                        NULL
Vice President                                                     NULL
Manager                                                              NULL

As I'm looping through my results, I'm trying to check to see if the overridename column is NULL. I don't care if it's empty, I want to know if it's NULL.

if($workqueueexclusiverolelist[$i]["overridename"]==NULL)
{
echo $workqueueexclusiverolelist[$i]["defaultname"]." |  corp (unassigned)";
}

The result that I get from the above gives me every row without a value as opposed to only those that are NULL.

What am I doing wrong? How can I better craft my query so I'm getting only those rows that are NULL as opposed to everything that's empty?
0
Comment
Question by:brucegust
7 Comments
 
LVL 38

Assisted Solution

by:Tom Beck
Tom Beck earned 100 total points
ID: 40004337
How about is_null?

if(is_null($workqueueexclusiverolelist[$i]["overridename"]))
{
echo $workqueueexclusiverolelist[$i]["defaultname"]." |  corp (unassigned)";
}

http://www.php.net/is_null
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 300 total points
ID: 40004352
NULL in PHP is not really the same as a null column coming back from SQL.  For this reason I define my columns that might be expected to be empty this way:

NOT NULL DEFAULT ''

The '' there is a pair of apostrophes with nothing between them, in other words, an empty string.  Then when I fetch the column, I can test for PHP empty() or == NULL or == '' and get the expected result.

This is a little bit TL;DR but it shows what came to me as a surprise.  Look at the output when a bound variable contains an empty string and it's used to SELECT a column with SQL null in it.
http://www.iconoun.com/demo/temp_michael_munger.php

<?php // demo/temp_michael_munger.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
)
;
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
require_once('RAY_live_data.php');
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// CREATING A FIRST TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE first_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) DEFAULT NULL
, lname VARCHAR(24) DEFAULT NULL
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

echo PHP_EOL . "****** LOADING FIRST TABLE (HAS NULLS)" . PHP_EOL;

// PREPARE A QUERY TO LOAD OUR DATA INTO THE TABLE
$sql = "INSERT INTO first_table ( lname ) VALUES ( ? )";
$ins = $mysqli->prepare($sql);
if (!$ins)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// NB: THESE VARIABLES MUST EXIST BEFORE THEY CAN BE BOUND!
$person = current($test_names_arrays);

// USE AN ITERATOR TO LOAD THE TABLE
foreach ($test_names_arrays as $person)
{
    // BIND INPUT VARIABLES TO THE QUERY
	$ins->bind_param('s', $person['lname']);

    // RUN THE QUERY TO INSERT THE ROW
    $ins->execute();

    // GET THE AUTO_INCREMENT ID FROM CONNECTION, NOT FROM STATEMENT
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>{$person['lname']}</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;

// PREPARE A SELECT-ALL QUERY
$sql = "SELECT * FROM first_table";
$res = $mysqli->query($sql);
if (!$res)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// SHOW THE RESULTS SET
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}

// PREPARE A SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM first_table WHERE (fname=? AND lname=?)";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$fname = "";
$lname = "Paseur";
$sel->bind_param('ss', $fname, $lname);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($first_id, $first_fname, $first_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$first_id' fname='$first_fname' lname='$first_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();

// REPEAT THE SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM first_table WHERE lname=? OR id=? ";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$lname = "Paseur";
$id    = 1;
$sel->bind_param('si', $lname, $id);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($first_id, $first_fname, $first_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$first_id' fname='$first_fname' lname='$first_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();




// CREATING ANOTHER TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE second_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

echo PHP_EOL . "****** LOADING SECOND TABLE (HAS NOT NULL)" . PHP_EOL;

// PREPARE A QUERY TO LOAD OUR DATA INTO THE TABLE
$sql = "INSERT INTO second_table ( lname ) VALUES ( ? )";
$ins = $mysqli->prepare($sql);
if (!$ins)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// NB: THESE VARIABLES MUST EXIST BEFORE THEY CAN BE BOUND!
$person = current($test_names_arrays);

// USE AN ITERATOR TO LOAD THE TABLE
foreach ($test_names_arrays as $person)
{
    // BIND INPUT VARIABLES TO THE QUERY
	$ins->bind_param('s', $person['lname']);

    // RUN THE QUERY TO INSERT THE ROW
    $ins->execute();

    // GET THE AUTO_INCREMENT ID FROM CONNECTION, NOT FROM STATEMENT
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>{$person['lname']}</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;

// PREPARE A SELECT-ALL QUERY
$sql = "SELECT * FROM second_table";
$res = $mysqli->query($sql);
if (!$res)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// SHOW THE RESULTS SET
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}

// PREPARE A SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM second_table WHERE (fname=? AND lname=?)";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$fname = "";
$lname = "Paseur";
$sel->bind_param('ss', $fname, $lname);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($second_id, $second_fname, $second_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$second_id' fname='$second_fname' lname='$second_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();

// REPEAT THE SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM second_table WHERE lname=? OR id=? ";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$lname = "Paseur";
$id    = 1;
$sel->bind_param('si', $lname, $id);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($second_id, $second_fname, $second_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$second_id' fname='$second_fname' lname='$second_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();

Open in new window

0
 

Author Comment

by:brucegust
ID: 40004500
Tom, thanks so much for weighing in. I had tried your suggestion, but ran into the same problem and I believe Ray has confirmed what I've seen in the past.

Ray, here's my dilemma: The recordset that I'm using is a result of a LEFT JOIN query. The NULL values are representative of a row in one of the tables that doesn't exist as opposed to a situation where I had a legitimate row with a NULL column. That being the case, changing the default value wouldn't make a dent, would it? Reason being is that the NULL value displays to indicate a record in one table that doesn't exist in the other.

My logic may be off, but if what I'm saying resonates with you, what then are my options if I can't change the default value of my column?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40004537
I would be glad to experiment with it a little bit.  Can you please give us the SSCCE?  That would be a pair of CREATE TABLE statements, a few queries to load the tables and a query representative of the LEFT JOIN.  If you use CREATE TEMPORARY TABLE you can put all of these into a single script that we can run and rerun to tinker with the settings and displays of data.
0
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 100 total points
ID: 40004562
How about chaning your query to
SELECT * FROM table1 t1 LEFT JOIN t2 ON t1.joinfield = t2.joinfield
WHERE overridename IS NULL;

Open in new window

So any row that has a blank in overridenname is a null value.
0
 

Author Comment

by:brucegust
ID: 40007311
Folks, thanks so much for your input. Here's what I did:

Once it became obvious that PHP and SQL weren't going to be talking to one another like I had hoped, I chose to simply run another query at that point I was needing the info and checked for an id value as opposed to a NULL value. It required a little more in the way of heavy lifting, but the end result was credible information exactly where I needed it and, like a great man used to say, "You can't argue with success."

Thanks so much!
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40007517
Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now