• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Why does this query not work for NULL?

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
brucegust
Asked:
brucegust
3 Solutions
 
Tom BeckCommented:
How about is_null?

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

http://www.php.net/is_null
0
 
Ray PaseurCommented:
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
 
brucegustPHP DeveloperAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Ray PaseurCommented:
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
 
Julian HansenCommented:
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
 
brucegustPHP DeveloperAuthor Commented:
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
 
Ray PaseurCommented:
Thanks for the points and thanks for using EE, ~Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now